alternative to rewire CASE WHEN in query

prachidalwadi

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

Herman T Instance

case when (isnull(Product_Master.Product_Code, '') = '' then '-' else Product_Master.Product_Code end as Product_Code

In Word you can only store 2 bytes. That is why I use Writer.

scottgp

Personally, I find it hard to believe that the CASE statements are what's taking time since they don't look complex. How do you know that the CASE statements are what take the most time? Have you tried the same query without some of the CASE statements, just returning the actual column data instead, and comparing the performance? Is the DISTINCT required? Is RegDate indexed? Scott

PIEBALDconsult

Yeah, what they say. Plus why convert (and transmit) the textual RegDate? Perform that outside the database.

prachidalwadi

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

scottgp

Then we'd probably have to see the whole query since the little we've seen doesn't indicate a problem. It would help to know the table structures and rough row counts. Scott

Jorgen Andersson

I suppose that the nulls are there because of the left joins, you refer to in your answer to Scott, and therefore is nothing you can do anything about. But how about the zero length strings, do they serve a purpose? If not, you can add a check to the columns that disallows zero length strings at inserts and then you can remove that check in the query, which should speed it up a bit. Alternatively swap to Oracle where you can use a Function Based Index which would speed up things immensely. Like: Create index PM_PRODUCTCODE_EXIST_IDX ON PRODUCT_MASTER(Product_Kid,CASE WHEN (Product_Code = '' OR Product_Code IS NULL) THEN '-' ELSE Product_Master.Product_Code END);

"When did ignorance become a point of view" - Dilbert

Herman T Instance

Left joins..... yup that's costly

In Word you can only store 2 bytes. That is why I use Writer.

prachidalwadi

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