October 24, 2018 at 6:39 am
"SQL Expert" can compare their credentials with Gail, I can already guess how that's going to pan out.
https://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
October 24, 2018 at 7:33 am
SELECT BCM.B@,
BCM.Ref@,
BPY.Polref@
FROM dbo.ic_yyclient BCM
JOIN dbo.ic_brpolicy BPY ON BCM.B@ = BPY.B@ AND BCM.Ref@ = BPY.Ref@
WHERE BCM.B@ != 3
AND BCM.Ref@ != 'XXXX01'
AND BPY.Term_date IS NULL
AND BPY.PolRef@ NOT IN (SELECT BTX.Polref@
FROM dbo.ic_brcledger BTX
WHERE BTX.B@ = BPY.B@
AND BTX.Polref@ = BPY.PolRef@
AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal'));
October 24, 2018 at 7:40 am
Jonathan AC Roberts - Wednesday, October 24, 2018 7:33 AMI'm just wondering why you didn't include "AND BTX.Polref@ = BPY.PolRef@" in the WHERE clause of the NOT IN statement?SELECT BCM.B@,
BCM.Ref@,
BPY.Polref@
FROM dbo.ic_yyclient BCM
JOIN dbo.ic_brpolicy BPY ON BCM.B@ = BPY.B@ AND BCM.Ref@ = BPY.Ref@
WHERE BCM.B@ != 3
AND BCM.Ref@ != 'XXXX01'
AND BPY.Term_date IS NULL
AND BPY.PolRef@ NOT IN (SELECT BTX.Polref@
FROM dbo.ic_brcledger BTX
WHERE BTX.B@ = BPY.B@
AND BTX.Polref@ = BPY.PolRef@
AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal'));
Covered that in my post too 🙂
Thom A - Wednesday, October 24, 2018 5:30 AMNote: Adding the clause BTX.Polref@ = BPY.Polref@ in the NOT IN brings performance far closer to NOT EXISTS and LEFT JOIN (rerun below), but not as good. I excluded this in the original, as the "expert" used the format I have in the above SQL.
When I did add that, however, NOT EXISTS still performs better.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2018 at 7:50 am
Thom A - Wednesday, October 24, 2018 7:40 AMJonathan AC Roberts - Wednesday, October 24, 2018 7:33 AMI'm just wondering why you didn't include "AND BTX.Polref@ = BPY.PolRef@" in the WHERE clause of the NOT IN statement?SELECT BCM.B@,
BCM.Ref@,
BPY.Polref@
FROM dbo.ic_yyclient BCM
JOIN dbo.ic_brpolicy BPY ON BCM.B@ = BPY.B@ AND BCM.Ref@ = BPY.Ref@
WHERE BCM.B@ != 3
AND BCM.Ref@ != 'XXXX01'
AND BPY.Term_date IS NULL
AND BPY.PolRef@ NOT IN (SELECT BTX.Polref@
FROM dbo.ic_brcledger BTX
WHERE BTX.B@ = BPY.B@
AND BTX.Polref@ = BPY.PolRef@
AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal'));Covered that in my post too 🙂
Thom A - Wednesday, October 24, 2018 5:30 AMNote: Adding the clause BTX.Polref@ = BPY.Polref@ in the NOT IN brings performance far closer to NOT EXISTS and LEFT JOIN (rerun below), but not as good. I excluded this in the original, as the "expert" used the format I have in the above SQL.When I did add that, however, NOT EXISTS still performs better.
Oh, I hadn't read the full post.
I always use NOT EXISTS over NOT IN. I'd never heard anyone say EXISTS is a poorer performer.
Did you look at the execution plans?
October 24, 2018 at 8:19 am
Jonathan AC Roberts - Wednesday, October 24, 2018 7:50 AMOh, I hadn't read the full post.
I always use NOT EXISTS over NOT IN. I'd never heard anyone say EXISTS is a poorer performer.
Did you look at the execution plans?
The NOT IN (when using BTX.Polref@ = BPY.Polref@) and NOT EXISTS have identical plans, the LEFT JOIN has a different one.The estimated plan does differ if the BTX.Polref@ = BPY.Polref@ isn't included in the NOT IN, there are additional parallelism added and the Clustered Index Seek on the Ledger changes to a Clustered Index Scan.
Like yourself, I always use EXISTS over IN, so why I'm asking here. I've not really witnessed anyone be so adamant about something and then not back it up (apart from saying, trust me, I'm an expert). if what theya re saying is true, I'm certainly not seeing it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2018 at 8:39 am
I must ask, Thom, if you've compared the equivalent EXCEPT operator.
October 24, 2018 at 8:41 am
I've never actually played with EXISTS / NOT EXISTS, so I should probably try it out and see if it helps with our ITD feed processes, which are taking longer and longer to run.
I'll look at your code, see if I can adapt it, and verify whether or not it works for my specific scenario.
October 24, 2018 at 8:46 am
Okay, so I do have an additional question. Why use the specific WHERE clauses you've used? Why not test the NOT IN with specific values?
Why are you using this:
AND BPY.PolRef@ NOT IN (SELECT BTX.Polref@
FROM dbo.ic_brcledger BTX
WHERE BTX.B@ = BPY.B@
AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal'));
instead of this?
AND BPY.PolRef@ NOT IN ('value1','value2','value3');
I usually work with specific, short lists of things a value can't be or can be. So I'm curious if NOT EXISTS is actually better than calling out specific values in NOT IN or if it only works better when having to do a subquery.
October 24, 2018 at 8:51 am
Thom A - Wednesday, October 24, 2018 8:19 AMJonathan AC Roberts - Wednesday, October 24, 2018 7:50 AMOh, I hadn't read the full post.
I always use NOT EXISTS over NOT IN. I'd never heard anyone say EXISTS is a poorer performer.
Did you look at the execution plans?The NOT IN (when using BTX.Polref@ = BPY.Polref@) and NOT EXISTS have identical plans, the LEFT JOIN has a different one.The estimated plan does differ if the BTX.Polref@ = BPY.Polref@ isn't included in the NOT IN, there are additional parallelism added and the Clustered Index Seek on the Ledger changes to a Clustered Index Scan.
Like yourself, I always use EXISTS over IN, so why I'm asking here. I've not really witnessed anyone be so adamant about something and then not back it up (apart from saying, trust me, I'm an expert). if what theya re saying is true, I'm certainly not seeing it.
Looks to me like you've met someone full of BS. (and there are plenty of people like that about)
October 24, 2018 at 8:59 am
Brandie Tarvin - Wednesday, October 24, 2018 8:46 AMOkay, so I do have an additional question. Why use the specific WHERE clauses you've used? Why not test the NOT IN with specific values?Why are you using this:
AND BPY.PolRef@ NOT IN (SELECT BTX.Polref@
FROM dbo.ic_brcledger BTX
WHERE BTX.B@ = BPY.B@
AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal'));instead of this?
AND BPY.PolRef@ NOT IN ('value1','value2','value3');I usually work with specific, short lists of things a value can't be or can be. So I'm curious if NOT EXISTS is actually better than calling out specific values in NOT IN or if it only works better when having to do a subquery.
It's not a short list, Brandie (there are 225810 distinct B@ + Polref@ values in the ledger). If were a small number, I totally agree, I'd use NOT IN ('Value1','Value2',etc...).
The query I've actually used (well the NOT EXISTS), isn't actually one I would normally use (it's not an EXISTS), but it was a base line query I could quickly change to a NOT EXISTS. Normally the query I have would look like this (just the FROM AND WHERE):
That filters my dataset to live (insurance) policies only (as users have a habit of creating a policy, and then instead of marking the termination date, simply delete the transaction and forget about it... /sigh).
From my perspective, what I have works great, both NOT EXISTS and EXISTS, and instead I'm wonder why I have been told with such determination that the method in "the worst", when it clearly isn't. Perhaps it's a hang over from an older version of SQL Server? Perhaps it's something that's changed in the new cardinality estimator (which I don't have access to till me upgrade next(?) year). Perhaps it's that the person just mistaken and refuses to accept that actually they're wrong. I'm using this as a learning exercise to see if I'm missing something obvious or if, going forward, when using a (NOT) IN/EXISTS I should change to IN, from EXISTS.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2018 at 10:24 am
Brandie Tarvin - Wednesday, October 24, 2018 8:46 AMOkay, so I do have an additional question. Why use the specific WHERE clauses you've used? Why not test the NOT IN with specific values?Why are you using this:
AND BPY.PolRef@ NOT IN (SELECT BTX.Polref@
FROM dbo.ic_brcledger BTX
WHERE BTX.B@ = BPY.B@
AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal'));instead of this?
AND BPY.PolRef@ NOT IN ('value1','value2','value3');I usually work with specific, short lists of things a value can't be or can be. So I'm curious if NOT EXISTS is actually better than calling out specific values in NOT IN or if it only works better when having to do a subquery.
Looks to me like the polref relationship is complicated, when the requirement given was not to include the trantypes listed. Users probably can't give a good list for the polref.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
October 24, 2018 at 2:50 pm
One thing to take into consideration is that the first time you ran the test - the NOT IN actually loaded the buffer cache which is probably the majority of the performance hit. Look at the read-ahead reads for icp_brpolicy and icp_brcledger...
The second execution of the NOT IN with the added parameter does not have those reads.
What happens if you switch up the order and run the NOT EXISTS first and then NOT IN?
Table 'icp_brpolicy'. Scan count 5, logical reads 26052, physical reads 0, read-ahead reads 1794, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'icp_brcledger'. Scan count 5, logical reads 194280, physical reads 1, read-ahead reads 186739, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'icp_yyclient'. Scan count 8, logical reads 696, physical reads 1, read-ahead reads 628, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 24, 2018 at 6:04 pm
Ask the ring knocker if he likes pork chops and then feed him your results.
Lordy, I hate such "experts". As always, one good test is worth a thousand expert opinions.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2018 at 4:48 am
Jeff Moden - Wednesday, October 24, 2018 6:04 PMAsk the ring knocker if he likes pork chops and then feed him your results.Lordy, I hate such "experts". As always, one good test is worth a thousand expert opinions.
In my expert opinion, there is no such thing as a good test.
<duck> @=)
October 25, 2018 at 4:59 am
Perhaps my initial post mislead people, but the query in my initial post wasn't what was being "debated", when i was informed that NOT EXISTS is "the worst option", it was a totally different query in a different environment; I simply wasn't in a position to test when the discussion came up. It's something that I finally found the time to do yesterday, and was unsurprised by my findings (that in truth there is little difference for the performance of NOT IN and NOT EXISTS, but that NOT EXISTS (for me) was the fastest answer).
Thus what I was asking here was more if those (far) more educated that me were of the same belief that NOT IN > NOT EXISTS < all other methods, or if I was being fed utter nonsense and told to believe based on the "experts" self opinion (sorry, I probably wasn't clear on that). I've not had the pleasure of talking to the person since, but wanted to find out if there was any evidence to his claim. The topic here, however, strongly suggests not; which was my initial understanding.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply