Last post Mar 19, 2015 02:28 PM by vikasrulez
Mar 17, 2015 05:14 AM|kaviyarasan|LINK
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.
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.
how to optimize this select with sum() query? please advice me.
Mar 17, 2015 05:33 AM|PatriceSc|LINK
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...
Mar 17, 2015 09:27 AM|kaviyarasan|LINK
Thanks for the reply.
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.
Please Advice me about Trim() and Group by relationship.
Mar 17, 2015 11:01 PM|Edwin Guru Singh|LINK
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
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
Mar 19, 2015 02:09 PM|PatriceSc|LINK
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.
Mar 19, 2015 02:28 PM|vikasrulez|LINK