# How to Optimize the Select Sum() query from 1 million of records?RSS

## 5 replies

Last post Mar 19, 2015 02:28 PM by vikasrulez

• kaviyarasan

Member

79 Points

109 Posts

### How to Optimize the Select Sum() query from 1 million of records?

Hi All,

I have the Database with 1 million of rows and Select query with sum() operation from three tables, but this simple select with aggregate function query takes ~18 seconds for execution. And also that query results will be only 16 rows.

Query is,

```SELECT table1.PId,table1.PName,
RTrim(table1.CName) Ctgry,
RTrim(table1.SName) Size,
RTrim(table1.code) Code,
ROUND(SUM(table2.OpCost),3) OpCost,
ROUND(SUM(table2.ICost),3) ICost,
ROUND(SUM(table2.CoCost),3) CoCost,
ROUND(SUM(table2.P1Cost),3) P1Cost,
ROUND(SUM(table2.P2Cost),3) P2Cost,
ROUND(SUM(table2.SCost),3) SCost

FROM table1
LEFT OUTER JOIN table2 ON
table1.TypeId = table2.TypeId  AND table1.PId = table2.PId LEFT OUTER JOIN table3 ON
table1.TypeId = table3.TypeId  AND table1.PId = table3.PId

WHERE table1.PId=  1
GROUP BY table1.PId ,table1.PName ,RTrim(table1.CName) ,RTrim(table1.SName) ,table1.Code
ORDER BY Code```

Note: Table1 only has the 1 million rows.

Thanks.

optimization query sql

• PatriceSc

All-Star

43240 Points

14967 Posts

### Re: How to Optimize the Select Sum() query from 1 million of records?

Hi,

Your best bet is always to see how it is done -  see https://msdn.microsoft.com/en-us/library/ms187735.aspx  - rather than to just look at the query itself.

Check that you have the proper index on your joins (BTW it seems table3 is joined by that you don't use anything from this table ? what if you omit this join ?). If you have an index the GROUP BY could use, using TRIM will prevent using it. Finally my approach is also to rebuild my query step by step - possibly startiing with a COUNT(*) - to see each how it performs as I introduce each element I need and spot possible issues...

optimization query sql

• kaviyarasan

Member

79 Points

109 Posts

### Re: How to Optimize the Select Sum() query from 1 million of records?

All the three tables, Primary key column having the clustered index and some non primary columns having the non-clustered index.

I have tried following,
1. Omitted the table 3 join, but no luck.

2. Omitted the table 3 join and Added the RTrim to table1.Code (one of the Group by field), now the query execution takes the 5 seconds.

Thanks.

optimization query sql

• Edwin Guru S...

Star

8524 Points

1376 Posts

### Re: How to Optimize the Select Sum() query from 1 million of records?

Mar 17, 2015 11:01 PM|Edwin Guru Singh|LINK

#### kaviyarasan

Omitted the table 3 join and Added the RTrim to table1.Code (one of the Group by field), now the query execution takes the 5 seconds. Please Advice me about Trim() and Group by relationship

As per your case, you can try like below query :

```SELECT A.PId,A.PName,
A.Ctgry,A.Size,A.Code,
ROUND(SUM(A.OpCost),3)[OpCost],
ROUND(SUM(A.ICost),3)[ICost],
ROUND(SUM(A.CoCost),3)[CoCost],
ROUND(SUM(A.P1Cost),3)[P1Cost],
ROUND(SUM(A.P2Cost),3)[P2Cost],
ROUND(SUM(A.SCost),3)[SCost]
from

(SELECT table1.PId,table1.PName,
RTrim(table1.CName)[Ctgry],
RTrim(table1.SName)[Size],
RTrim(table1.code)[Code],
table2.OpCost,table2.ICost,table2.CoCost,table2.P1Cost,table2.P2Cost,table2.SCost
FROM table1
LEFT OUTER JOIN table2 ON  table1.TypeId = table2.TypeId  AND table1.PId = table2.PId
WHERE table1.PId=  1 )[A]

GROUP BY A.PId,A.PName,A.Ctgry,A.Size,A.Code
ORDER BY A.Code```

--
with regards,
Edwin

optimization query sql

with regards,
Edwin
• PatriceSc

All-Star

43240 Points

14967 Posts

### Re: How to Optimize the Select Sum() query from 1 million of records?

This is not particular to "group by". The point is that using a function doesn't allow to use an index you already have (or that you could create to speed up things) as you are not using any more the column value found inside this index.

optimization query sql

• vikasrulez

Participant

877 Points

297 Posts