Using count(*) performance

  • 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

  • deleted!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Yes,

    Index Seek (NonClustered)

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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
  • 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

  • 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