SQL Server Performance

SQL query performance

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by ralph1972, Oct 2, 2006.

  1. ralph1972 New Member

    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
  2. Adriaan New Member

    Please do not triple-post ...
  3. rabehma New Member

    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.

Share This Page