This article going to talk about real world query optimization.Most of the times all developers and Database administrators face the long time running query.so this article will help to you optimize the sql query with index.

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

Now, you’ll see the different, it will takes faster 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.Now you can feel the different way of query optimization and retrive records from database in short time.

Hope the tutorial above help you. If you’re looking for SQL 2012 hosting on Europe server, please visit HostForLIFE.eu. HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.microsoft.com/web/hosting/HostingProvider/Details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.