October 17, 2007 at 2:00 pm
We have a large table of 250MM records, we are running the following queries and they are returning incorrect results.
We are running Sql server 2000 SP4 on a dual intel box with 4 gigs of ram both files have the same collation(SQL_Latin1_General_CP1_CI_AS).
Both tables have nonclustered indexes on the name columns, IMPORTFILE_DeDuped1 only has 1 column(Name) both fields in each table are varchar(255)
The bottom query should be returning roughly 84k records, can anyone provide insight into why?
WORKS – 16k recs
Select * From IMPORTFILE_DeDuped1 Where
Name In (Select Name from BIG250MMTABLE)
DOESN”T WORK – 0 recs
Select * From IMPORTFILE_DeDuped1 Where
Name NOT In (Select Name from BIG250MMTABLE)
October 17, 2007 at 2:15 pm
You probably have null values in your name column in the BIG250MMTABLE table.
If you have NOT IN (x,y,NULL), then this will always evaluate to false.
Here is probably a better explanation:
http://articles.techrepublic.com.com/5100-9592-5319615.html
Best Regards,
Chris Büttner
October 17, 2007 at 2:24 pm
Nope no nulls, we specifically remove those prior to the select.
October 17, 2007 at 2:33 pm
I'm not sure about the performance you will get out of this, but try using the following syntax:
Select ID1.*, BT.Name From IMPORTFILE_DeDuped1 as ID1
left outer join BIG250MMTABLE as BT
on ID1.Name = BT.Name
where BT.Name is not null
With this query, you can change to a full join and remove the where clause, and kind of see if everything is matching the way you intended it to. I like this syntax when my in and not in clauses are acting funny.
Thanks,
Eric
October 18, 2007 at 6:47 am
Strommy (10/17/2007)
I'm not sure about the performance you will get out of this, but try using the following syntax:Select ID1.*, BT.Name From IMPORTFILE_DeDuped1 as ID1
left outer join BIG250MMTABLE as BT
on ID1.Name = BT.Name
where BT.Name is not null
With this query, you can change to a full join and remove the where clause, and kind of see if everything is matching the way you intended it to. I like this syntax when my in and not in clauses are acting funny.
Thanks,
Eric
We tried this, it does work, however we are still unsure as to why the "NOT IN" clause produces different results.
October 19, 2007 at 2:31 am
Hi,
By the way can you re-write the query as below and let me know whether it helps.
Select A.* From IMPORTFILE_DeDuped1 A Where
A.Name NOT In (Select B.Name from BIG250MMTABLE B)
Best Regards,
Roshan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply