Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there a way to improve this SELECT statement for performance?


Is there a way to improve this SELECT statement for performance?

Author
Message
Molap
Molap
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 69
Hello all,

I have to run this query during inserts. It works fine for thousands of records but once it hits tens or hundreds of thousands of records the performance is in minutes and not seconds. Is there a way to improve it? :

SELECT DISTINCT
@SeasonStamp,
r.RunnerID,
r.RunID AS RunID,
(SELECT COUNT(DISTINCT RunnerID) from RACES WITH (NOLOCK) where universalid = r.universalid AND rtrim(RunID) = rtrim(r.RunID) AND RaceDate >= @CutoffDate) AS NUMOFSponsorS,
(SELECT COUNT(DISTINCT RaceDate) from RACES WITH (NOLOCK) where universalid = r.universalid AND rtrim(RunID) = rtrim(r.RunID) AND RaceDate >= @CutoffDate AND RunnerID like r.RunnerID + '%') AS NUMBEROFinhouseMeets,
(SELECT COUNT(DISTINCT RaceDate) from RACES WITH (NOLOCK) where universalid = r.universalid AND rtrim(RunID) = rtrim(r.RunID) AND RaceDate >= @CutoffDate AND RunnerID NOT like r.RunnerID + '%') AS NUMBEROFoutsideMeets,
r.universalid
FROM
RACES r WITH (NOLOCK)
WHERE
r.RaceDate >= @CutoffDate
AND RunnerID like @Sponsor+'%'
AND r.ZorX = @ZorX
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16570 Visits: 17016
There are very likely some things to help performance. Please see this article about what we need to see in order to help.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Also, you need to drop the NOLOCK hints. They are NOT a performance boost.

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4905 Visits: 7365
+1 @Sean

And to the OP, if you do post a question without sample data/DDL for the tables needed (etc), at least attach an actual execution plan - as most of the "experts" on this forum will typically take a stab at it if that's been included in your post.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6680
Sean Lange (8/30/2013)
NOLOCK hints. They are NOT a performance boost.



To me, that's not a logical claim. While NOLOCK is clearly dangerous, it DOES in fact reduce overhead by avoiding any locks at all from being taken while reading the table.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4905 Visits: 7365
It may "bypass" the locking of the records it's ready but it does not improve performance or reduce any overhead one bit.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6680
Here's a quick-and-dirty first try -- might or might not work. Also, you need to get rid of the DISTINCT if at all possible -- it's a big drag on performance.



IF OBJECT_ID('tempdb..#race_counts') IS NOT NULL
DROP TABLE #race_counts
CREATE TABLE #race_counts (
universalid int NOT NULL,
RunID int NOT NULL,
RunnerID int NOT NULL,
race_date_count int NULL,
UNIQUE CLUSTERED ( universalid, RunID, RunnerID )
)
--EXEC tempdb..sp_help #race_Counts

INSERT INTO #race_counts
SELECT
universalid, RunID, RunnerID,
COUNT(DISTINCT RaceDate) AS race_date_count
FROM dbo.races r WITH (NOLOCK)
WHERE
RaceDate >= @CutoffDate
GROUP BY
universalid, RunID, RunnerID

SELECT DISTINCT
@SeasonStamp,
r.RunnerID,
r.RunID AS RunID,
(SELECT COUNT(*) FROM #race_counts WHERE universalid = r.universalid AND RunID = r.RunID) AS NUMOFSponsorS,
(SELECT SUM(race_date_count) FROM #race_counts WHERE universalid = r.universalid AND RunID = r.RunID AND RunnerID like r.RunnerID + '%') AS NUMBEROFinhouseMeets,
(SELECT SUM(race_date_count) FROM #race_counts WHERE universalid = r.universalid AND RunID = r.RunID AND RunnerID NOT like r.RunnerID + '%') AS NUMBEROFoutsideMeets,
r.universalid
FROM
RACES r WITH (NOLOCK)
WHERE
r.RaceDate >= @CutoffDate
AND r.RunnerID like @Sponsor+'%'
AND r.ZorX = @ZorX




SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16570 Visits: 17016
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
NOLOCK hints. They are NOT a performance boost.



To me, that's not a logical claim. While NOLOCK is clearly dangerous, it DOES in fact reduce overhead by avoiding any locks at all from being taken while reading the table.


Good grief Scott. How many times have you seen or heard people say they add NOLOCK because "it makes the queries faster"? Using NOLOCK as a performance enhancement tool for queries is like using a jet engine for cooking fish. Sure it will get you to the end line slightly faster but it has a really good chance of destroying the food.

Are you honestly suggesting that we should use this to boost performance or do you just like splitting hairs? I provided some links with in depth details about the usage of that hint. I would assume that somewhat alleviates the need for me to reproduce all the details in my thread.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6680
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
NOLOCK hints. They are NOT a performance boost.



To me, that's not a logical claim. While NOLOCK is clearly dangerous, it DOES in fact reduce overhead by avoiding any locks at all from being taken while reading the table.


Good grief Scott. How many times have you seen or heard people say they add NOLOCK because "it makes the queries faster"? Using NOLOCK as a performance enhancement tool for queries is like using a jet engine for cooking fish. Sure it will get you to the end line slightly faster but it has a really good chance of destroying the food.

Are you honestly suggesting that we should use this to boost performance or do you just like splitting hairs? I provided some links with in depth details about the usage of that hint. I would assume that somewhat alleviates the need for me to reproduce all the details in my thread.



Yes, I am honestly suggesting it can be worthwhile to add NOLOCK to boost performance. Are you honestly suggesting it should be removed from the product just because you don't understand when it is safe or not safe to use it??

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16570 Visits: 17016
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
NOLOCK hints. They are NOT a performance boost.



To me, that's not a logical claim. While NOLOCK is clearly dangerous, it DOES in fact reduce overhead by avoiding any locks at all from being taken while reading the table.


Good grief Scott. How many times have you seen or heard people say they add NOLOCK because "it makes the queries faster"? Using NOLOCK as a performance enhancement tool for queries is like using a jet engine for cooking fish. Sure it will get you to the end line slightly faster but it has a really good chance of destroying the food.

Are you honestly suggesting that we should use this to boost performance or do you just like splitting hairs? I provided some links with in depth details about the usage of that hint. I would assume that somewhat alleviates the need for me to reproduce all the details in my thread.



Yes, I am honestly suggesting it can be worthwhile to add NOLOCK to boost performance. Are you honestly suggesting it should be removed from the product just because you don't understand when it is safe or not safe to use it??


Yes I am. I would use isolation which does not return duplicate/missing data unlike NOLOCK.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6680
MyDoggieJessie (8/30/2013)
It may "bypass" the locking of the records it's ready but it does not improve performance or reduce any overhead one bit.



LOL. That's self-contradictory -- if NOLOCK avoids locking that the statement would otherwise have to do, it de facto improves performance.

I'm not saying NOLOCK should be used indiscriminately. I'm saying it does have proper uses because it DOES reduce overhead.

Just because it's often overused and/or misused is no reason to falsify what it does or does not do.

NOLOCK can be useful, for example, for code lookup tables, such as state code lookups. A state hasn't been added since 1959, I think I'll risk it :-).

Also, for example, if/when I'm forced to do read(s) from very busy production table(s) and I want to insure that I don't interfere with production processing.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search