Scott, here is the whole query. and roughly, records for each supplier is minimum 400(rows) declare @a as char(1) declare @b as varchar(35) set @a='Y' set @b='<span style=''color:Red''>N</span>' SELECT DISTINCT Product_Master.Product_Kid, Product_Master.Product_Name, convert(nvarchar,Product_Master.Product_RegDate,103) as Product_RegDate, Product_Master.Product_RegDate as RegDate, Isnull(Product_Master.Product_Code,'-') AS Product_Code, Isnull(Product_Master.Product_ModelNo,'-') AS Product_ModelNo, CASE WHEN (Product_Master.Product_Image IS NULL) THEN @b ELSE @a END AS Product_Image, CASE WHEN (Product_Master.Product_BrandId IS NULL) THEN @b ELSE @a END AS Brand_Name, CASE WHEN (Product_Master.Product_MarketPrice IS NULL) THEN @b ELSE @a END AS Product_MarketPrice, CASE WHEN (Product_Master.Product_PackagingCharge IS NULL) THEN @b ELSE @a END AS Product_PackagingCharge, CASE WHEN (Product_Master.Product_MinOrderQty IS NULL) THEN @b ELSE @a END AS Product_MinOrderQty, CASE WHEN (Product_Master.Product_ShippingQty IS NULL) THEN @b ELSE @a END AS Product_ShippingQty, CASE WHEN (Product_Master.Product_Weight IS NULL) THEN @b ELSE @a END AS Product_Weight, CASE WHEN (Product_Master.Product_Dimension IS NULL) THEN @b ELSE @a END AS Product_Dimension, CASE WHEN (Product_Master.Product_DeliveryPeriod IS NULL) THEN @b ELSE @a END AS Product_DeliveryPeriod, CASE WHEN (Product_Master.Product_Description Is NULL) THEN @b ELSE @a END AS Product_Description, CASE WHEN (Product_Master.Product_PcsPerKg IS NULL) THEN @b ELSE @a END AS Product_PcsPerKg, CASE WHEN (ProductApplication.ProdApp_Code IS NULL) THEN @b ELSE @a END AS ProdApp_Code, CASE WHEN (ProductFeatures.ProdFeatures_Code IS NULL) THEN @b ELSE @a END AS ProdFeatures_Code, CASE WHEN (ProductTechnicalSpecification.TechSpec_code IS NULL) THEN @b ELSE @a END AS TechSpec_code, CASE WHEN (ProductSpecialNotesInstruction.ProdSpecInstr_Code IS NULL) THEN @b ELSE @a END AS ProdSpecInstr_Code, CASE WHEN (MaterialSafetyDataSheet.MSDS_Code IS NULL) THEN @b ELSE @a END AS MSDS_Code, CASE WHEN (ProductStandardApproval.ProdStdApproval_Code IS NULL) THEN @b ELSE @a END AS ProdStdApproval_Code, CASE WHEN (InstallationManual.InstCommManual_Code IS NULL) THEN @b ELSE @a END AS InstCommManual_Code, CASE WHEN (ProductPackaging.ProdPackaging_Code IS NULL) THEN @b ELSE @a END AS Product_Pack

Thanks for reply Scott, Yes, I have tried the same query without CASE WHEN statements, and it works faster comperatively. Moreover, The query is not this much only, there are many more CASE WHEN and Left Joins(which are necessary). Left Joins are essential for the result, so I can't move it. The only options is to find alternate for CASE WHEN statements. Prachi

Hello there... I have a query which spends a lot of time calculating my CASE WHEN -statements. There are around 16 CASE WHEN statements, which takes a huge time to calculate. Is there any way to rewrite query? My query looks like this SELECT DISTINCT Product_Master.Product_Kid, Product_Master.Product_Name, convert nvarchar,Product_Master.Product_RegDate,103) as Product_RegDate, Product_Master.Product_RegDate as RegDate, CASE WHEN (Product_Master.Product_Code = '' OR Product_Master.Product_Code IS NULL) THEN '-' ELSE Product_Master.Product_Code END AS Product_Code, CASE WHEN (Product_Master.Product_ModelNo = '' OR Product_Master.Product_ModelNo IS NULL) THEN '-' ELSE Product_Master.Product_ModelNo END AS Product_ModelNo, CASE WHEN (Product_Master.Product_Image = '' OR Product_Master.Product_Image IS NULL) THEN 'N' ELSE 'Y' END AS Product_Image, CASE WHEN (Product_Master.Product_BrandId = '' OR Product_Master.Product_BrandId IS NULL) THEN 'N' ELSE 'Y' END AS Brand_Name FROM Product_Master ORDER BY RegDate DESC

Hello there... I have a query in which, around 15 left outer join are there. I need to optimize the query to reduce the execution time. Thanks in advance for help Prachi