Speed up Query Run Time with multiple Case When Expressions

Question

I have the following code below. It is a simple Sum with specific conditions and divided by count distinct function with specific conditions. The problem it is taking too long to run. Right now it is 2 Days and it has not finished running. How can I make this faster? This is part of the code the Case whens are repeated over 100 times as I am tracking over every month.

Select 

 (sum (case when INSTALLATION_TYPE like'%R' and RATE_FAKT = 'VI' and INvoice_Date between '2018-01-01' and '2018-12-31' then QUANT end)/1000)/

Count (Distinct (case when INSTALLATION_TYPE like'%R' and RATE_FAKT = 'VI' and INvoice_Date between '2018-01-01' and '2018-12-31' then MIRN end) )CY18_GJPERMIRN_VI,



(sum (case when RATE_FAKT = 'VB' and INvoice_Date between '2018-01-01' and '2018-12-31' then QUANT end)/1000)/

Count (Distinct (case when INSTALLATION_TYPE like'%R' and RATE_FAKT = 'VB' and INvoice_Date between '2018-01-01' and '2018-12-31' then MIRN end) )CY18_GJPERMIRN_VB,



  (sum (case when INSTALLATION_TYPE like'%B' and RATE_FAKT = 'VI' and INvoice_Date between '2018-01-01' and '2018-12-31' then QUANT end)/1000)/

Count (Distinct (case when INSTALLATION_TYPE like'%B' and RATE_FAKT = 'VI' and INvoice_Date between '2018-01-01' and '2019-12-31' then MIRN end) )CY18_GJPERMIRN_VI_Commercial,


  (sum (case when INSTALLATION_TYPE like'%R' and RATE_FAKT = 'VI' and INvoice_Date between '2019-01-01' and '2019-08-31' then QUANT end)/1000)/

Count (Distinct (case when INSTALLATION_TYPE like'%R' and RATE_FAKT = 'VI' and INvoice_Date between '2019-01-01' and '2019-08-31' then MIRN end) )CY19_AUG_GJPERMIRN_VI,


(sum (case when RATE_FAKT = 'VB' and INvoice_Date between '2019-01-01' and '2019-08-31' then QUANT end)/1000)/

Count (Distinct (case when INSTALLATION_TYPE like'%R' and RATE_FAKT = 'VB' and INvoice_Date between '2019-01-01' and '2019-08-31' then MIRN end) )CY19_AUG_GJPERMIRN_VB,


  (sum (case when INSTALLATION_TYPE like'%B' and RATE_FAKT = 'VI' and INvoice_Date between '2019-01-01' and '2019-08-31' then QUANT end)/1000)/


Count (Distinct (case when INSTALLATION_TYPE like'%B' and RATE_FAKT = 'VI' and INvoice_Date between '2019-01-01' and '2019-08-31' then MIRN end) )CY19_AUG_GJPERMIRN_VI_Commercial

  FROM [Analytics_JGN].[dbo].[VW_BILLINGS]


  Where MIRN like '524%';

Please add some sample data in Google spreadsheet. So, it will be better understand related to your query. — JIKEN, Sep 22 '19 at 01:12
This does not appear to be a [tag:mysql] question. That tag should be removed. — Michael - sqlbot, Sep 22 '19 at 03:20
Your query is referencing a view (presumably, based on the naming). It is impossible to know what is going on without knowing what the view is. — Gordon Linoff, Sep 22 '19 at 03:27

score 1 · Answer 1 · answered Sep 22 '19 at 05:32

You need to use dynamic PIVOT query to generate columns dynamically.
This link will help you in it.
Because I checked that there are 3 columns (GJPERMIRN_VI, GJPERMIRN_VB, GJPERMIRN_VI_Commercial) are repeating year wise and prefix is CY(Last 2 character of year from Invoice date column)_.

I have changed your query from CASE statement to UNION ALL with CTE.
This will little bit faster.
Please check below query for your answer.

;WITH CTE_GJPERMIRN
AS
(
    SELECT
        (SUM(ISNULL(QUANT,0)) / 1000) / ISNULL(COUNT(DISTINCT MIRN),1) AS CY18_GJPERMIRN_VI,
        0 AS CY18_GJPERMIRN_VB,
        0 AS CY18_GJPERMIRN_VI_Commercial,
        0 AS CY19_AUG_GJPERMIRN_VI,
        0 AS CY19_AUG_GJPERMIRN_VB,
        0 AS CY19_AUG_GJPERMIRN_VI_Commercial
    FROM [Analytics_JGN].[dbo].[VW_BILLINGS]
    Where MIRN LIKE '524%'
    AND INSTALLATION_TYPE LIKE '%R' 
    AND RATE_FAKT = 'VI' 
    AND INvoice_Date BETWEEN '2018-01-01' AND '2018-12-31'

    UNION ALL

    SELECT
        0 AS CY18_GJPERMIRN_VI,
        (SUM(ISNULL(QUANT,0)) / 1000) / ISNULL(COUNT(DISTINCT MIRN),1) AS CY18_GJPERMIRN_VB,
        0 AS CY18_GJPERMIRN_VI_Commercial,
        0 AS CY19_AUG_GJPERMIRN_VI,
        0 AS CY19_AUG_GJPERMIRN_VB,
        0 AS CY19_AUG_GJPERMIRN_VI_Commercial
    FROM [Analytics_JGN].[dbo].[VW_BILLINGS]
    Where MIRN LIKE '524%'
    AND INSTALLATION_TYPE LIKE '%R'
    AND RATE_FAKT = 'VB' 
    AND INvoice_Date BETWEEN '2018-01-01' AND '2018-12-31'

    UNION ALL

    SELECT
        0 AS CY18_GJPERMIRN_VI,
        0 AS CY18_GJPERMIRN_VB,
        (SUM(ISNULL(QUANT,0)) / 1000) / ISNULL(COUNT(DISTINCT MIRN),1) AS CY18_GJPERMIRN_VI_Commercial,
        0 AS CY19_AUG_GJPERMIRN_VI,
        0 AS CY19_AUG_GJPERMIRN_VB,
        0 AS CY19_AUG_GJPERMIRN_VI_Commercial
    FROM [Analytics_JGN].[dbo].[VW_BILLINGS]
    Where MIRN LIKE '524%'
    AND INSTALLATION_TYPE LIKE '%B' 
    AND RATE_FAKT = 'VI' 
    AND INvoice_Date BETWEEN '2018-01-01' AND '2018-12-31' --Consider 2018 year in End Date instead of 2019 in count distinct case statement

    UNION ALL

    SELECT
        0 AS CY18_GJPERMIRN_VI,
        0 AS CY18_GJPERMIRN_VB,
        0 AS CY18_GJPERMIRN_VI_Commercial,
        (SUM(ISNULL(QUANT,0)) / 1000) / ISNULL(COUNT(DISTINCT MIRN),1) AS CY19_AUG_GJPERMIRN_VI,
        0 AS CY19_AUG_GJPERMIRN_VB,
        0 AS CY19_AUG_GJPERMIRN_VI_Commercial
    FROM [Analytics_JGN].[dbo].[VW_BILLINGS]
    Where MIRN LIKE '524%'
    AND INSTALLATION_TYPE LIKE'%R' 
    AND RATE_FAKT = 'VI' 
    AND INvoice_Date BETWEEN '2019-01-01' AND '2019-08-31'

    UNION ALL

    SELECT
        0 AS CY18_GJPERMIRN_VI,
        0 AS CY18_GJPERMIRN_VB,
        0 AS CY18_GJPERMIRN_VI_Commercial,
        0 AS CY19_AUG_GJPERMIRN_VI,
        (SUM(ISNULL(QUANT,0)) / 1000) / ISNULL(COUNT(DISTINCT MIRN),1) AS CY19_AUG_GJPERMIRN_VB,
        0 AS CY19_AUG_GJPERMIRN_VI_Commercial
    FROM [Analytics_JGN].[dbo].[VW_BILLINGS]
    Where MIRN LIKE '524%'
    AND INSTALLATION_TYPE LIKE '%R'
    AND RATE_FAKT = 'VB' 
    AND INvoice_Date BETWEEN '2019-01-01' AND '2019-08-31'

    UNION ALL

    SELECT
        0 AS CY18_GJPERMIRN_VI,
        0 AS CY18_GJPERMIRN_VB,
        0 AS CY18_GJPERMIRN_VI_Commercial,
        0 AS CY19_AUG_GJPERMIRN_VI,
        0 AS CY19_AUG_GJPERMIRN_VB,
        (SUM(ISNULL(QUANT,0)) / 1000) / ISNULL(COUNT(DISTINCT MIRN),1) AS CY19_AUG_GJPERMIRN_VI_Commercial
    FROM [Analytics_JGN].[dbo].[VW_BILLINGS]
    Where MIRN LIKE '524%'
    AND INSTALLATION_TYPE LIKE '%B' 
    AND RATE_FAKT = 'VI' 
    AND INvoice_Date BETWEEN '2019-01-01' AND '2019-08-31'
)
SELECT
    SUM(CG.CY18_GJPERMIRN_VI)                AS CY18_GJPERMIRN_VI,
    SUM(CG.CY18_GJPERMIRN_VB)                AS CY18_GJPERMIRN_VB,
    SUM(CG.CY18_GJPERMIRN_VI_Commercial)     AS CY18_GJPERMIRN_VI_Commercial,
    SUM(CG.CY19_AUG_GJPERMIRN_VI)            AS CY19_AUG_GJPERMIRN_VI,
    SUM(CG.CY19_AUG_GJPERMIRN_VB)            AS CY19_AUG_GJPERMIRN_VB,
    SUM(CG.CY19_AUG_GJPERMIRN_VI_Commercial) AS CY19_AUG_GJPERMIRN_VI_Commercial
FROM CTE_GJPERMIRN CG

Note: CY18_GJPERMIRN_VI_Commercial - You may put wrong end date in COUNT(DISTINCT) Invoice date case statement, which I have corrected.

@BardiaAlavi Please mark it as an answer. You will then get +2, and Jiken will get +15. Check the big "check" icon which you see on the left side of the answer. — SQL Police, Sep 24 '19 at 11:16

avery_larry · Answer 2 · 2019-09-22T14:27:25.833

For performance troubleshooting you can try using many separate select statements instead of many case statements in 1 select statement. Something like: (untested)

select *
from (
   Select sum(isnull(QUANT,0)/1000/Count(Distinct MIRN) CY18_GJPERMIRN_VI
   FROM [Analytics_JGN].[dbo].[VW_BILLINGS]
   Where MIRN like '524%'
      and INSTALLATION_TYPE like'%R'
      and RATE_FAKT = 'VI'
      and INvoice_Date between '2018-01-01' and '2018-12-31'
) a
full outer join
(
   select sum(isnull(QUANT,0)/1000/Count(Distinct(MIRN) CY18_GJPERMIRN_VB
   FROM [Analytics_JGN].[dbo].[VW_BILLINGS]
   Where MIRN like '524%'
      and RATE_FAKT = 'VB'
      and INvoice_Date between '2018-01-01' and '2018-12-31'
) b on 1=1

Or another approach is to use group by instead of case statements -- presuming it is reasonable. The date ranges would still need to be case statements, but RATE_FAKT and INSTALLATION_TYPE could most likely be moved to GROUP BY.

score 0 · Answer 3 · edited Sep 23 '19 at 11:21

Your query can be optimized in 2-4 steps because of communication problem.

1. First you load the require data in Temp table.
2. Since you are saying that this is only part of code so consider my script to incomplete as well.

CREATE TABLE #temp 
(
    INSTALLATION_TYPE VARCHAR(1),
    RATE_FAKT VARCHAR(2) ,
    INvoice_Date DATETIME,
    QUANT INT,
    MIRN INT
)

INSERT INTO #temp 
(
    INSTALLATION_TYPE,
    RATE_FAKT,
    INvoice_Date,
    QUANT,
    MIRN
) 
SELECT 
    RIGHT(INSTALLATION_TYPE,1),
    RATE_FAKT,
    INvoice_Date,
    QUANT,
    MIRN 
FROM [Analytics_JGN].[dbo].[VW_BILLINGS] 
WHERE INvoice_Date >= '2018-01-01' 
AND INvoice_Date <= '2019-12-31'

SELECT * FROM #temp

DROP TABLE #temp;

formatting not working today from my system. Thanks. – KumarHarsh Sep 23 '19 at 09:25 — KumarHarsh, Sep 23 '19 at 09:25

Speed up Query Run Time with multiple Case When Expressions

3 Answers3