SQLTeam.com | Weblogs | Forums

Query optimization-1


HI Friends,
I would really appreciate if you could help me to optimise the below query.

,SUM(Amount) AS Amount
,SUM(Debit) AS Debit
,SUM(Received) AS Received
,SUM(Write_Off) AS Write_Off
,SUM(Rec_Former_Acc) AS Rec_Former_Acc
,SUM(Rec_Current_Acc) AS Rec_Current_Acc
,SUM(Rec_HB) AS Rec_HB
,SUM(Rec_SP) AS Rec_SP
,SUM(Rec_Acc_Holder) AS Rec_Acc_Holder
,SUM(Debit_Standard) AS Debit_Standard
,SUM(Debit_Adjustment) AS Debit_Adjustment
dbo.Transaction AS Trans
GROUP BY Account_Ref, Year, Week
HAVING (SUM(Debit) <> 0) OR
(SUM(Received) <> 0) OR
(SUM(Write_Off) <> 0) OR
(SUM(Rec_Former_Acc) <> 0) OR
(SUM(Rec_Current_Acc) <> 0) OR
(SUM(Rec_HB) <> 0) OR
(SUM(Rec_SP) <> 0) OR
(SUM(Rec_Acc_Holder) <> 0) OR
(SUM(Debit_Standard) <> 0) OR
(SUM(Debit_Adjustment) <> 0)


Optimization comes into picture only if you are limiting the number of rows returned via a WHERE clause, or if you are joining two or more tables (again limiting the number of rows returned). In your case, you are returning all the rows from a single table. There is nothing that you can/need to do to optimize.


Thanks for the reply james


IF the dbo.Transaction table is properly clustered on (Year, Week) [rather than being lazily clustered on an, say, an identity column] then changing the GROUP BY should help performance considerably:

GROUP BY Year, Week, Account_Ref