April 15, 2009 at 5:35 pm
By this time we had put approximately 20 indexes on other tables identified by DTA and the "missing indexes" dmv's that didn't really help the main problem
Sorry , I did some googling and I kind of see what you mean now
April 16, 2009 at 12:08 am
I have witnessed many times that NOT IN works very slow in SQL Server 2005, especially when the it is comparing with a long list of string values.
Didn't knew anbout SQL Server 2000 (as in current scenario).
April 17, 2009 at 7:42 am
Christopher Stobbs (4/15/2009)
oh and here is some sample stuff that I'm using:
CREATE TABLE Table1
(BIBNAC VARCHAR(100),Col1 INT,Col2 INT ,Col3 INT)
CREATE TABLE Table2
(ACCOUNTNUM VARCHAR(100))
CREATE CLUSTERED INDEX PK_1 ON Table1
(BIBNAC)
CREATE CLUSTERED INDEX PK_1 ON Table2
(ACCOUNTNUM)
INSERT INTO Table1
SELECT TOP (10000) 41,1,0,2
FROM syscomments a, syscomments b UNION ALL
SELECT TOP (10000) 1,99,12,3
FROM syscomments a, syscomments b UNION ALL
SELECT TOP (10000) 0005041,99,12,3
FROM syscomments a, syscomments b UNION ALL
SELECT TOP (10000) 00074441,99,7,3
FROM syscomments a, syscomments b UNION ALL
SELECT TOP (10000) 00000001,9,2,32
FROM syscomments a, syscomments b UNION ALL
SELECT TOP (10000) 0021,63,731,31
FROM syscomments a, syscomments b
INSERT INTO Table2
SELECT TOP 10000 41
FROM syscomments a, syscomments b UNION ALL
SELECT TOP 10000 36345
FROM syscomments a, syscomments b UNION ALL
SELECT TOP 10000 002232
FROM syscomments a, syscomments b UNION ALL
SELECT TOP 10000 000234242
FROM syscomments a, syscomments b UNION ALL
SELECT TOP 10000 00000211
FROM syscomments a, syscomments b UNION ALL
SELECT TOP 10000 1
FROM syscomments a, syscomments b
SELECT COUNT(*) FROM Table1
SELECT COUNT(*) FROM Table2
UPDATE STATISTICS Table1 WITH FULLSCAN
UPDATE STATISTICS Table2 WITH FULLSCAN
select *
from Table1 T1
where substring(T1.BIBNAC,patindex('%[^0]%', T1.BIBNAC),len(T1.BIBNAC) - patindex('%[^0]%', T1.BIBNAC) + 1) in
(select
substring(T1A.BIBNAC,patindex('%[^0]%', T1A.BIBNAC),len(T1.BIBNAC) - patindex('%[^0]%', T1A.BIBNAC) + 1)
from Table1 T1A
except
select
substring(T2.ACCOUNTNUM,patindex('%[^0]%', T2.ACCOUNTNUM),len(T2.ACCOUNTNUM) - patindex('%[^0]%', T2.ACCOUNTNUM) + 1)
from Table2 T2)
select * from Table1 where
substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) - patindex('%[^0]%', BIBNAC) + 1)
NOT IN
(
SELECT
substring(accountnum,patindex('%[^0]%', accountnum),len(accountnum) - patindex('%[^0]%', accountnum) + 1)
FROM
Table2
)
When I add my data to your tables the 'except' query takes forever in 2005.
I have officially gone mad with this problem!
April 17, 2009 at 8:00 am
Hi,
Have you tried my NOT EXISTS solution in 2005?
I found that to be the fastest...
select *
from Table1
where NOT EXISTS
(
SELECT 1
FROM Table2
WHERE substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) - patindex('%[^0]%', BIBNAC) + 1) =
substring(accountnum,patindex('%[^0]%', accountnum),len(accountnum) - patindex('%[^0]%', accountnum) + 1)
)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 17, 2009 at 6:18 pm
Yeah i dunno man, i tried the NOT EXISTS method and it works with your dummy data but not with my actual data.
Maybe its a data thing.
April 17, 2009 at 7:34 pm
Strange, but something to check, are there any NULL values in Table2 (the one being queried in the WHERE ... NOT IN ?
April 20, 2009 at 7:52 am
No unfortunately there aren't any null values
April 21, 2009 at 7:35 am
I ran into the same problem when we upgraded to SQL2K5. It does in fact process a NOT IN query very different than SQL2K especially if the subquery is pulling a "large" list. My jobs were getting slammed. I had to change all the NOT IN queries to LEFT JOIN queries to get the same performance, and in some cases it was even faster. As everyone said prior, the execution plan will elude to the possible differences.
April 21, 2009 at 8:33 am
Thank Antonio
Yeah seems like a join instead of 'not in' is the way to go
Viewing 9 posts - 61 through 68 (of 68 total)
You must be logged in to reply to this topic. Login to reply