2

I have 2 tables:

ReportItem

id (int, primary key clustered index)
report_item  (nvarchar(100))
flow_name (nvarchar(50))
source (nvarchar(50))

Tech_Details

id (int, primary key clustered index)
flow_name (nvarchar(50))
source (nvarchar(50))
lang (nvarchar(5))
start_time (datetime)

Based on these 2 tables, I would like to produce output as below:

Date (start_time)  report_item    EN (lang)    BM (lang)
2015-01-01         test           3             0
2015-01-01         test           1             2
2015-01-02         test2          6             2
2015-01-02         test2          0             7

I have 2 stored procedures which are able to get the desired results but they are not good enough.

The first stored procedure is too lengthy and hard to maintain. Performance-wise, it is acceptable. Stored Procedure as below:

SELECT tb.date , (tb.source + '-' +tb.report_item) as report_item, sum(isnull(tb1.EN,0)) as EN, sum(isnull(tb1.BM,0)) as BM
FROM (
    SELECT (dateadd(dd,0, datediff(dd,0,b.start_time))) as date, a.report_item, a.flow_name, a.source 
    FROM ReportItem a, TechDetails b
    WHERE (dateadd(dd,0, datediff(dd,0,b.start_time))) >= @StartDate 
    AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
    AND a.report_id =8 AND a.flow_name = @flow_name
    GROUP BY (dateadd(dd,0, datediff(dd,0,b.start_time))), a.report_item, a.flow_name, a.source 
) as tb 

LEFT JOIN
(
    SELECT (dateadd(dd,0, datediff(dd,0,b.start_time))) as date, b.flow_name , b.source, count(*) as EN, 'Test' as report_item
    FROM TechDetails b
    WHERE(dateadd(dd,0, datediff(dd,0,b.start_time))) >=  @StartDate AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
    and flow_name = @flow_name and b.lang ='EN' and b.sel_test  =1
    GROUP BY dateadd(dd,0, datediff(dd,0,b.start_time)), b.flow_name, b.source, b.lang

    UNION

    SELECT (dateadd(dd,0, datediff(dd,0,b.start_time))) as date, b.flow_name , b.source, 0 as BM, count(*) as BM, 'Test' as report_item
    FROM TechDetails b
    WHERE(dateadd(dd,0, datediff(dd,0,b.start_time))) >=  @StartDate AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
    and flow_name = @flow_name and b.lang ='EN' and b.sel_test  =1
    GROUP BY dateadd(dd,0, datediff(dd,0,b.start_time)), b.flow_name, b.source, b.lang      

    UNION

    SELECT (dateadd(dd,0, datediff(dd,0,b.start_time))) as date, b.flow_name , b.source, count(*) as EN, 0 as BM, 'Test2' as report_item
    FROM TechDetails b
    WHERE(dateadd(dd,0, datediff(dd,0,b.start_time))) >=  @StartDate AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
    and flow_name = @flow_name and b.lang ='EN' and b.sel_test2  =1
    GROUP BY dateadd(dd,0, datediff(dd,0,b.start_time)), b.flow_name, b.source, b.lang              

    UNION

    SELECT (dateadd(dd,0, datediff(dd,0,b.start_time))) as date, b.flow_name , b.source, 0 as EN, COUNT(*) as BM,  'Test2' as report_item
    FROM TechDetails b
    WHERE(dateadd(dd,0, datediff(dd,0,b.start_time))) >=  @StartDate AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
    and flow_name = @flow_name and b.lang ='BM' and b.sel_test2 =1
    GROUP BY dateadd(dd,0, datediff(dd,0,b.start_time)), b.flow_name, b.source, b.lang


) as tb1 ON tb.flow_name = tb1.flow_name and tb.source = tb1.source and tb.report_item =tb1.report_item 
  and tb.date = tb1.date

GROUP BY tb.date , tb.flow_name, tb.report_item, tb.source
ORDER BY tb.date 

The Second stored procedure is shorter and easier to maintain but it is terrible at handling large chunk of data. Stored Procedure as below:

SELECT (dateadd(dd,0, datediff(dd,0,b.start_time))) as date, (a.source + '-' + a.report_item) as report_item
, SUM(CASE WHEN (b.lang = 'EN' AND b.sel_test = 1 AND a.report_item = 'Test')
    OR (b.lang = 'EN' AND b.sel_test2 = 1 AND a.report_item = 'Test2') 
    THEN 1 ELSE 0 END) AS EN
, SUM(CASE WHEN (b.lang = 'BM' AND b.sel_test = 1 AND a.report_item = 'Test')
    OR (b.lang = 'BM' AND b.sel_test2 = 1 AND a.report_item = 'Test2') 
    THEN 1 ELSE 0 END) AS BM
FROM ReportItem a INNER JOIN TechDetails b ON a.source = b.source
WHERE (dateadd(dd,0, datediff(dd,0,b.start_time))) >= @StartDate 
AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
AND a.report_id =8 AND a.flow_name = @flow_name
GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), a.flow_name, a.report_item, a.source
ORDER BY dateadd (dd, 0, datediff(dd, 0, b.start_time))    

Is there any way to improve the stored procedure or other approach?

Click here for Execution Plan in XML

I have changed a.report_item to int datatype, added non-clustered index and removed a.source = b.source from CASE but query speed did not improve. Select CASE from multiple tables is the root cause but I have no idea how to deal with it. CASE WHEN (b.lang = 'BM' AND b.sel_test = 1 AND a.source = b.source AND a.report_item = 'Test'

5
  • Looks like you are missing a join between ReportItem and TechDetailsin your second query. You have the join in SUM() instead of as a join. That will give you the cartesian product number of rows to walk through. Could be a lot of rows. – Mikael Eriksson May 17 '16 at 12:05
  • Try to change the FROMclause to FROM ReportItem a INNER JOIN TechDetails b ON a.source = b.source Perhaps use flow_name in the join too if that returns what you want. FROM ReportItem a INNER JOIN TechDetails b ON a.source = b.source and a.flow_name = b.flow_name – Mikael Eriksson May 17 '16 at 12:11
  • 1
    This is a good illustration of how comma joins are easy to trip over. Just stop using them and switch to the explicit join syntax. – Andriy M May 17 '16 at 13:02
  • @MikaelEriksson Explicit INNER JOIN does help to halves the execution time, but comparatively still much slower than the 1st query especially at handling large data. – Jc85 May 18 '16 at 4:01
  • 1
    I noticed a.report_item = 'Test' is the main cause of the slow execution time – Jc85 May 18 '16 at 4:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.