December 14, 2015 at 5:54 am
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
deleted!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 14, 2015 at 7:25 am
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
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
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
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 14, 2015 at 10:05 am
Yes,
Index Seek (NonClustered)
December 14, 2015 at 10:12 am
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
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
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
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 15, 2015 at 2:47 am
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
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
December 15, 2015 at 3:14 am
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
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