sql server mseq: Hi,

I have some SQL-statements that are very slow, and I'm wondering if I can do
this in a more efficient way.
The problem is that I need to list out _detail information_, but the where
condition check _sum of the group_.

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.

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
WHERE EXISTS
(SELECT 1 FROM [Order Details] A
WHERE A.OrderID = O.OrderID
HAVING SUM(A.UnitPrice * A.Quantity * (1-A.Discount)) > 15000)
ORDER BY C.CompanyName


Ingar Eide

[quoted text, click to view]
(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
--