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 NET
FROM 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.