Post reply

Using count(*) performance

  • December 14, 2015 at 5:54 am

    #399469

    I have a query which execute 'counts' like the following:

    select

    col1

    col2

    col3_no = (select count(*) from MyTable2 tbl2 where tbl2.col1 = tbl1.col2),

    col4_no = (select count(*) from MyTable3 tbl3 where tbl3.col1 = tbl1.col2)

    from

    MyTable1 tbl1

    Sometimes I get the error:

    System.Data.SqlClient.SqlException: Timeout expired. The timeout

    period elapsed prior to completion of the operation or the server is not responding

    1. Could this be related to the 'count' query being blocked by other reading queries?

    2. Is this a good practice? Using the count instead of having a physical column 'col3_no'

    which value is updated always when a new matching row is inserted in MyTable2?

    Thank you in advance

  • December 14, 2015 at 6:35 am

    #1845790

    deleted!

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 14, 2015 at 7:25 am

    #1845812

    1) Yeah, possibly. Hard to say without seeing the execution plan and wait statistics. I'd for sure collect those to understand what's happening here.

    2) No, the COUNT(*) is a fine way to collect that information. Doing it from within a correlated sub-query like you have here might not be the best approach. It really depends on how the optimizer is choosing to resolve these queries, indexes involved, etc.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 14, 2015 at 9:52 am

    #1845857

    This error does not happen always.

    Looking at the execution plan it displays among others:

    Nested Loops(Inner join) 0% < compute scalar 0% < stream aggregate 0% < Index Seek (NonClustered) 8%

    Any advice to replace the correlated query?

    Thanks

  • December 14, 2015 at 9:55 am

    #1845861

    Do you have a nonclustered, nonfiltered index containing tbl2.col1?

    A nonclustered, nonfiltered index containing tbl3.col1?

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

  • December 14, 2015 at 10:02 am

    #1845864

    sql.queries (12/14/2015)

    ...

    Any advice to replace the correlated query?

    Thanks

    You could try left-joining to a CTE:

    with t2Counts as (select t2.col1, ctcol1 = count(t2.*) from MyTable2 t2 group by t2.col1)

    select

    col1,

    col2,

    col3_no = IsNull(t2Counts.ctcol1,0),

    col4_no = same pattern as for t2

    from

    MyTable1 t1

    left join t2Counts on t1.col2 = t2Counts.col1

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 14, 2015 at 10:05 am

    #1845866

    Yes,

    Index Seek (NonClustered)

  • December 14, 2015 at 10:12 am

    #1845869

    If you have indexes keyed on tbl2.col1 and on tbl3.col1, then it almost has to be some type of blocking. You could try the query below, might work better, might not, depending on the specifics of your situation:

    select

    col1

    col2

    tbl2.col1_count,

    tbl3.col1_count

    from

    MyTable1 tbl1

    left outer join (

    select col1, count(*) as col1_count

    from MyTable2

    group by col1

    ) as tbl2 on tbl2.col1 = tbl1.col2

    left outer join (

    select col1, count(*) as col1_count

    from MyTable3

    group by col1

    ) as tbl3 on tbl3.col1 = tbl1.col2

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

  • December 14, 2015 at 10:50 am

    #1845880

    Rather than a correlated sub-query, you could take a stab at simply select from tbl1, tbl2, tbl3 and then wrap another aggregate query around it; in other words a non-correlated sub-query. It's still not something I'd want to see in an OLTP database, so I'm assuming this is some type of reporting or DW query that only gets executed infrequently.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • December 14, 2015 at 11:11 am

    #1845893

    Thanks.

    Would the 'group by' solution, grouping all results from MyTable2, be a better solution than the correlated query?

    It is not an OLTP database and the query is executed frequently, although the error is not frequent.

  • December 14, 2015 at 11:17 am

    #1845894

    sql.queries (12/14/2015)

    Thanks.

    Would the 'group by' solution, grouping all results from MyTable2, be a better solution than the correlated query?

    It is not an OLTP database and the query is executed frequently, although the error is not frequent.

    Perhaps. You should test both and compare the results / query plans.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 15, 2015 at 2:47 am

    #1846022

    The execution plan with group by is using a 'Index Scan (NonClustered)', so I think the index seek in the correlated query is better.

    Thanks

  • December 15, 2015 at 3:04 am

    #1846025

    Don't just look at the execution plan operators and assume which is better. Test. Get actual metrics and compare numbers (and I don't mean cost %).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 15, 2015 at 3:14 am

    #1846028

    GilaMonster (12/15/2015)

    Don't just look at the execution plan operators and assume which is better. Test. Get actual metrics and compare numbers (and I don't mean cost %).

    THIS!

    Scans are not necessarily bad. Seeks are not necessarily good. It's down to why is it doing either and which one is appropriate to the situation, your query and your data.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 15, 2015 at 7:04 am

    #1846071

    I think I will need to study execution plans to accomplish this. Any advice where to start?

    Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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