Table columns:
tblEmail (email varchar(255), IsDeleted int)
tblEmailPromotion (email varchar(255), PromotionID int)
Both tables without index
SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID = 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email
It takes about 2:43 (2 minutes 43 seconds) to get the result
Change the question from "LEFT OUTER JOIN" to "IN" become
SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email
Guest what? great improvement. Takes 1:07 (1 minute 7 seconds) to load the result.
Now, we index table tblEmail column Email (Unique)
SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID = 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email
It takes about 1:22 (1 minute 22 seconds) to get the result
Now, we run the 2nd query:
SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email
It only takes 27 Seconds
To make the 2nd query better:
SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6 AND pe.Email LIKE '%hotmail%')
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email
This will give 15 seconds.
Summary:
We need to choose the query wisely.
Index will help to improve data retrieving process but must be careful with indexes. Wrong indexes may cause you more problem.
