Advice on an OR statement

Question

Post reply

Advice on an OR statement

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

Thank you for the detailed feedback - it is appreciated!

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 2

DrJogalog (3/31/2010)[hrThanks for the reply.
Our devlopers have a passion for writing 'no lock's' in their statements. The data returned in these queries should not actually change once written so there is no real potential to return inaccurate data in this example. <snip>

Think again:

http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 3

This probably doesn't add much to the conversation, but as pointed out OR predicates often cause scans..

Since you have a compound index, you *might* see a gain by changing the where clause by pulling out the common term. For example:SELECT Document, rc

FROM [ILines]

WHERE

Prefix='C'

and

(

(Document='STCR124171' and seqno=2)

OR (Document='STCR124172' and seqno=1)

)But, the Union will probably still perform better.. 🙂

DrJogalog SSC Veteran Points: 283 More actions · Answer 4

Thanks Lamprey. Missing and duplicating records on commited data becuase of NO LOCK hints is news to me. Hopefully this will be something we encouter few and far between. Something learnt every day. I'll pass this along and see what the developers make of it.

I'll also give your amendment to the query a go just to compare to the other SQL results.

Thanks again.

Paul White SSC Guru Points: 150468 More actions · Answer 5

Lamprey13 (4/1/2010)
Think again:
http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

...and think again, again:

http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx

...rows can be missed or double-counted at the default READ COMMITTED level too. It might surprise you that this can still happen at REPEATABLE READ as well...:-)

The only way to avoid such behaviour is to use SERIALIZABLE, or one of the row-versioning isolation levels: READ_COMMITTED_SNAPSHOT or full SNAPSHOT.

All good interesting stuff.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

DrJogalog SSC Veteran Points: 283 More actions · Answer 6

Duplicate and missing records on queries without NO LOCK hints too?! Thats a very simple but clear explanation and seems it could be common occurence than with the NO LOCK hint examples. As you say, it will make for interesting reading, at least for a few of my colleagues.

Thanks.

DrJogalog SSC Veteran Points: 283 More actions · Answer 7

Lamprey13 (4/1/2010)
Since you have a compound index, you *might* see a gain by changing the where clause by pulling out the common term. For example:SELECT Document, rc FROM [ILines] WHERE Prefix='C' and ( (Document='STCR124171' and seqno=2) OR (Document='STCR124172' and seqno=1)
)But, the Union will probably still perform better.. 🙂

This statement shows the execution plan exactly the same as OR statement and takes the same time too. UNION ALL is by far the fastest way to achieve the same results (or the customer could upgrade to 2008 ;-)).

Thanks for the input.

Paul White SSC Guru Points: 150468 More actions · Answer 8

DrJogalog (4/2/2010)
Duplicate and missing records on queries without NO LOCK hints too?! Thats a very simple but clear explanation and seems it could be common occurence than with the NO LOCK hint examples. As you say, it will make for interesting reading, at least for a few of my colleagues.

You're much more likely to skip rows, read rows twice, read rows that logically never existed..etc..at the READUNCOMMITTED (NOLOCK) isolation level...but that's what it does 🙂

It is a fascinating topic. If nothing else, it helps to focus the mind on exactly what is, and is not, guaranteed at each of the various isolation levels. No single isolation level is inherenty 'better' than another...just different. All things are compromises, and SQL Server is no different. In this case the trade-off is between concurrency, resource usage, and 'accuracy'.

Good fun, isn't it?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

DrJogalog SSC Veteran Points: 283 More actions · Answer 9

Paul White NZ (4/2/2010)
You're much more likely to skip rows, read rows twice, read rows that logically never existed..etc..at the READUNCOMMITTED (NOLOCK) isolation level...but that's what it does 🙂

Yes, I obviously believe this to be the case on a 1 to 1 basis. But comparative to the fact that most SELECT statements are at READCOMMITTED level in a working enviroment and NO LOCK hints shouldn't be common place. It would probably suggest that it happens more often and probably goes unrecongnized too depending on the scenario.

Good fun, isn't it?

I don't know about fun, but it certainly is inetresting. lol 😛

Paul White SSC Guru Points: 150468 More actions · Answer 10

DrJogalog (4/2/2010)
...and NO LOCK hints shouldn't be common place.

If only...:-)

It would probably suggest that it happens more often and probably goes unrecongnized too depending on the scenario.

I absolutely agree.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi