Post reply

performance improvement

  • October 12, 2015 at 1:10 am

    #308788

    Performance issue on Query. Here is query which i ran and it takes around 7-8 min to give the output. Could you please suggest the alternate way to write it.

    SELECT COUNT,MESS,

    count(distinct(CASE WHEN EVTIME=@D1 THEN EVENTUID ELSE null END)) AS 'D1'

    ,count(distinct(CASE WHEN EVTIME=@D2 THEN EVENTUID ELSE null END)) AS 'D2'

    ,count(distinct(CASE WHEN EVTIME =@D3 THEN EVENTUID ELSE null END)) AS 'D3'

    ,count(distinct(CASE WHEN EVTIME =@D4 THEN EVENTUID ELSE null END)) AS 'D4'

    ,count(distinct(CASE WHEN EVTIME =@D5 THEN EVENTUID ELSE null END)) AS 'D5'

    ,count(distinct(CASE WHEN EVTIME =@D6 THEN EVENTUID ELSE null END)) AS 'D6'

    ,count(distinct(CASE WHEN EVTIME =@D7 THEN EVENTUID ELSE null END)) AS 'D7'

    ,count(distinct(CASE WHEN EVTIME =@D8 THEN EVENTUID ELSE null END)) AS 'D8'

    ,count(distinct(CASE WHEN EVTIME =@D9 THEN EVENTUID ELSE null END)) AS 'D9'

    ,count(distinct(CASE WHEN EVTIME =@D10 THEN EVENTUID ELSE null END)) AS 'D10'

    ,count(distinct(CASE WHEN EVTIME =@D11 THEN EVENTUID ELSE null END)) AS 'D11'

    ,count(distinct(CASE WHEN EVTIME =@D12 THEN EVENTUID ELSE null END)) AS 'D12'

    ,count(distinct(CASE WHEN EVTIME =@D13 THEN EVENTUID ELSE null END)) AS 'D13'

    ,count(distinct(CASE WHEN EVTIME =@D14 THEN EVENTUID ELSE null END)) AS 'D14'

    FROM DBO.#TEMP C WITH(NOLOCK)

    GROUP BY COUNT,MESS

    ORDER BY 1,2

  • October 12, 2015 at 6:36 am

    #1832795

    There are a few things...

    #1) There's no need to use the NOLOCK hint here. It's a temp table which means you have exclusive access to it.

    #2) Why are are you doing distinct counts? The name,"EVENTUID", implies that this column already has unique values.

    #3) The biggie... Index your temp table. COUNT & MESS should be the first two key columns... If the distinct is necessary, then EVENTUID should be the 3rd key column. If not, then it can be an "included" column.

  • October 12, 2015 at 6:44 am

    #1832796

    sushil_dwid (10/12/2015)

    Performance issue on Query. Here is query which i ran and it takes around 7-8 min to give the output. Could you please suggest the alternate way to write it.

    SELECT COUNT,MESS,

    count(distinct(CASE WHEN EVTIME=@D1 THEN EVENTUID ELSE null END)) AS 'D1'

    ,count(distinct(CASE WHEN EVTIME=@D2 THEN EVENTUID ELSE null END)) AS 'D2'

    ,count(distinct(CASE WHEN EVTIME =@D3 THEN EVENTUID ELSE null END)) AS 'D3'

    ,count(distinct(CASE WHEN EVTIME =@D4 THEN EVENTUID ELSE null END)) AS 'D4'

    ,count(distinct(CASE WHEN EVTIME =@D5 THEN EVENTUID ELSE null END)) AS 'D5'

    ,count(distinct(CASE WHEN EVTIME =@D6 THEN EVENTUID ELSE null END)) AS 'D6'

    ,count(distinct(CASE WHEN EVTIME =@D7 THEN EVENTUID ELSE null END)) AS 'D7'

    ,count(distinct(CASE WHEN EVTIME =@D8 THEN EVENTUID ELSE null END)) AS 'D8'

    ,count(distinct(CASE WHEN EVTIME =@D9 THEN EVENTUID ELSE null END)) AS 'D9'

    ,count(distinct(CASE WHEN EVTIME =@D10 THEN EVENTUID ELSE null END)) AS 'D10'

    ,count(distinct(CASE WHEN EVTIME =@D11 THEN EVENTUID ELSE null END)) AS 'D11'

    ,count(distinct(CASE WHEN EVTIME =@D12 THEN EVENTUID ELSE null END)) AS 'D12'

    ,count(distinct(CASE WHEN EVTIME =@D13 THEN EVENTUID ELSE null END)) AS 'D13'

    ,count(distinct(CASE WHEN EVTIME =@D14 THEN EVENTUID ELSE null END)) AS 'D14'

    FROM DBO.#TEMP C WITH(NOLOCK)

    GROUP BY COUNT,MESS

    ORDER BY 1,2

    COUNT(DISTINCT...) suggests that your temp table could be constructed differently for better results. During preparation of your temp table, try this level of aggregation:

    GROUP BY Count, Mess, EVTIME, EVENTUID

    then for each output aggregate, something like this:

    SUM(CASE WHEN EVTIME =@D13 THEN 1 ELSE 0 END) AS 'D13'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 12, 2015 at 3:22 pm

    #1832886

    When you create the temp table, i.e before it's loaded, cluster it on ( COUNT, MESS ). A nonclustered index will not be efficient. That will speed up your query considerably.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 12, 2015 at 3:46 pm

    #1832889

    sushil_dwid (10/12/2015)

    Performance issue on Query. Here is query which i ran and it takes around 7-8 min to give the output. Could you please suggest the alternate way to write it.

    SELECT COUNT,MESS,

    count(distinct(CASE WHEN EVTIME=@D1 THEN EVENTUID ELSE null END)) AS 'D1'

    ,count(distinct(CASE WHEN EVTIME=@D2 THEN EVENTUID ELSE null END)) AS 'D2'

    ,count(distinct(CASE WHEN EVTIME =@D3 THEN EVENTUID ELSE null END)) AS 'D3'

    ,count(distinct(CASE WHEN EVTIME =@D4 THEN EVENTUID ELSE null END)) AS 'D4'

    ,count(distinct(CASE WHEN EVTIME =@D5 THEN EVENTUID ELSE null END)) AS 'D5'

    ,count(distinct(CASE WHEN EVTIME =@D6 THEN EVENTUID ELSE null END)) AS 'D6'

    ,count(distinct(CASE WHEN EVTIME =@D7 THEN EVENTUID ELSE null END)) AS 'D7'

    ,count(distinct(CASE WHEN EVTIME =@D8 THEN EVENTUID ELSE null END)) AS 'D8'

    ,count(distinct(CASE WHEN EVTIME =@D9 THEN EVENTUID ELSE null END)) AS 'D9'

    ,count(distinct(CASE WHEN EVTIME =@D10 THEN EVENTUID ELSE null END)) AS 'D10'

    ,count(distinct(CASE WHEN EVTIME =@D11 THEN EVENTUID ELSE null END)) AS 'D11'

    ,count(distinct(CASE WHEN EVTIME =@D12 THEN EVENTUID ELSE null END)) AS 'D12'

    ,count(distinct(CASE WHEN EVTIME =@D13 THEN EVENTUID ELSE null END)) AS 'D13'

    ,count(distinct(CASE WHEN EVTIME =@D14 THEN EVENTUID ELSE null END)) AS 'D14'

    FROM DBO.#TEMP C WITH(NOLOCK)

    GROUP BY COUNT,MESS

    ORDER BY 1,2

    7-8 minutes for how many rows?


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • October 14, 2015 at 11:42 pm

    #1833368

    I can not create cluster because it has duplicate records

  • October 15, 2015 at 1:24 am

    #1833371

    sushil_dwid (10/14/2015)

    I can not create cluster because it has duplicate records

    Clustered indexes don't have to be unique.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 15, 2015 at 7:29 am

    #1833484

    One other suggestion. Not going to make a difference for performance but you should get out of the habit of ordering by ordinal position. You should always use the column name. If your columns change and you forget to change the order by the rows will be returned in a different order.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 26, 2015 at 2:35 am

    #1835457

    Thanks for your replies. I used few of your suggestions

    1.Applied unique cluster index on temp table on COUNT,MESS columns.

    2.Used group by while inserting the data into temp table

    3.Remove distinct from case statement.

    It worked for me and giving response in seconds.. thanks again

Viewing 9 posts - 1 through 8 (of 8 total)

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