Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is there a way to improve this SELECT statement for performance? Expand / Collapse
Author
Message
Posted Friday, August 30, 2013 8:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 27, 2013 11:38 AM
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
Post #1490182
Posted Friday, August 30, 2013 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
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)
Post #1490196
Posted Friday, August 30, 2013 11:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
+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"
Post #1490240
Posted Friday, August 30, 2013 1:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 2,330, Visits: 3,510
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1490273
Posted Friday, August 30, 2013 1:30 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
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"
Post #1490274
Posted Friday, August 30, 2013 1:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 2,330, Visits: 3,510
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1490277
Posted Friday, August 30, 2013 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
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)
Post #1490282
Posted Friday, August 30, 2013 1:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 2,330, Visits: 3,510
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1490285
Posted Friday, August 30, 2013 1:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
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)
Post #1490288
Posted Friday, August 30, 2013 1:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 2,330, Visits: 3,510
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1490292
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse