Post reply

Query improvement

  • July 5, 2019 at 5:47 am

    #3660111

    Hi All,

    We have below query which is taking quite a long time.

    Is there a better way to re-write the query.

    Actual execution plan,

    https://www.brentozar.com/pastetheplan/?id=rk1vMwnlr

    Attaching table DDL and Index DDL.

     

    Opened SSMS and ran the below query

    set statistics io on

    set statistics time on

    select rowid_object from c_b_contact_xref group by rowid_object

    having count(distinct rowid_system) > 5

    (1388 row(s) affected)

    Table 'C_B_CONTACT_XREF'. Scan count 1, logical reads 48037, 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 = 6703 ms, elapsed time = 7173 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

     

    Thanks,

    Sam

    • This topic was modified 6 years, 2 months ago by vsamantha35.

    Attachments:
    You must be logged in to view attached files.

  • July 5, 2019 at 12:04 pm

    #3660173

    It's only reading the index so it seems about as efficient as it could get.

    Have you tried rebuilding the index?

  • July 5, 2019 at 3:34 pm

    #3660237

    Jonathan, are you suspecting index is fragmented?

  • July 5, 2019 at 4:14 pm

    #3660251

    Yes, that was one thought. I don't really understand why that query is taking 7 seconds to complete when it's just reading an index with two 14 character columns on a 4 million row table.

    It might be worth rebuilding it with a fill factor of 100 (if the table doesn't get too many updates), the index would then be on fewer pages so it should be faster to read.

    You could also rewrite the query like this, though I'm not sure if it would change the execution plan:

    ;with cte as
    (
        select distinct rowid_object, rowid_system
          from c_b_contact_xref 
    )
    select cte.rowid_object
      from cte
     group by cte.rowid_object
    having count(*) > 5

  • July 5, 2019 at 4:58 pm

    #3660261

    I agree with Jonathan: you've already got the exact index needed to support that query.

    What specific version and edition of SQL Server are you on?

    In particular, does the specific version and edition of SQL Server you are on support data compression?

    If so, rebuild the index with page compression and try again.

    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".

  • July 6, 2019 at 5:36 pm

    #3660452

    Scott, in what way page compression can help here? does it again cause cpu spike? does it need repetitive data?

  • July 6, 2019 at 5:37 pm

    #3660453

    Thanks Jonathan.

Viewing 7 posts - 1 through 7 (of 7 total)

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