Does anyone have any suggestions on making the following select statement more efficient? It is a very simple query but the SSRS using the code hits the timeout set.
SELECT
G.A
,B = IsNull(Avg(CAST(T.B as decimal)), 0)
,C = Sum(T.C)
,D = IsNull(Avg(CAST(T.D as decimal)), 0)
FROM
TableA as G
INNER JOIN
TableB as T ON T.An_ID = G.An_ID
group by G.A
This is a snippet of the code with identical grouping in the bigger script of the SP that the SSRS is using.
For this query:
SELECT G.A, B = IsNull(Avg(CAST(T.B as decimal)), 0),
C = Sum(T.C), D = IsNull(Avg(CAST(T.D as decimal)), 0)
FROM TableA G INNER JOIN
TableB T
ON T.An_ID = G.An_ID
GROUP BY G.A;
You want indexes on TableB(An_ID) and TableA(An_Id, A).
My guess is that the joins are producing a very large number of intermediate rows. You can get the count by doing:
select sum(a.cnt * b.cnt)
from (select an_id, count(*) as cnt from tablea group by an_id) a join
(select an_id, count(*) as cnt from tableb group by an_id) b
on a.an_id = b.an_id;
You can find the offensive combinations:
select top 100 a.cnt * b.cnt, a.an_id
from (select an_id, count(*) as cnt from tablea group by an_id) a join
(select an_id, count(*) as cnt from tableb group by an_id) b
on a.an_id = b.an_id
order by a.cnt * b.cnt desc
group by, andg.ais not in an aggregation function.