SQL query and optimization

I've run a simple test on sql query involve 2 tables, tblEmail & tblEmailPromotion

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.