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
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: 3938 Visits: 6666
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)

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: 16540 Visits: 16993
ScottPletcher (8/30/2013)
[quote]
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 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: 4862 Visits: 7363
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; They'll drag you down to their level and beat you with experience" ;-)
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: 16540 Visits: 16993
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 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: 3938 Visits: 6666
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
[quote]
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)

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: 4862 Visits: 7363
"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" - I do agree with this statement

______________________________________________________________________________
"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: 3938 Visits: 6666
Sean Lange (8/30/2013)
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.



What "isolation" were you talking about then? What "isolation" does not return "bad" data like NOLOCK but doesn't require locks??
["Snapshot isolation" has its own (rather large) overhead.]

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: 16540 Visits: 16993
And now a word from our sponsor (Molap). It seems your thread has been hijacked by a discussion well outside the bounds of your original question. As has been said by at least three others in here, if you can post the actual execution plan at a minimum we can likely assist you with your problem.

_______________________________________________________________

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: 3938 Visits: 6666
MyDoggieJessie (8/30/2013)
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



No, I'd expect the query plans to be identical, since locking isn't shown in the query plans. But the query with NOLOCK WILL HAVE LESS OVERHEAD if any rows are read. It may be very little less overhead, or a lot less overhead, but it will have less overhead in SQL than a query w/o NOLOCK.

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