SQL Server Performance

SQL performance

Discussion in 'General Developer Questions' 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 sec. 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,...

    Thanks

    Ralph
  2. Adriaan New Member

    A column called HeaderDateCreate isn't likely to be a candidate for having its own index. If this is a critical report that you always filter on HeaderDateCreate, you should make sure you have the proper index in place.
  3. ralph1972 New Member

    We need this date to get a range of sales for a given date.


    The user gives fe 01/01/06 -> 31/12/06 on month base

    So a report appears:
    JAN FEB MRT .... DEC
    ART A 1 2 2 44
    ...
    ART B 7 9 19 99

    How would this be best implemented?

    Ralph
  4. Adriaan New Member

    Does the report take 1 min. 30 sec., or does the query take 1 min. 30 sec.?

    The report you describe is not based on the query you posted, at least not without a conversion to the cross-tab format.
  5. ralph1972 New Member

    It is the time the qry needs in SQL Qry analyzer.

    This is not the report itself. We're testing how long a query would take to execute.

    And we think this is slow.

    How are such problems taken on? What is the best way to get such a list?

    Ralph
  6. Adriaan New Member

    Try adding an index to the table, on the HeaderDateCreate column alone.
  7. ralph1972 New Member

    There is an index on that field.

  8. Twan New Member

    in the header table, how many rows, and of those how many meet the date criteria?

    Do you have an index on the combination of the details table columns detailheader, detailheadercol, detailheaderws

    how many of the rows from the detail table will be selected as part of this criteria?

    what is the primary key of the detail table?

    the statement doesn't seem to be grouping by month, so how are you expecting this to be done?

    Cheers
    Twan
  9. Adriaan New Member

    Does TransactionDetails have an index on (DetailItem, DetailItemCol, DetailItemWs)?

    What is the FK on TransactionDetails that refers to the PK of TransactionHeader? If it is DetailItemWs, then the above-mentioned index should be on (DetailItemWs, DetailItem, DetailItemCol).
  10. ralph1972 New Member

    There are 2,5 million detail lines in the detail table.

    There is an index on TransactionDetails.DetailItem, TransactionDetails.DetailItemCol, TransactionDetails.DetailItemWs

    and FK :TransactionDetails.DetailHeader,TransactionDetails.DetailHeaderCol,TransactionDetails.DetailHeaderWs

  11. Adriaan New Member

    What is the PK on TransactionHeader?
  12. ralph1972 New Member

    PK on TransactionHeader is :
    TransactionHeaders.HeaderNumber, TransactionHeaders.Colroot, TransactionHeaders.WsRoot

    Or would it be usefull to use a totalization table ?

    The problem is that the user can ask totals by month, week, year,...


  13. Twan New Member

    Hi ya,

    so detailitem, detailitemcol, detailitemws is the primary key for the detail table?

    if so then your query is returning all 2.5million rows and not really summarising anything?

    Cheers
    Twan

  14. ralph1972 New Member

    I would like to group on the detail ID's

    So that I get a total by ID

    The problem is here that I then need to run multiple queries.

    Or how would you solve such problem

    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
  15. Twan New Member

    So we've got...

    ART = detailitem col
    A and B = detailitemws

    and the months at the top can be weeks, months or years
    and the date range is user specified, so a monthly report could cover say 2 years?)

    Is that right so far?

    Cheers
    Twan
  16. ralph1972 New Member

    Yez

    You are correct.

    If an user select fe 01/01/2005 -> 31/12/2007 on month base
    JAN/05 FEB/05 .... DEC/07
    ART .... ... ... ....

    How are these problems normally solved?

    - By running against details?

    - Or by using a summary total table by article

  17. Twan New Member

    To do this sort of data analysis I'd probably resort to a summary indexed view, table or database

    the basic query for this sort of thing would be something like



    SELECT
    datepart( year, headerdatecreate ) as year,
    datepart( month, headerdatecreate ) as month
    td.DetailItemCol,
    td.DetailItemWs,
    sum(td.DetailAmountNet) as NET
    FROMTransactionHeaders th
    INNER JOIN TransactionDetails td
    ON th.WsRoot = td.DetailHeaderWs
    AND th.Colroot = td.DetailHeaderCol
    AND th.HeaderNumber = td.DetailHeader
    whereheaderdatecreate > '01/01/2006'
    group by
    datepart( year, headerdatecreate ),
    datepart( month, headerdatecreate ),
    td.DetailItemCol,
    td.DetailItemWs
    order by
    datepart( year, headerdatecreate ),
    datepart( month, headerdatecreate ),
    td.DetailItemCol,
    td.DetailItemWs


    indexes on

    TransactionDetails( DetailHeader, DetailHeaderCol, DetailHeaderWs )
    TransactionHeaders( headerdatecreate, HeaderNumber, ColRoot, wsRoot )

    Cheers
    Twan
  18. ralph1972 New Member

    I changed the query to :
    SELECTdatepart( year, headerdatecreate ) as year,
    datepart( month, headerdatecreate ) as month,
    td.DetailItem ,td.DetailItemCol,
    td.DetailItemWs,
    sum(td.DetailAmountNet) as NET
    FROMTransactionHeaders th INNER JOIN TransactionDetails td
    ON th.WsRoot = td.DetailHeaderWs
    AND th.Colroot = td.DetailHeaderCol
    AND th.HeaderNumber = td.DetailHeader
    whereheaderdatecreate > '01/01/2006'
    group by datepart( year, headerdatecreate ),
    datepart( month, headerdatecreate ),
    td.DetailItem,
    td.DetailItemCol,
    td.DetailItemWs
    order by datepart( year, headerdatecreate ),
    datepart( month, headerdatecreate ),
    td.DetailItem,
    td.DetailItemCol,
    td.DetailItemWs

    I added the td.DetailItem field.

    I tested it and it took around 1min 20 sec results : 38000 rows.

    How would you solve the week / day issue? This is really a good time.



  19. Twan New Member

    for week and day you can use a different datepart week or day respectively. It would mean having three queries (ideally stored procs)

    for day in particular it would be important to be able to have some sort of limit on the daterange, since a 5 year daily totals across all product ranges is surely not that useful anymore...

    even 38000 rows still sounds like way too much to me... if this was a regaulr requirement then I'd consider having summary tables generated nightly to be the source for these to avoid having conflicts with your OLTP

    Cheers
    Twan
  20. ralph1972 New Member

    What is OLTP?

    I thaught 38000 records is ok. It mattered more to have the time performing.

    How would you do this nightly?

    Anyway I need to thank you already for the reply you have give me, it's already of great help.

  21. Twan New Member

    OLTP = online transaction processing, the kind of reporting you are doing here is called DSS (decision support service) normally the two cause conflicts in terms of resource usage and sensitivity to delays<br /><br />is someone going to go through 38000 records of data? That would be around three thousand product codes each with 12 months worth of data. assuming 50 lines per A4 sheet that's still 60 pages worth of figures? What would they be looking for? Can you use SQL to give them the answer, rather than given them the summarised data. It's a bit like there is a needle in a field of grass and you want to find it. In the first step you have summarised all of the grass into a bundle of straw, but it is still hard to find the needle... perhaps we can create a magnet to hover over the field which would automatically pick up the needle... bad analogy perhaps, but sounds good inside my head <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />to run it nightly you'd need to create a stored procedure which creates the summary tables and populates them with all of the data summarised by week, month, year. Then schedule this procedure to run every night using Enterprise Manager.<br /><br />Cheers<br />Twan<br />
  22. ralph1972 New Member

    Twan,

    I discovered that jan/01/2005 is week 1 and Jan/02/2005 is week 2

    Normally if an user want a sales report summarized by week from
    01/01/2005 31/01/2005. the user would expect that week 1 = 01/01 -> 07/01
    Week 2 = 08/01 -> 14/07,...

    When using the MSSQL
    Week 1 = 01/01/2005
    Week 2 = 02/01/2005 -> 08/01/2005

    This is since MS starts week 1 = Jan/01 and the next week starts on the next sunday.

    Otherwise your solution is great.



    Thanks

    Ralph
  23. Twan New Member

    Hi Ralph

    can you use day of year and then div it by 7?

    Cheers
    Twan
  24. ralph1972 New Member

    Twan,

    Can you give an example?

    Ralph
  25. Twan New Member

    Hi Ralph

    select ( ( datepart( dy, getdate() ) - 1 ) / 7 ) + 1 as MyWeek

    Cheers
    Twan
  26. Twan New Member

    It does mean that 31st Dec is week 53, and in a leap year 30th Dec is also week 53

    Cheers
    Twan
  27. ralph1972 New Member

    Twan,

    How do you build this in the query you have given me a couple posts ago?

    Ralph
  28. Twan New Member

    SELECT datepart( year, headerdatecreate ) as year,
    ((datepart( dy, headerdatecreate )-1)/7)+1 as week,
    td.DetailItem ,td.DetailItemCol,
    td.DetailItemWs,
    sum(td.DetailAmountNet) as NET
    FROM TransactionHeaders th INNER JOIN TransactionDetails td
    ON th.WsRoot = td.DetailHeaderWs
    AND th.Colroot = td.DetailHeaderCol
    AND th.HeaderNumber = td.DetailHeader
    where headerdatecreate > '01/01/2006'
    group by datepart( year, headerdatecreate ),
    datepart( month, headerdatecreate ),
    td.DetailItem,
    td.DetailItemCol,
    td.DetailItemWs
    order by datepart( year, headerdatecreate ),
    ((datepart( dy, headerdatecreate )-1)/7)+1,
    td.DetailItem,
    td.DetailItemCol,
    td.DetailItemWs
  29. ralph1972 New Member

    Twan,

    SELECT datepart( year, headerdatecreate ) as year,
    ((datepart( dy, headerdatecreate )-1)/7)+1 as week,
    td.DetailItem ,td.DetailItemCol,
    td.DetailItemWs,
    sum(td.DetailAmountNet) as NET
    FROM TransactionHeaders th INNER JOIN TransactionDetails td
    ON th.WsRoot = td.DetailHeaderWs
    AND th.Colroot = td.DetailHeaderCol
    AND th.HeaderNumber = td.DetailHeader
    where (headerdatecreate > '01/01/2005') and (headerdatecreate < '01/10/2005' )
    group by datepart( year, headerdatecreate ),
    (((datepart( dy, headerdatecreate )-1)/7)+ 1) as weekd,
    td.DetailItem,
    td.DetailItemCol,
    td.DetailItemWs
    order by datepart( year, headerdatecreate ),
    ((datepart( dy, headerdatecreate )-1)/7)+1,
    td.DetailItem,
    td.DetailItemCol,
    td.DetailItemWs

    I tried this but got an error when trying to execute the SQL


    (((datepart( dy, headerdatecreate )-1)/7)+ 1) as week I think this is the error line.




  30. Twan New Member

    Sorry ralph, I should have done the cut and paste in all three places

    SELECT datepart( year, headerdatecreate ) as year,
    ((datepart( dy, headerdatecreate )-1)/7)+1 as week,
    td.DetailItem ,td.DetailItemCol,
    td.DetailItemWs,
    sum(td.DetailAmountNet) as NET
    FROM TransactionHeaders th INNER JOIN TransactionDetails td
    ON th.WsRoot = td.DetailHeaderWs
    AND th.Colroot = td.DetailHeaderCol
    AND th.HeaderNumber = td.DetailHeader
    where (headerdatecreate > '01/01/2005') and (headerdatecreate < '01/10/2005' )
    group by datepart( year, headerdatecreate ),
    ((datepart( dy, headerdatecreate )-1)/7)+ 1,
    td.DetailItem,
    td.DetailItemCol,
    td.DetailItemWs
    order by datepart( year, headerdatecreate ),
    ((datepart( dy, headerdatecreate )-1)/7)+1,
    td.DetailItem,
    td.DetailItemCol,
    td.DetailItemWs

    Cheers
    Twan
  31. ralph1972 New Member

    Twan,

    I posted another question in the same forum, to display same fields (sale price) in the same row.

Share This Page