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

Member

79 Points

113 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

All-Star

38050 Points

11818 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

Member

79 Points

113 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

Star

8536 Points

1376 Posts

Microsoft

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

All-Star

38050 Points

11818 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

Participant

877 Points

299 Posts