Need help on query performance

  • jc85 (5/26/2016)


    Sergiy (5/25/2016)


    If that one goes not so bad then incorporate it into the bigger one:

    SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang, SUM(b.sel_test) Test, SUM(b.sel_test2) Test2

    FROM TechDetails b

    WHERE b.start_time >= @StartDate

    AND b.start_time < dateadd(dd,0, datediff(dd,0,@EndDate)+1)

    GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), b.source, b.lang

    SELECT DT.date, (a.source + '-' + a.report_item) as report_item,

    SUM(CASE WHEN b.lang = 'EN' THEN CASE a.report_item WHEN 'Test' THEN Test WHEN 'Test2' THEN Test2 ELSE 0 END ELSE 0 END) EN,

    SUM(CASE WHEN b.lang = 'BM' THEN CASE a.report_item WHEN 'Test' THEN Test WHEN 'Test2' THEN Test2 ELSE 0 END ELSE 0 END) BM

    FROM ReportItem a

    INNER JOIN (

    SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang, SUM(b.sel_test) Test, SUM(b.sel_test2) Test2

    FROM TechDetails b

    WHERE b.start_time >= @StartDate

    AND b.start_time < dateadd(dd,0, datediff(dd,0,@EndDate)+1)

    AND b.lang IN ('EN', 'BM')

    GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), b.source, b.lang

    ) DT ON a.source = b.source

    WHERE a.report_id =8 AND a.flow_name = @flow_name

    AND a.report_item IN ('Test', 'Test2')

    And from looking at the overall design I can conclude the clustered index on ReportItem must be

    (report_id, flow_name, report_item) INCLUDE (source)

    The first one performed well but the second one is getting syntax error "The multi-part identifier b.lang and b.source could not be bound).

    In outer query it must be DT.lang and DT.source, of course.

    _____________
    Code for TallyGenerator

  • John Mitchell-245523 (5/26/2016)


    Sergiy (5/25/2016)


    And from looking at the overall design I can conclude the clustered index on ReportItem must be

    (report_id, flow_name, report_item) INCLUDE (source)

    Do you mean (report_id, flow_name, report_item, source)? You can't have an included column in a clustered index.

    John

    Yes, I forgot for a minute it was about a clustered index.

    Source may be not even needed, if it's a clustered index.

    _____________
    Code for TallyGenerator

  • Sergiy (5/26/2016)


    Source may be not even needed, if it's a clustered index.

    Yes, that's true. You only want the actual key columns.

    John

  • jc85 (5/25/2016)


    ChrisM@Work (5/25/2016)


    jc85 (5/25/2016)


    How should i post the table with data on this forum?

    How many rows do you need for ann_events_Tech_Details table?

    ChrisM@Work (5/25/2016)


    Ideally you would do this by posting a script to create a table then a series of inserts to populate it with data. You don't need to use the same table name, a temp table called say #ann_ReportItem will be fine. Jumping the gun a bit, you might want to do the same with ann_events_Tech_Details.

    The simple answer to "how many rows" is "sufficient to determine correctness of the query", so I'll leave that up to you, but you should include sufficient to give you some results in each of the columns in a representative set of output rows, possibly a subset of three days' worth.

    Provided the data in attached excel sheet, thanks!

    Excellent, thank you.

    Using a clustered index like so:

    CREATE CLUSTERED INDEX [cx_Stuff] ON [dbo].[ann_events_Tech_Details]

    ([source] ASC, [start_time] ASC)

    Try this:

    DECLARE @call_flow_name VARCHAR(20) = 'Technical'

    DECLARE @StartDate DATETIME = '20160302'

    DECLARE @EndDate DATETIME = '20160305'

    ;WITH FirstPass AS (

    SELECT

    [date] = CAST(b.start_time AS DATE),

    b.[source],

    b.lang,

    [sel_tel_nodialtone] = SUM(CASE WHEN b.sel_tel_nodialtone = 1 THEN 1 ELSE 0 END),

    [sel_tel_noisy] = SUM(CASE WHEN b.sel_tel_noisy = 1 THEN 1 ELSE 0 END),

    [sel_tech_ckc_disconnect] = SUM(CASE WHEN b.sel_tech_ckc_disconnect = 1 THEN 1 ELSE 0 END),

    [sel_tech_ckc_transfer] = SUM(CASE WHEN b.sel_tech_ckc_transfer = 1 THEN 1 ELSE 0 END),

    [sel_tel_csr] = SUM(CASE WHEN b.sel_tel_csr = 1 THEN 1 ELSE 0 END),

    [sel_inv_del] = SUM(CASE WHEN b.sel_inv_del = 1 THEN 1 ELSE 0 END),

    [sel_inv_unifi] = SUM(CASE WHEN b.sel_inv_unifi = 1 THEN 1 ELSE 0 END)

    FROM ann_events_Tech_Details b

    WHERE b.start_time >= @StartDate

    AND b.start_time <= @EndDate+1

    AND EXISTS (SELECT 1 FROM ann_ReportItem a

    WHERE a.report_id = 8

    AND a.call_flow_name = @call_flow_name

    AND a.[source] = b.[source]

    )

    GROUP BY

    b.[source], b.lang, CAST(b.start_time AS DATE)

    )

    SELECT

    b.[Date],

    [report_item] = (a.[source] + '-' + a.report_item),

    [EN] = MAX(CASE

    WHEN Lang = 'EN' AND report_item_id = 1 THEN sel_tel_nodialtone

    WHEN Lang = 'EN' AND report_item_id = 2 THEN sel_tel_noisy

    WHEN Lang = 'EN' AND report_item_id = 3 THEN sel_tech_ckc_disconnect

    WHEN Lang = 'EN' AND report_item_id = 4 THEN sel_tech_ckc_transfer

    WHEN Lang = 'EN' AND report_item_id = 5 THEN sel_tel_csr

    WHEN Lang = 'EN' AND report_item_id = 6 THEN sel_inv_del

    WHEN Lang = 'EN' AND report_item_id = 7 THEN sel_inv_unifi

    ELSE 0 END),

    [BM] = MAX(CASE

    WHEN Lang = 'BM' AND report_item_id = 1 THEN sel_tel_nodialtone

    WHEN Lang = 'BM' AND report_item_id = 2 THEN sel_tel_noisy

    WHEN Lang = 'BM' AND report_item_id = 3 THEN sel_tech_ckc_disconnect

    WHEN Lang = 'BM' AND report_item_id = 4 THEN sel_tech_ckc_transfer

    WHEN Lang = 'BM' AND report_item_id = 5 THEN sel_tel_csr

    WHEN Lang = 'BM' AND report_item_id = 6 THEN sel_inv_del

    WHEN Lang = 'BM' AND report_item_id = 7 THEN sel_inv_unifi

    ELSE 0 END),

    [MD] = MAX(CASE

    WHEN Lang = 'MD' AND report_item_id = 1 THEN sel_tel_nodialtone

    WHEN Lang = 'MD' AND report_item_id = 2 THEN sel_tel_noisy

    WHEN Lang = 'MD' AND report_item_id = 3 THEN sel_tech_ckc_disconnect

    WHEN Lang = 'MD' AND report_item_id = 4 THEN sel_tech_ckc_transfer

    WHEN Lang = 'MD' AND report_item_id = 5 THEN sel_tel_csr

    WHEN Lang = 'MD' AND report_item_id = 6 THEN sel_inv_del

    WHEN Lang = 'MD' AND report_item_id = 7 THEN sel_inv_unifi

    ELSE 0 END)

    FROM ann_ReportItem a

    INNER JOIN FirstPass b

    ON b.source = a.source

    WHERE a.report_id = 8

    AND a.call_flow_name = @call_flow_name

    GROUP BY [date], a.source, a.report_item

    ORDER BY b.[Date], a.[source], a.report_item

    On this humble home pc and with the data set ramped up to 10 million rows, it executes in less than half a second compared with 4 seconds for the original. Here's stats:

    Original query

    (14 row(s) affected)

    Table 'ann_ReportItem'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

    Table 'ann_events_Tech_Details'. Scan count 3, logical reads 423423, physical reads 0, read-ahead reads 171.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 5656 ms, elapsed time = 4078 ms.

    Modified query

    (14 row(s) affected)

    Table 'ann_ReportItem'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0.

    Table 'ann_events_Tech_Details'. Scan count 3, logical reads 5959, physical reads 0, read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 392 ms, elapsed time = 241 ms.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • ChrisM@home (5/26/2016)


    Using a clustered index like so:

    CREATE CLUSTERED INDEX [cx_Stuff] ON [dbo].[ann_events_Tech_Details]

    ([source] ASC, [start_time] ASC)

    Such clustered may be very good on query but it's gonna be a very high cost of maintenance.

    It will become fragmented all the time.

    Splitting pages will slow down inserts, increase IO load and amount of data recorded to log.

    Clustered index on start_time only should not be much worse in terms of query performance, but it will be much easier of the system.

    _____________
    Code for TallyGenerator

  • Sergiy (5/26/2016)


    ChrisM@home (5/26/2016)


    Using a clustered index like so:

    CREATE CLUSTERED INDEX [cx_Stuff] ON [dbo].[ann_events_Tech_Details]

    ([source] ASC, [start_time] ASC)

    Such clustered may be very good on query but it's gonna be a very high cost of maintenance.

    It will become fragmented all the time.

    Splitting pages will slow down inserts, increase IO load and amount of data recorded to log.

    Clustered index on start_time only should not be much worse in terms of query performance, but it will be much easier of the system.

    Absolutely, and this had to be said, thanks Sergiy.

    A covering nonclustered index would be slightly more efficient (more rows per page)

    CREATE INDEX ix_QueryHelper ON ann_events_Tech_Details

    ([source], start_time, )

    INCLUDE (lang, sel_tel_nodialtone, sel_tel_noisy, sel_tech_ckc_disconnect, sel_tech_ckc_transfer, sel_tel_csr, sel_inv_del, sel_inv_unifi)


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Sergiy (5/26/2016)


    jc85 (5/26/2016)


    Sergiy (5/25/2016)


    If that one goes not so bad then incorporate it into the bigger one:

    SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang, SUM(b.sel_test) Test, SUM(b.sel_test2) Test2

    FROM TechDetails b

    WHERE b.start_time >= @StartDate

    AND b.start_time < dateadd(dd,0, datediff(dd,0,@EndDate)+1)

    GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), b.source, b.lang

    SELECT DT.date, (a.source + '-' + a.report_item) as report_item,

    SUM(CASE WHEN b.lang = 'EN' THEN CASE a.report_item WHEN 'Test' THEN Test WHEN 'Test2' THEN Test2 ELSE 0 END ELSE 0 END) EN,

    SUM(CASE WHEN b.lang = 'BM' THEN CASE a.report_item WHEN 'Test' THEN Test WHEN 'Test2' THEN Test2 ELSE 0 END ELSE 0 END) BM

    FROM ReportItem a

    INNER JOIN (

    SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang, SUM(b.sel_test) Test, SUM(b.sel_test2) Test2

    FROM TechDetails b

    WHERE b.start_time >= @StartDate

    AND b.start_time < dateadd(dd,0, datediff(dd,0,@EndDate)+1)

    AND b.lang IN ('EN', 'BM')

    GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), b.source, b.lang

    ) DT ON a.source = b.source

    WHERE a.report_id =8 AND a.flow_name = @flow_name

    AND a.report_item IN ('Test', 'Test2')

    And from looking at the overall design I can conclude the clustered index on ReportItem must be

    (report_id, flow_name, report_item) INCLUDE (source)

    The first one performed well but the second one is getting syntax error "The multi-part identifier b.lang and b.source could not be bound).

    In outer query it must be DT.lang and DT.source, of course.

    I have modified the query to execute successfully based on my limited knowledge.

    However it did not return correct output, can you advise which part of the query need to be changed?

    Thanks.

    SELECT DT.date, (a.source + '-' + a.report_item) as report_item,

    SUM(CASE WHEN DT.lang = 'EN' THEN CASE a.report_item WHEN 'Noisy' THEN 1 WHEN 'Telephony' THEN 1 ELSE 0 END ELSE 0 END) EN,

    SUM(CASE WHEN DT.lang = 'BM' THEN CASE a.report_item WHEN 'Noisy' THEN 1 WHEN 'Telephony' THEN 1 ELSE 0 END ELSE 0 END) BM

    FROM ann_ReportItem a

    INNER JOIN (

    SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang, SUM(b.sel_tel_noisy) Noisy, SUM(b.sel_tech_tel) Telephony

    FROM ann_events_Tech_Details b

    WHERE b.start_time >= @StartDate

    AND b.start_time < dateadd(dd,0, datediff(dd,0,@EndDate)+1)

    AND b.lang IN ('EN', 'BM')

    GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), b.source, b.lang

    ) DT ON a.source = DT.source

    WHERE a.report_id =8 AND a.call_flow_name = @call_flow_name

    AND a.report_item IN ('Noisy', 'Telephony')

    GROUP BY DT.DATE, A.SOURCE, a.report_item

    Output

    2016-02-01 00:00:00.000----CYGAL-Noisy-----1----1

    2016-02-01 00:00:00.000----PFCC-Noisy------1----1

    2016-02-01 00:00:00.000----PV-Noisy---------1----1

  • ChrisM@home (5/27/2016)


    Sergiy (5/26/2016)


    ChrisM@home (5/26/2016)


    Using a clustered index like so:

    CREATE CLUSTERED INDEX [cx_Stuff] ON [dbo].[ann_events_Tech_Details]

    ([source] ASC, [start_time] ASC)

    Such clustered may be very good on query but it's gonna be a very high cost of maintenance.

    It will become fragmented all the time.

    Splitting pages will slow down inserts, increase IO load and amount of data recorded to log.

    Clustered index on start_time only should not be much worse in terms of query performance, but it will be much easier of the system.

    Absolutely, and this had to be said, thanks Sergiy.

    A covering nonclustered index would be slightly more efficient (more rows per page)

    CREATE INDEX ix_QueryHelper ON ann_events_Tech_Details

    ([source], start_time, )

    INCLUDE (lang, sel_tel_nodialtone, sel_tel_noisy, sel_tech_ckc_disconnect, sel_tech_ckc_transfer, sel_tel_csr, sel_inv_del, sel_inv_unifi)

    Which column should I put as index for table ann_ReportItem?

    I've set [source], [call_flow_name] and [report_id] as clustered indexes while [id] as unique nonclustered index.

    As for table ann_events_Tech_Details I've followed your suggestion.

    The query execution takes 6-7 seconds to complete, which is the same as the original.

    '20160101' as StartDate

    '20160301' as EndDate

    Execution Plan is attached for reference, thanks!

    Update: Your query runs slightly faster on the server (not my pc) compared to the original query.

  • jc85 (5/29/2016)


    Which column should I put as index for table ann_ReportItem?

    Clustered index must be set up on column(s) used for range selection.

    Always.

    You select by the range of dates.

    So, your clustered index should be on start_time.

    Maybe on (start_time, source).

    _____________
    Code for TallyGenerator

  • jc85 (5/29/2016)


    I have modified the query to execute successfully based on my limited knowledge.

    However it did not return correct output, can you advise which part of the query need to be changed?

    Thanks.

    You never use values from derived table columns Noisy and Telephony in outer query.

    Should it be this?

    SUM(CASE WHEN DT.lang = 'EN' THEN CASE a.report_item WHEN 'Noisy' THEN Noisy WHEN 'Telephony' THEN Telephony ELSE 0 END ELSE 0 END) EN,

    SUM(CASE WHEN DT.lang = 'BM' THEN CASE a.report_item WHEN 'Noisy' THEN Noisy WHEN 'Telephony' THEN Telephony ELSE 0 END ELSE 0 END) BM

    _____________
    Code for TallyGenerator

  • Sergiy (5/30/2016)


    You never use values from derived table columns Noisy and Telephony in outer query.

    Should it be this?

    SUM(CASE WHEN DT.lang = 'EN' THEN CASE a.report_item WHEN 'Noisy' THEN Noisy WHEN 'Telephony' THEN Telephony ELSE 0 END ELSE 0 END) EN,

    SUM(CASE WHEN DT.lang = 'BM' THEN CASE a.report_item WHEN 'Noisy' THEN Noisy WHEN 'Telephony' THEN Telephony ELSE 0 END ELSE 0 END) BM

    Works perfectly, thanks!

    SELECT DT.date, (a.source + '-' + a.report_item) as report_item,

    SUM(CASE WHEN DT.lang = 'EN' THEN CASE a.report_item WHEN 'No Dial Tone' THEN NoDialTone

    WHEN 'Noisy' THEN Noisy

    WHEN 'CKC End Call' THEN CKCEndCall

    WHEN 'CKC Transfer CSR' THEN CKCTransferCSR

    WHEN 'Tel - Speak to CSR' THEN TelSpeakToCSR

    WHEN 'Inv Num - Del' THEN InvNumDel

    WHEN 'Inv Num - Unifi' THEN InvNumUnifi

    ELSE 0 END ELSE 0 END) EN,

    SUM(CASE WHEN DT.lang = 'BM' THEN CASE a.report_item WHEN 'No Dial Tone' THEN NoDialTone

    WHEN 'Noisy' THEN Noisy

    WHEN 'CKC End Call' THEN CKCEndCall

    WHEN 'CKC Transfer CSR' THEN CKCTransferCSR

    WHEN 'Tel - Speak to CSR' THEN TelSpeakToCSR

    WHEN 'Inv Num - Del' THEN InvNumDel

    WHEN 'Inv Num - Unifi' THEN InvNumUnifi

    ELSE 0 END ELSE 0 END) BM,

    SUM(CASE WHEN DT.lang = 'MD' THEN CASE a.report_item WHEN 'No Dial Tone' THEN NoDialTone

    WHEN 'Noisy' THEN Noisy

    WHEN 'CKC End Call' THEN CKCEndCall

    WHEN 'CKC Transfer CSR' THEN CKCTransferCSR

    WHEN 'Tel - Speak to CSR' THEN TelSpeakToCSR

    WHEN 'Inv Num - Del' THEN InvNumDel

    WHEN 'Inv Num - Unifi' THEN InvNumUnifi

    ELSE 0 END ELSE 0 END) MD

    FROM ann_ReportItem a

    INNER JOIN (

    SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang,

    SUM(CONVERT(INT, b.sel_tel_nodialtone)) NoDialTone,

    SUM(CONVERT(INT, b.sel_tel_noisy)) Noisy,

    SUM(CONVERT(INT, b.sel_tech_ckc_disconnect)) CKCEndCall,

    SUM(CONVERT(INT, b.sel_tech_ckc_transfer)) CKCTransferCSR,

    SUM(CONVERT(INT, b.sel_tel_csr)) TelSpeakToCSR,

    SUM(CONVERT(INT, b.sel_inv_del)) InvNumDel,

    SUM(CONVERT(INT, b.sel_inv_unifi)) InvNumUnifi

    FROM ann_events_Tech_Details b

    WHERE b.start_time >= @StartDate

    AND b.start_time < dateadd(dd,0, datediff(dd,0,@EndDate)+1)

    AND b.lang IN ('EN', 'BM', 'MD')

    GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), b.source, b.lang

    ) DT ON a.source = DT.source

    WHERE a.report_id =8 AND a.call_flow_name = @call_flow_name

    AND a.report_item IN ('No Dial Tone','Noisy', 'CKC End Call', 'CKC Transfer CSR', 'Tel - Speak to CSR', 'Inv Num - Del', 'Inv Num - Unifi')

    GROUP BY DT.DATE, A.SOURCE, a.report_item

    Fastest query method with these indexes:

    --ann_events_Tech_Details

    CREATE CLUSTERED INDEX [IX_Clustered] ON [dbo].[ann_events_Tech_Details]

    (

    [start_time] ASC,

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    GO

    ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [PK_ann_events_Tech_Details] PRIMARY KEY NONCLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    GO

    --ann_ReportItem

    ALTER TABLE [dbo].[ann_ReportItem] ADD CONSTRAINT [PK_ann_ReportItem] PRIMARY KEY CLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    GO

    (2149 row(s) affected)

    Table 'ann_ReportItem'. Scan count 7, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'ann_events_Tech_Details'. Scan count 9, logical reads 66203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 9422 ms, elapsed time = 1469 ms.

    Slower but uses less CPU time query method with these indexes:

    --ann_events_Tech_Details

    CREATE NONCLUSTERED INDEX [IX_QueryHelper] ON [dbo].[ann_events_Tech_Details]

    (

    [lang] ASC,

    [start_time] ASC

    )

    INCLUDE ( [source],

    [sel_tel_nodialtone],

    [sel_tel_noisy],

    [sel_tel_csr],

    [sel_tech_ckc_disconnect],

    [sel_tech_ckc_transfer],

    [sel_inv_del],

    [sel_inv_unifi]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [PK_ID] PRIMARY KEY NONCLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    --ann_ReportItem

    CREATE NONCLUSTERED INDEX [ix_RItem] ON [dbo].[ann_ReportItem]

    (

    [source] ASC,

    [call_flow_name] ASC,

    [report_id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[ann_ReportItem] ADD CONSTRAINT [PK_ReportItem_ID] PRIMARY KEY NONCLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    (2149 row(s) affected)

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'ann_events_Tech_Details'. Scan count 3, logical reads 12602, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'ann_ReportItem'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 4290 ms, elapsed time = 4427 ms.

    Both methods are good enough to me, any suggestion/feedback would be appreciated.

  • Glad it helped.

    Thanks for the feedback.

    I'd recommend a clustered index on ann_ReportItem like this:

    CREATE NONCLUSTERED INDEX [ix_RItem] ON [dbo].[ann_ReportItem]

    (

    [report_id] ASC,

    [call_flow_name] ASC,

    [source] ASC

    _____________
    Code for TallyGenerator

  • WHERE month(b.start_time) = @Loc_Month

    AND year(b.start_time) = @Loc_Year

    Is there any other way to avoid using function in WHERE clause in this scenario?

    It doesn't have huge impact on query speed but as pointed out earlier, it is not a good practice.

  • jc85 (5/31/2016)


    WHERE month(b.start_time) = @Loc_Month

    AND year(b.start_time) = @Loc_Year

    Is there any other way to avoid using function in WHERE clause in this scenario?

    It doesn't have huge impact on query speed but as pointed out earlier, it is not a good practice.

    Yes, of course.

    Build @StartDate and @EndDate out of @Loc_Year and @Loc_Month and... You know what to do then.

    _____________
    Code for TallyGenerator

  • Sergiy (5/31/2016)


    jc85 (5/31/2016)


    WHERE month(b.start_time) = @Loc_Month

    AND year(b.start_time) = @Loc_Year

    Is there any other way to avoid using function in WHERE clause in this scenario?

    It doesn't have huge impact on query speed but as pointed out earlier, it is not a good practice.

    Yes, of course.

    Build @StartDate and @EndDate out of @Loc_Year and @Loc_Month and... You know what to do then.

    Sorry for not being clear enough, this is meant to use as a monthly report so I would need to do a month and year comparison.

    User will select a single date and it will be passed as @Date.

    My idea is to declare 2 local variables @Loc_Month and @Loc_Year to grab month and year from @Date and then use them to do comparison.

    Date (start_time)--report_item----EN (lang)---BM (lang)

    2015-01-------Noisy----------------------3------------- 0

    2015-01-------No Dial Tone-------------6--------------2

    2015-02-------Noisy----------------------1--------------2

    2015-02-------No Dial Tone-------------0------------- 7

Viewing 15 posts - 46 through 60 (of 76 total)

You must be logged in to reply to this topic. Login to reply