Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2008
    Posts
    1

    Unanswered: Very very slow query

    Hi guys/gals

    I have only just signed up, as I have a problem thats confusing me a lot

    I have a page (classic asp - sadly) that displays a list of invoices for a certain customer

    Each of these customers has fairly complex permissions system, but the page generates a list of invoices and values in 0.01 seconds

    However in the while...do loop, for each invoice it checks a database table for "extra charges"..

    This is a simple SQL query

    "Select Sum(amount) from extra_charges where invoice_number=" & current_invoice_number
    However each time it runs this simple select statement it takes 2 seconds - which causes the page to load in 70 seconds instead of 0.01

    I am absolutely beyond confused. I have tried it in a stored procedure, and a direct ado query..

    The table by the way is empty on my current test system, and one one other system has about 150 records in it. Both take the same time

    Any help would be appreciated?

    This is a MSSQL 2000 database I've inherited, I am usually a PHP/MYSQL developer - so im a little out of my field of expertise here

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is there an index on invoice_Number on the extra_charges table?

    It could also be that the statistics need to be updated. Try running:
    Code:
    update statistics extra_charges with fullscan

  3. #3
    Join Date
    Feb 2007
    Posts
    62
    Once you sort it out you should also look at
    a) Avoiding dynamic SQL
    b) Avoiding doing queries in a loop (think more set based)

    Both of these will increase your throughput

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Agreed with the above, but I think it may also be beneficial to see the other SQL statement too - perhaps the results could be combined into a single resultset, thus avoiding any nested loops.
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    If it is indexed and you've selected to update statistics but it still takes long, one has to wonder if there's some connection overhead surrounding each call. Is it a local database (local LAN)?

    Either way; you should follow the prior 2 suggestions to return the entire record-set in a single call (all invoices) and stop sending SQL statements from ASP since they are potentially re-compiled each time.

    Since you're unfamiliar with TSQL, maybe it'll help to have a sample (untested) that returns a single recordset with a row for each invoice. You should place this into a Stored Procedure or at the very least create a View.
    Code:
    SELECT  tbInvoice.InvoiceNo,
    	tbInvoice.InvoiceDate, 
    	sum(tbInvoice.Balance) Balance, 
    	sum(tbExtra_charges.OtherCharges) OtherCharges
    FROM    tbInvoice LEFT OUTER JOIN
            tbExtra_charges  ON tbInvoice.InvoiceNo = tbExtra_charges .InvoiceNo
    WHERE   (tbInvoice.CustomerID = 123)
    GROUP BY tbInvoice.InvoiceDate, tbInvoice.InvoiceNo
    ORDER BY tbInvoice.InvoiceDate, tbInvoice.InvoiceNo
    Last edited by vich; 03-28-08 at 00:22.

  6. #6
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Vich,

    The sum balance would cause the balance of the invoice to be totalled per extra charge while there is a single balance for the invoice?

    Other then that, Vich's suggestion is the way to go. you get the results in one go.

    The only problem with that solution is if you join to other tables aswell and you get matrix combinations. Say that you also join to the invoice detail table to get the sum or count of details. If you have 4 details and 2 charges, all details will be counted twice and all charges 4 times.

    You can solve that with subqueries:
    Code:
    select
      tbInvoice.InvoiceNo,
      tbInvoice.InvoiceDate,
      tbInvoice.Balance,
      (select count(*) from tbInvoice_detail where tbInvoice_detail.InvoiceNo=tbInvoice.InvoiceNo) NumDetails,
      isnull((select sum(tbExtra_charges.OtherCharges) from tbExtra_charges where tbExtra_charges.InvoiceNo=tbInvoice.InvoiceNo), 0) OtherCharges
    from tbInvoice
    where tbInvoice.CustomerID = 123
    order by tbInvoiceInvoiceDate, tbInvoice.InvoiceNo
    Joins are generally better then subqueries though, so if Vichs solution works for you, go with it.
    Greetz,

    Bastiaan Olij

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Curious, SP4 ?

    If so, do an sp_who2 and see if your connection is "blocking" itself (io wait), I've seen queries slow down 30 fold after the installation of SP4.

  8. #8
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by BasOlij
    Vich,

    The sum balance would cause the balance of the invoice to be totalled per extra charge while there is a single balance for the invoice?
    silly mistakes ...

    As a rule; never allow more than one 1-many detail tables to hang off an Invoice header and only put the "sum()" on the totals from that 1-many table that can be simultaneously linked to. If your DB cannot be absolutely depended on to follow that rule then yeah, subqueries - ergo the importance of such enforcement.

  9. #9
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Vich,

    hehe I don't have the luxery of only 1 1:n detail table..
    Greetz,

    Bastiaan Olij

  10. #10
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by BasOlij
    Vich,

    hehe I don't have the luxery of only 1 1:n detail table..
    I think my statement over simplified it - it's more of a "rule" that's applicable within a single query, and depends upon the grouping and aggregate requirements. Sorry for not being more explicit and just blurting out such a rash statement (and untested query). More like; you need to be able to depend on tables that Should only be 1-0 or 1-1 being so.

    Makes me wonder if SQL has a feature or two to address this?

    1. Some sort of constraint that enforces a 1 to 1 (or zero) relationship.
    2. A version of sum() that will take the place of those inline subqueries for cases where there are more than one 1-many sub-tables.


    For 2; obviously subqueries that do grouping are simple to write.

    Example;

    Table1: Order Header, Inv #123
    Table2: Order Lines, 10 lines
    Table3: Shipment tracking, 3 shipments
    Table4: Shipment tracking lines (partial shipments of single line)
    Table5: Other Ship-To Address (1-0 or 1-1 relationship to Order Header)

    So; the query writer needs to be able to depend on Table5 not being 1-many. However; a query that does subtotals that includes Table3 or Table4 would need to do something to prevent aggregates from repeating (grouped sub queries or just retrieving an aggregates from the shipment tables).

    For the OP's example situation it seems like the single sub-table linkage that a single level query using SUM() should be good.
    Last edited by vich; 03-28-08 at 15:23.

  11. #11
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Vich,

    True, that would be a nice protection to warn you for improperly formatted queries.

    That does remind me, there is a way for MSSQL to join with multiple detail tables other then using subqueries in the select and still aggregate correctly, though I have no idea if the performance will be ok. I do believe it has some tricks up its sleeve because so far it does handle it fairly well.

    Code:
    select
      tbInvoice.InvoiceNo,
      tbInvoice.InvoiceDate,
      tbInvoice.InvoiceAmount,
      isnull(aliasDetails.DetailCount,0),
      isnull(aliasDetails.DetailTotAmount,0.00),
      isnull(aliasOther.OtherCount,0),
      isnull(aliasOther.OtherTotOtherCharges,0.00)
    from
      tbInvoice,
      (select
        InvoiceNo,
        count(*) DetailCount,
        sum(DetailAmount) DetailTotAmount
      from tbInvoice_detail 
      group by InvoiceNo) aliasDetails,
      (select
        InvoiceNo,
        count(*) OtherCount,
        sum(OtherCharges) OtherTotOtherCharges
      from tbExtra_charges
      group by InvoiceNo) aliasOther
    from tbInvoice
    where aliasDetails.InvoiceNo=*tbInvoice.InvoiceNo
    and aliasOther.InvoiceNo=*tbInvoice.InvoiceNo
    Should also work with normal left outer join syntax...
    Greetz,

    Bastiaan Olij

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That should work just fine and is about as efficient as it could be. Not a fan of the old school joins though....
    In case you didn't know (since you use other terms but not this one) they are called derived tables. They are standard SQL, not just propietry to SQL Server.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Pootle_flump,

    yeah I'm still getting used to the proper join notation, using it more and more. I've been doing Sybase for the past 10 years and for the longest time, old school joins was all we had so thats what I'm used to
    Only been doing MSSQL for just over a year now so still learning its tricks while unlearning the limitations of Sybase 12 and before

    Same reason why doing derived tables is relatively new to me, as far as I am aware Sybase doesn't support it, at least not in the versions I've used (I haven't done much with 15). I love them though. Can't imagine life without them anymore
    Greetz,

    Bastiaan Olij

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BasOlij
    I love them though. Can't imagine life without them anymore
    Just you wait until you start using CTEs and analytic functions (OVER clause) then
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •