Search This Blog

Tuesday, January 26, 2010

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.

No comments: