Query optimization-1

1

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

SELECT
Account_Ref
,Year
,Week
,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
FROM
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)

2

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.

3

Thanks for the reply james

4

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

1 Like