CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Keneni
Keneni

Reputation: 705

SQL Server 2012 - Optimizing SQL query with functions in select statement

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.

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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

Upvotes: 1

Related Questions