SQL Server Performance Forum – Threads Archive
SQL query performance
Hello, We developped a POS application where in the reporting the user wants to see a list with for all the articles the sales grouped by month. In the sales detail table there are 2 million detail lines. I tried a sample query for the sales for this year grouped SELECT TransactionDetails.DetailItem, TransactionDetails.DetailItemCol, TransactionDetails.DetailItemWs, sum(TransactionDetails.DetailAmountNet) as NETFROM TransactionHeaders INNER JOIN TransactionDetails ON (TransactionHeaders.WsRoot = TransactionDetails.DetailHeaderWs) AND (TransactionHeaders.Colroot = TransactionDetails.DetailHeaderCol) AND (TransactionHeaders.HeaderNumber = TransactionDetails.DetailHeader)
where headerdatecreate > ’01/01/2006′
group by TransactionDetails.DetailItem, TransactionDetails.DetailItemCol, TransactionDetails.DetailItemWs And it took 1 min 30 to 2 min to complete it. How would you solve this problem? The user give a date range and wants to see a list
with each article and then next to it totals by month, or by week,…
fe JAN FEB MRT … DEC TOTALS
ART A 1 2 399 … 52 454
….
ART B 67 86 19 … 98 270 Totals 68 88 418 … 150 724 This is a sample report that I need. Thanks Ralph
Please do not triple-post …
Aggregate 2M records takes time, so doing that in an online application needs you to use an alternative solutions :
1. Aggregating table updated by triggers. The headerdatecreate field could be daily.
2. Indexed view
3. Olap cubes updating (refreshed) periodically, and access the aggregation using MDX.
]]>