I actually saw a very similar result when updating a large number of rows in a table. I had an SSIS package which updated one or two columns of many rows in a table. It would take hours to use the SQL Command component (and run a stored procedure for each row). So instead I inserted the PK and values of the rows that needed to be change into a temporary table with the same columns and data types as the table that needed updating. After that I ran a stored procedure that was something like this
UPDATE MyTable
SET Col1 = ISNULL(MyTempTable.Col1, MyTable.Col2)
FROM
MyTable INNER JOIN MyTempTable
ON MyTable.PK = MyTempTable.PK
(I know my code isn't right, but I think the principle is clear.)
Doing this takes as little as 5 minutes, when previously I would have waited hours to do the updates one at a time.
I appreciate that the overhead of SSIS's connections almost cetainly exaggreates the benefit, but the basic idea is the same. Using a join instead of looking up the individual rows.
SQL Server (like all DBMS) works best when dealing with sets rather than row by row operations.
When you use the JOIN variations for this type of query, SQL Server can optimise and perform the operations effectiely in a single pass for the set rather than looking row by row.
The query plans for the second 2 optiosn should show this.
Hi K. Matsumura
Thanks for taking the time to play with the queries, that's the best way to know what will work best in your environment.
These tests were conducted using SQL Server 2000 SP4. I re-ran the tests this morning on a couple different servers to verify that the results are still similar to what they were when I wrote the article, and they are. I also ran them on SQL Server 2005 SP2 with the same trend in query times.
However, when I ran them on my workstation, today (I had only run them on servers before), the numbers worked out the way you have recorded: faster for the NOT IN() (10 seconds for 100 iterations) than for the NOT EXISTS (12.6 seconds). Something to do with the newer hardware in the workstation, perhaps (single processor, dual-core workstation vs. dual processor, single-core, server)?
KenJ
I've not checked this on SQL Server 2005, but the NUMBER of terms in the NOT IN or the NUMBER of <> , in combination with the distribution statistics, makes a huge difference in Sybase, and I suspect it could in SQL 2005.
Looking for "not in" is expensive; how expensive depends on how many you have. On Sybase, if there were many different values and the distribution was pretty even, there was a point - about 4 or 5, I recall, where the plan changed from using index to table scan. It takes longer to optimize a bunch of NOTs, and at some point (to avoid letting the optimizer use more time than the search!) it bails out and says "if you have that many, a table scan is probably fastest anyway (and besides, if you wrote than many NOTs performance was probably a secondary question).
In an app that built the statement on the fly from t to X number of items to be NOT IN, performance varied wildly depending on how many the user entered. 3 was fast. 15 was not. (These were wide rows with 100s of millions of records).
It was faster to break it into a single batch of smaller queries. Instead of doing
select foo from bar where x not in (1, 20, 43, 99, 110, 2000, 3201, 7098, 7099)
go
it was faster to do
select foo from bar where x < 100 and x not in (1, 20, 43, 99)
select foo from bar where x >= 100 and x < 5000 and and x not in (110, 2000, 3201)
select foo from bar where x >= 5000 and and x not in (7098, 7099)
go (a single batch)
Of course, this is the kind of silly performance hack that will bite your butt when you port it to a new RDB or a new version that better optimizes the original query.
