Re: SQL-query, optimizing

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 11/08/04

  • Next message: pranab_das26: "Re: format a numeric values with commas" 
    Date: Mon, 08 Nov 2004 14:45:11 +0100

    On Mon, 8 Nov 2004 12:46:45 +0100, Ingar Eide wrote:

    >Here is a simple example of a query (from Northwind):
    >I would like to list out all orderlines that are included in orders that
    >have a totalamount > 15000.
    >The only way I can see to do this is using a Select-statement in the Where
    >Clause.
    (snip)

    Hi Ingar,

    You could replace the correlated subquery with a derived table. In this
    particular case, the I/O statistics show a great improvement in
    performance. You'll have to test if it helps in your situation as well.

    Same Northwind example with derived table:

    SELECT C.CompanyName, O.OrderID, P.ProductName, D.UnitPrice * D.Quantity *
    (1-D.Discount) As Price
    FROM Orders O
    JOIN [Order Details] D ON D.OrderID = O.OrderID
    JOIN Products P ON P.ProductID = D.ProductID
    JOIN Customers C ON C.CustomerID = O.CustomerID
    JOIN (SELECT OrderID
          FROM [Order Details] A
          GROUP BY OrderID
          HAVING SUM(A.UnitPrice * A.Quantity * (1-A.Discount)) > 15000) X
      ON X.OrderID = O.OrderID
    ORDER BY C.CompanyName

    Best, Hugo 

    -- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
  • Next message: pranab_das26: "Re: format a numeric values with commas"