Not Exist causing major performance problem

Question

Not Exist causing major performance problem

Viewing 5 posts - 16 through 19 (of 19 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Jeff Moden (8/30/2015)
and not exists (select * from ContractorCoverageException cce where cce.contractorcoverageid = a.id and postalid = postal.id)

Thank you Sir.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

kim / data detection group (9/1/2015)
... and of course, you have a suitable (unique? clustered? as appropriate) index on ContractorCoverageException, like:
... ContractorCoverageException (contractorcoverageid, postalid)
or
... ContractorCoverageException (contractorcoverageid) include (postalid)

Thank you.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Jeff Moden (8/28/2015)
Before you go to all that trouble, try changing "postalid" to "*" or "1" in the not exists and see what happens.

That won't do anything. The SELECT list for an exists is completely ignored, it's not processed in any way. You can put SELECT 1/0 in an EXISTS and it'll still run fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

GilaMonster (9/2/2015)
Jeff Moden (8/28/2015)
Before you go to all that trouble, try changing "postalid" to "*" or "1" in the not exists and see what happens.
That won't do anything. The SELECT list for an exists is completely ignored, it's not processed in any way. You can put SELECT 1/0 in an EXISTS and it'll still run fine.

Thanks for dispelling another old myth, Gail. :blush: I'm embarrassed to admit that I've never tested the myth. That 1/0 thing looks like a way to have some fun with someone's mind. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Jeff Moden (9/2/2015)
GilaMonster (9/2/2015)
Jeff Moden (8/28/2015)
Before you go to all that trouble, try changing "postalid" to "*" or "1" in the not exists and see what happens.
That won't do anything. The SELECT list for an exists is completely ignored, it's not processed in any way. You can put SELECT 1/0 in an EXISTS and it'll still run fine.
Thanks for dispelling another old myth, Gail. :blush: I'm embarrassed to admit that I've never tested the myth. That 1/0 thing looks like a way to have some fun with someone's mind. 🙂

Yes it does, and it happened to me. :hehe:

Gail, you posted something like that somewhere before and I thought it was the most counter-intuitive thing I'd seen in a long time. It does, however, illustrate the point very clearly and I thought the way you proved it with the 1 / 0 was brilliant. I tested it several different ways and tried to find that post again to compliment you on your proof, but I couldn't find the thread.

Since it's come up again here, I have another chance. Thank you for the irrefutable proof. Very nicely illustrated.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions