tuning qry: is there a better way to write below query RRS feed

  • Question

  • In the below query the last AND condition having CASE stmt giving nightmare performance 9 hours to run vs 30 mins without it.

    Query plan show NEST LOOP; is there a better way to write this?

    ( Program_Name  is in STAR_SKUs;   Account_ID  is in STAR_Dealers ) 



    SELECT  Account_Display_ID, Contract_Number

    ,      Data_Source

    ,      Account_ID

    ,      co.SKU_Number

    ,      SKU_Description

    ,      Program_Name

    ,      Insurance_Premium

    FROM Temp.Contracts2 AS co (NOLOCK)

    JOIN dbo.STAR_SKUs AS sk (NOLOCK)       ON co.sku_number = sk.SKU_Number

    JOIN dbo.STAR_Dealers AS dl (NOLOCK)    ON co.dealer_id = dl.Dealer_ID

    JOIN dbo.Account_Displays ad      ON 1=AND Active_Flag = 1

    WHERE creation_date < (@run_date)

           AND (Contract_Number <> 'sdfssfsdf' AND co.Dealer_ID <> 'dfhsjkf')

           AND co.status <> 'del'

           AND SKU_Vendor_Flag NOT IN ('phs','twt','lnx','aff','stw')

           AND Program_Name NOT IN('pnaamport','pnaportfol','rheemport')


           AND (CASE WHEN co.status = 'can' AND cancel_process_date >= (@run_date)

                         THEN insurance_premium

                  ELSE insurance_premium - ISNULL(premium_refund_amount,0) END) > 0

           AND ((isnull(insurance_premium,0) - ISNULL(premium_refund_amount,0)) > 0 )




           CASE WHEN Program_Name IN('amz-lg') THEN 'Amazon LG'

           WHEN Program_Name IN('amz-pnsnic') THEN 'Amazon Panasonic'

           WHEN Program_Name IN('amz-phlps') THEN 'Amazon Philips'

           WHEN Program_Name = 'Amz-Sony' THEN 'Amazon Sony'

           WHEN Program_Name IN('amz-samsun','samsungecm') THEN 'Amazon Samsung'

           WHEN Program_Name IN('amz-sharp','sharpazn') THEN 'Amazon Sharp'

           WHEN Account_ID IN(78,104) THEN 'LG'

           WHEN Program_Name = 'MblSure001' THEN 'MobileSure'

           WHEN Account_ID = 81 THEN 'Philips AFM'

           WHEN Account_ID IN(56) THEN 'Epson America Inc'

           WHEN Account_ID IN(73,33) THEN 'SN Dealers'

           WHEN Account_ID IN(42,43,72) THEN 'Pro Group'

           WHEN Program_Name IN ('Atime500','BFranklin','Dir Engy','OneHour','PSI') THEN 'Direct Energy'

           WHEN Program_Name IN ('HDAM','HomDep_Dir','Home Depot') THEN 'Home Depot'

           WHEN Program_Name IN ('NordynDist','Nordyne','NordyneAM','NordyneCA') THEN 'Nordyne'

           WHEN Program_Name IN ('EZPlanHC','Rheem','RHEEM CAN','RHMDISTSPQ') THEN 'Rheem'

           WHEN Program_Name IN ('Amstrcan','Amstrus','CmfrtSurCN','CmfrtSurUS') THEN 'Trane'

           WHEN Program_Name IN ('EqdCANPort') THEN 'HVAC/R Portfolio'

           WHEN Program_Name IN ('LennoxComm','LnxCommCAN') THEN 'Lennox Commercial'

           WHEN Program_Name IN('ASKO-Phnx','ASKOAM','AskoPOS','Euro-Phnx') THEN 'ASKO'

           WHEN Program_Name IN('Dacor','Dacor-Phnx','DacorAM') THEN 'Dacor Appliances'

           WHEN Account_ID IN(109,105) THEN 'Zodiac Pool Supplies'

           WHEN Account_ID IN(89,101) AND Program_Name IN('AZKindle','AZKindleUK') THEN 'Amazon Kindle'

           WHEN Account_ID IN(89) AND Program_Name IN('AMZ-AIG') THEN 'Amazon Channel'

           WHEN Program_Name IN('SAMSUNGENT','SMSNGNOTE2','SMSNGNOTE3') THEN 'Samsung Mobile'

           WHEN Program_Name IN('ANCHomProt','CHRTotlHme') THEN 'Chartis Home'

           ELSE Account_Name END = ad.Account_Display_Name  --@account_display


    Monday, November 30, 2015 10:25 PM

All replies

  • Hi, I didn't understand why you need the CASE command in WHERE clause. Which data do you want to filter?

    Antonio Cesar

    Monday, November 30, 2015 10:33 PM
  • Why do you need to join with the Account_Displays at all? Also, since you're querying multiple tables, you need to add aliases to every referenced column in the query to simplify maintenance.

    Also, don't use NOLOCK hint in the query.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    Monday, November 30, 2015 10:44 PM
  • Also, my suggestion will be to use your case statement in the SELECT list instead and select info without last where condition into a temp table (and don't join with the account displays table). Join with it after you pre-selected the information into a temp table first with that extra column.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    Monday, November 30, 2015 10:47 PM
  • Using the CASE stmt I m generating needed account name based on Program_name or Account_ID and matching  it to = ad.Account_Display_Name


    Monday, November 30, 2015 11:35 PM
  • CASE is an expression. In SQL expressions return a scalar value. This much is fine, but we do not do display formatting in a query. That is supposed  to be done in a presentation layer.

    CASE also tests the WHEN clauses in left to right order and executes the first THEN that tested TRUE. Are you sure that this is done the way you meant?  The mix of program and accounts  names is weird.  Why are there  so many synonyms? It looks like you need to clean up  at least one table. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, December 1, 2015 4:12 AM
  • >Query plan show NEST LOOP;

    I would start by asking why o you think Nested loop is bad. What is issue with query why do you want to rewrite it.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles


    Tuesday, December 1, 2015 7:18 AM
  • I would suggest you to move that complicated CASE Expression evaluation to a table variable as already pointed. If that still doesn't work then try breaking your query into multiple queries starting from the most selective predicate first and then applying other predicates subsequently.

    Thanks and regards, Rishabh K

    Tuesday, December 1, 2015 7:28 AM