August 30, 2013 at 8:23 am
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? :
SELECTDISTINCT
@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
August 30, 2013 at 9:01 am
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/[/url]
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/[/url]
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 11:10 am
+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; Theyll drag you down to their level and beat you with experience
August 30, 2013 at 1:26 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 30, 2013 at 1:30 pm
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; Theyll drag you down to their level and beat you with experience
August 30, 2013 at 1:34 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 30, 2013 at 1:46 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 1:52 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 30, 2013 at 1:56 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 2:00 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 30, 2013 at 2:02 pm
Sean Lange (8/30/2013)
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.
Huh? The only isolation level lower than the default is READ UNCOMMITTED, which is just shorthand for NOLOCK on every table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 30, 2013 at 2:07 pm
ScottPletcher (8/30/2013)
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 :-).
And a table with 50 rows that hasn't been updated since 1959 is likely to encounter locking???
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 2:14 pm
ScottPletcher (8/30/2013)
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.
So you['re saying that if you compare the execution plan of two queries, one with tables having WITH(NOLOCK) specified and one that does not, you would see a more optimal plan with the latter?
I agree "time" is saved by bypassing potentially locked rows, but there's no difference to the performance of the query any more than adding a WAITFOR DELAY in the middle of your stored-procedure/TSQL
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 30, 2013 at 2:16 pm
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
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.
Huh? The only isolation level lower than the default is READ UNCOMMITTED, which is just shorthand for NOLOCK on every table.
This discussion has gotten far beyond silly. You are now accusing me of not understanding what the NOLOCK hint does or when it is safe. You win Scott.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 2:17 pm
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
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 :-).
And a table with 50 rows that hasn't been updated since 1959 is likely to encounter locking???
No, but ANY locking ADDS OVERHEAD. Avoiding the locking thus reduces overhead.
I don't know why so many want to deny that: THAT'S the main reason NOLOCK is available, to allow one to reduce locking overhead.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply