transact sql performance not in clause times out is null compare performance

PROBLEM: THE "not in" CLAUSE WAS CAUSING TIME OUTS IN MY TRY-CATCH BLOCKS IN .NET

SOLUTION: THE CODE BELOW POINTED ME IN THE RIGHT DIRECITON ABOUT HOW TO USE BETTER STATEMENTS TO ACCOMPLISH THE ORGINAL GOAL.

SEARCH TERM:

transact sql in clause times out "is null" compare performance

transactional sql 2005 in clause times out "is null" compare performance

transactional Microsoft sql 2005 in clause times out "is null" compare performance

This web site receives credit for pointing me in the correction direction...but after I ran some tests on my own database the results were very encouraging.

http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx

If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance:

Use EXISTS or NOT EXISTS
Use IN
Perform a LEFT OUTER JOIN and check for a NULL condition

during the TESTING PROCESS:

I also highlighted each individual statement, right-clicked and "display estimated execution plan"

declare @locTimeStart as datetime

declare @locTimeEnd as datetime

declare @total1 as int

declare @cnt as int

set @total1 = 0

set @cnt=0

SET NOCOUNT ON

while (@cnt <= 10)

BEGIN

set @locTimeStart = getdate()

-----------------------------------------------------------------------------------------------

-- sTBL table has 400,000 records / sLogTBL has 10 records with same sid

-- DURING EACH TEST ONLY RUN A OR B OR C...BUT NOT ALL OF THEM

-- TOGETHER

-----------------------------------------------------------------------------------------------

------a

--clustered sid Index (11 runs: 493 milliseconds avg(1st round) / 466 milliseconds(2nd round))

--NOTclustered sid Index (11 runs: 840 milliseconds avg(1st round) / 851 milliseconds(2nd round))

select min(sid) from sTBL where sid not in (Select sid from sLogTBL)

------B ..........this is the TRUE WINNER in My SCENARIO

--clustered sid Index (11 runs: 69 milliseconds avg(1st round) / 72 milliseconds(2nd round))

--NOTclustered sid Index (11 runs: 70 milliseconds avg(1st round) / 72 milliseconds(2nd round))

--select min(s.sid) from sTBL as s where NOT EXISTS (Select sid from sLogTBL as l where s.sid=l.sid)

------C

--clustered sid Index (11 runs: 76 milliseconds avg(1st round) / 77 milliseconds(2nd round))

--NOTclustered sid Index (11 runs: 77 milliseconds avg(1st round) / 77 milliseconds(2nd round))

--select min(s.sid) from sTBL as s left join sLogTBL as l on s.sid=l.sid where l.sid is null

set @locTimeEnd = getdate()

set @total1 = @total1 + datediff(ms,@locTimeStart,@locTimeEnd)

------------

set @cnt = @cnt +1

END

SET NOCOUNT OFF

Select @total1 as old, @cnt as loops, (@total1/@cnt) as avgMilliseconds

as to my delight the NOT EXISTS performed almost 90% better. (SEE THE ARTICLE ABOVE)
In other words the "NOT IN" clause was 7-14 times slower depending on the type of index which was used (CLUSTER / NON CLUSTERED).

In fact the "NOT IN" clause when using a Non-Clustered index actually perfromed a table scan (OUCH)

but for the NOT EXISTS clause on IS NULL clause...the type of index of the foreign key did not matter at all.

thanks for the bit of information when I was trying to improve my sql server statements.

DATE POSTED FOR MICROSOFT SQL SERVER 2005 --2009-04-07 (April 7, 2009)

Doug Lubey and Family