SQL: NOT IN vs. NOT EQUAL
By: Zack Turnbow
Introduction
While writing SQL queries, have you ever strung several (not equals) together in the WHERE clause and just wondered if that was inefficient? Or have you used the NOT IN function, (both of which can be used to get the same result), and wondered the same thing? If you have or just are curious about either of these methods, keep reading. In this article, both of these functions will be explored to see how efficient they are, plus it will cover a few other methods to get the same results.
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
Implementation
First, let’s see which is faster, NOT IN or NOT EQUAL. To be able to test this, a dummy table with some data needs to be created. Use the attached SQL script to create a test table. The script will take several minutes to run. After the table has been created, we need to set up the base test script without the query that is to be tested. Use the attached base test query script. Its contents are as follows:
DECLARE @Start datetime, @End datetime -- The start and end time of the query
DECLARE @Iterations int, @Results int -- Total iterations and the results
DECLARE @Counter int -- Loop counter
SET @Iterations = 100
PRINT 'The test has started'
SET @Counter = 0
SET @Start = getDate()
WHILE @Counter
BEGIN
-- Queries go here
SET @Counter = @Counter + 1
END
SET @End = getDate()
PRINT 'Time Elapsed: ' + cast(datediff(ms, @Start, @End) AS varchar) + ' milliseconds'
The actual test query will go inside the WHILE loop. Now we need to create the test queries, one for NOT IN and the other for NOT EQUAL. The two queries are below and can be found in the attached file called TestQueries.
FROM [Dummy]
WHERE ID NOT IN (622, 623, 624, 625)
SELECT @results = count(ID)
FROM [Dummy]
WHERE ID 622 AND
ID 623 AND
ID 624 AND
ID 625
Now it is time to test each of the queries, so copy and paste the first query inside the WHILE loop and execute the test. The test will take some time to complete so be patient. Please keep in mind that the execution results posted in this article will vary from the result from a different SQL Server since each machine is different but the final comparison of NOT EQUAL and NOT IN should be the same. The results are as follows:
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
NOT IN:
The test has started
Time Elapsed: 46526 milliseconds
NOT EQUAL:
The test has started
Time Elapsed: 46376 milliseconds
As shown, the difference between the two is extremely small. So it really doesn’t matter which way is used. Perhaps NOT IN would be a better choice since there is less typing involved.
Earlier, it was mentioned that there are ways to get the results quicker than the two functions above. It is possible but it takes a little more tweaking. There are two other ways to improve performance, the first one does not use a WHERE clause but instead uses a UNION and LEFT OUTER JOIN to get the same results. The query looks like this:
FROM [Dummy] LEFT OUTER JOIN (
SELECT 622 AS ID UNION
SELECT 623 UNION
SELECT 624 UNION
SELECT 625 ) AS Temp
ON [Dummy].ID = Temp.ID
WHERE Temp.ID IS NULL
Go ahead and copy and paste it inside the WHILE loop in the test script then run it. The results for this article are as follows:
The test has started
Time Elapsed: 28056 milliseconds
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
As you can see, this query produces the results much quicker. But wait there is still one other way to get the results faster. The query will be using the NOT EXIST statement and looks like this:
FROM [Dummy]
WHERE NOT EXISTS(SELECT * FROM
(
SELECT 622 AS TempID UNION ALL
SELECT 623 UNION ALL
SELECT 624 UNION ALL
SELECT 625 ) AS Temp
WHERE Temp.TempID = [Dummy].ID )
Copy and paste this query into the test script then run it. The results are:
The test has started
Time Elapsed: 19916 milliseconds
This query is even faster. In this article hopefully it has been shown that there are many different ways to get the same results out of SQL Server. Some are better than others. It all depends on how much effort is put into the query.
What have we learned?
- Which is faster between NOT IN and NOT EQUAL
- How to test queries for performance.
- Other ways to improve performance.
Attachments
|
|