NOT IN vs NOT EXISTS

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 24, 2018 at 5:30 am

  #372285

  A little while a go I was told that NOT EXISTS will "never perform better" than NOT IN, and that NOT EXISTS is the "worst of all options". personally, this wasn't something I had experienced, and so in the interest of learning asked for any evidence of citation for such a claim; I was met with the answer "I'm an expert" and "Any citation will be by someone not as less experience, so take my word as the truth." (this is barely paraphrasing). This person claimed themselves as a SQL Expert, however, could give themselves no accreditation, and I personally had no seen their input anywhere else before (although they did have the "points", sql server was not an area that provided those). The attitude really annoyed me, but I had more pressing projects to work on, and so left it until I had such time to test.

  So, today I spent a  little time doing a quick comparison of the 3 options: NOT IN, LEFT JOIN ... WHERE {left table PK} IS NULL and NOT EXISTS. The queries I used are below:

  SET STATISTICS IO ON;

  SET STATISTICS TIME ON;

  PRINT N'NOT IN clause';

  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.Trantype IN ('New Business','Transfrd NB','Renewal'));

  PRINT N'LEFT JOIN and WHERE {left table PK} IS NULL';

  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@

    LEFT JOIN dbo.ic_brcledger BTX ON BTX.B@ = BPY.B@ AND BTX.PolRef@ = BPY.Polref@

               AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal')

  WHERE BCM.B@ != 3

  AND BCM.Ref@ != 'XXXX01'

  AND BPY.Term_date IS NULL

  AND BTX.Key@ IS NULL;

         

  PRINT N'NOT EXISTS clause';

  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 NOT EXISTS (SELECT 1

         FROM dbo.ic_brcledger BTX

        WHERE BTX.B@ = BPY.B@

         AND BTX.PolRef@ = BPY.Polref@

         AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal'));

  SET STATISTICS IO OFF;

  SET STATISTICS TIME OFF;

  I ran these against our Dev server at work; not the fastest machine, but still it very quickly proved a point, as the output was as follows:
  NOT IN clause

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

  (3168 rows affected)
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.

  SQL Server Execution Times:
 CPU time = 3206502 ms, elapsed time = 3204974 ms.
LEFT JOIN and WHERE {left table PK} IS NULL

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

  (3168 rows affected)
Table 'icp_brpolicy'. Scan count 5, logical reads 26052, 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 7, logical reads 696, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'icp_brcledger'. Scan count 35299, logical reads 230952, physical reads 0, read-ahead reads 0, 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 '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.

  SQL Server Execution Times:
 CPU time = 1781 ms, elapsed time = 483 ms.
NOT EXISTS clause

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

  (3168 rows affected)
Table 'icp_brcledger'. Scan count 38058, logical reads 167725, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'icp_brpolicy'. Scan count 5, logical reads 26052, 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 7, logical reads 696, physical reads 0, read-ahead reads 0, 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 '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.

  SQL Server Execution Times:
 CPU time = 828 ms, elapsed time = 245 ms.

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.
  The NOT IN was staggering slower.

  So, this got me back to thinking; why was I being told that NOT IN is better? Personally, like i said, I'd had better experience with NOT EXISTS. Also Redgate's (and I'm not saying Redgate are infallible) SQL Server prompt recommends the use of EXISTS over IN. Obviously there must be a reason behind it.

  Now, this could be a case of my indexing, but all 3 queries were written to do the same thing, and return the same number of rows (3168), so I see this as unlikely. Perhaps someone more obliging could share their experience, and give me a better reasoning than "trust me I'm a (self proclaimed) expert".

  Note: 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.

  NOT IN clause with additional BTX.Polref = BPY.PolRef@ clause

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

  (3168 rows affected)
Table 'icp_brpolicy'. Scan count 5, logical reads 26052, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'icp_brcledger'. Scan count 5, logical reads 194415, physical reads 0, read-ahead reads 0, 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 7, logical reads 696, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
 CPU time = 2922 ms, elapsed time = 784 ms.
LEFT JOIN and WHERE {left table PK} IS NULL

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

  (3168 rows affected)
Table 'icp_brpolicy'. Scan count 5, logical reads 26052, 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 7, logical reads 696, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'icp_brcledger'. Scan count 35299, logical reads 230952, physical reads 0, read-ahead reads 0, 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 '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.

  SQL Server Execution Times:
 CPU time = 1564 ms, elapsed time = 404 ms.
NOT EXISTS clause

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
 CPU time = 94 ms, elapsed time = 101 ms.

  (3168 rows affected)
Table 'icp_brcledger'. Scan count 38058, logical reads 167729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'icp_brpolicy'. Scan count 5, logical reads 26052, 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 0, read-ahead reads 0, 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 '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.

  SQL Server Execution Times:
 CPU time = 938 ms, elapsed time = 244 ms.

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

  Either way, in both cases NOT EXISTS was the winner in elapsed time.

  Thanks all!

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• jonathan.crawford

  SSCertifiable

  Points: 6611

  More actions

  October 24, 2018 at 6:39 am

  #2010912

  "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

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  October 24, 2018 at 7:33 am

  #2010921

  I'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'));


• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 24, 2018 at 7:40 am

  #2010922

  Jonathan AC Roberts - Wednesday, October 24, 2018 7:33 AM

  I'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 AM

  Note: 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

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  October 24, 2018 at 7:50 am

  #2010925

  Thom A - Wednesday, October 24, 2018 7:40 AM

  Jonathan AC Roberts - Wednesday, October 24, 2018 7:33 AM

  I'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 AM

  Note: 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?

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 24, 2018 at 8:19 am

  #2010933

  Jonathan AC Roberts - Wednesday, October 24, 2018 7:50 AM

  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?

  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

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  October 24, 2018 at 8:39 am

  #2010938

  I must ask, Thom, if you've compared the equivalent EXCEPT operator.

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

• Brandie Tarvin

  SSC Guru

  Points: 173004

  More actions

  October 24, 2018 at 8:41 am

  #2010939

  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.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Brandie Tarvin

  SSC Guru

  Points: 173004

  More actions

  October 24, 2018 at 8:46 am

  #2010943

  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.
  

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  October 24, 2018 at 8:51 am

  #2010945

  Thom A - Wednesday, October 24, 2018 8:19 AM

  Jonathan AC Roberts - Wednesday, October 24, 2018 7:50 AM

  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?

  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)

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 24, 2018 at 8:59 am

  #2010950

  Brandie Tarvin - Wednesday, October 24, 2018 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.
  

  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):

  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 EXISTS (SELECT 1

       FROM dbo.ic_brcledger BTX

       WHERE BTX.B@ = BPY.B@

        AND BTX.PolRef@ = BPY.Polref@

        AND BTX.Trantype IN ('New Business','Transfrd NB','Renewal'))

  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

• jonathan.crawford

  SSCertifiable

  Points: 6611

  More actions

  October 24, 2018 at 10:24 am

  #2010969

  Brandie Tarvin - Wednesday, October 24, 2018 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.
  

  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

• Jeffrey Williams

  SSC Guru

  Points: 90258

  More actions

  October 24, 2018 at 2:50 pm

  #2011021

  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

• Jeff Moden

  SSC Guru

  Points: 1003907

  More actions

  October 24, 2018 at 6:04 pm

  #2011048

  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

  ---

  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)
  

• Brandie Tarvin

  SSC Guru

  Points: 173004

  More actions

  October 25, 2018 at 4:48 am

  #2011099

  Jeff Moden - Wednesday, October 24, 2018 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.

  In my expert opinion, there is no such thing as a good test.

  <duck> @=)

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.