ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,441 through 4,455 (of 10,143 total)

RE: Return numbers in a range

Bala' (6/30/2013)
Thanks for this update Jeff. Yeah I got exactly what you have expressed.
I have an question ,is there any alternative way to get all child in self referencing...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 1, 2013 at 1:16 am

#1628618
RE: Logic for a complex query which involves grouping and average in SQL

SQLServeruser2304 (6/28/2013)
Thanks Chris, Just one thing, I havent mentioned but I thought I could query it myself. Below are the points :
1. I have 2 input parameters i.e., FromDate...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 1, 2013 at 1:10 am

#1628616
RE: Please Help me in the query

An index on column msisdn of table vmd_blulm would help, though probably not as much as the plan suggests. I'd do this first because it's likely skewing the relative costs.
The...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2013 at 7:59 am

#1628301
RE: Updating huge table

-- Gather all of the update info into a skinny temp table
-- then you're done with VoiceData_History.
-- Add a column containing a suitable batch number
-- used to split your data...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2013 at 6:16 am

#1628268
RE: Return numbers in a range

Bala' (6/28/2013)
WITH RECURSIVE CTE
CREATE TABLE #TestTable ([Index] int,[StartNumber] int,[Endnumber] int)
INSERT INTO #TestTable
SELECT 1,1000,1002 UNION ALL
SELECT 2,1550,1555
;WITH TableCte
AS
(
SELECT [Index],[StartNumber] ,[Endnumber] FROM #TestTable
UNION ALL
SELECT [Index],[StartNumber]+1 ,[Endnumber] FROM...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2013 at 4:15 am

#1628241
RE: Logic for a complex query which involves grouping and average in SQL

There's plenty of scope for improvement - mostly maintainability, but performance too. Compare the original with this (without sample data to test against, it's untested), which is about halfway to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2013 at 2:18 am

#1628222
RE: Are the posted questions getting worse?

Lynn Pettis (6/27/2013)
Cleared IRDO and will be heading to Afghanistan tomorrow via Kuwait.
It is actually happening! I am getting much more anxious!
I will probably be dark for about a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2013 at 12:58 am

#1628210
RE: INSERT Statement

Or NOT EXISTS. Both compare columns individually rather than concatenating them together which can give rise to false matches.
WHERE NOT EXISTS (
SELECT 1 FROM DSD
WHERE DSD.modify_by = smf6.RespID AND...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2013 at 9:01 am

#1628018
RE: What's the best index?

Easily tested:
/*
Use a tally table as a row source
DROP TABLE #MyTable
SELECT
col1 = ABS(CHECKSUM(newid()))%32,
col2 = (10000-n)%32,
col3 = n,
col4 = newid()
INTO #MyTable
FROM InlineTally (1000000)
*/
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
--...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2013 at 8:30 am

#1628001
RE: Query improvement

Why read the table twice?
SELECT
sumyy = sum(yy),
xx = ???('summary line') -- where ??? is an aggregate function
FROM tablea
CROSS APPLY (
SELECT SurrogateGroup = CASE
WHEN somedate_column < @startdate_parameter THEN...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2013 at 7:48 am

#1627976
RE: Problem with Co-Related Subquery

Post the actual execution plan as a .sqlplan attachment.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2013 at 7:01 am

#1627945
RE: Stored Procedure Performance

amardesai89 (6/27/2013)
I have one Stored procedure That takes around 2 hour to execute.
it runs in 2 hours on server with 8 processors.
and it runs in same time on server with...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2013 at 6:56 am

#1627943
RE: Are the posted questions getting worse?

GilaMonster (6/27/2013)
Apologies if any arrogance is showing through this morning.
Can't see any arrogance showing through from here, commendable considering the unruliness of the kids in your classroom today.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2013 at 5:44 am

#1627920
RE: general

paul.knibbs (6/27/2013)
Is the "Compute Scalar" part the bit that is ISNULL?
Right-click on the Compute Scalar operator to raise the properties sheet.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2013 at 3:09 am

#1627869
RE: general

paul.knibbs (6/27/2013)
Is ISNULL such an expensive operation, then? (Genuinely curious, never actually sat down and analysed it)...
It's not expensive, but it's not free either. Have a look at the actual...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2013 at 2:31 am

#1627847

Viewing 15 posts - 4,441 through 4,455 (of 10,143 total)