asked
Better way to write correlated subquery
I've tried to ways to write this query, one with CTE. I'm wondering if this is a better way to write this, especially with the correlated sub-queries. TRY 1: SELECT O.OrderID, O.UserID, O.OrderPONumber, O.OrderTotal, O.OrderDateTime, ( SELECT SUM(OrderItemQty * OrderItemPrice) from OrderItems WHERE OrderId = O.OrderId ) AS ProductSubtotal, ( SELECT OrderShippingTotal + OrderTaxValue + ( SELECT SUM(OrderItemQty * OrderItemPrice) from OrderItems WHERE OrderId = O.OrderId ) from Orders WHERE OrderID = O.OrderId ) AS TotalCharges, UC.UserContactFirstName, UC.UserContactLastName, UC.UserContactAddress1, UC.UserContactAddress2, UC.UserContactCity, ( SELECT StateAbbreviation from States WHERE StateID = UC.StateID ) AS State, CASE CountryID WHEN 1 THEN 'US' WHEN 2 THEN 'CA' ELSE 'US' END AS Country, UC.UserContactZip FROM Orders O JOIN UserContact UC ON UC.UserID = O.UserID WHERE UC.UserContactTypeID = 1 AND O.UserID = 927 TRY #2: ;WITH OrdersCTE(OrderID, UserID, OrderPONumber, OrderTotal, OrderDateTime, ProductSubtotal, TotalCharges) AS ( SELECT OrderID, UserID, OrderPONumber, OrderTotal, OrderDateTime, ( SELECT SUM(OrderItemQty * OrderItemPrice) from OrderItems WHERE OrderId = O.OrderID ) AS ProductSubtotal, ( SELECT OrderShippingTotal + OrderTaxValue + ( SELECT SUM(OrderItemQty * OrderItemPrice) from OrderItems WHERE OrderId = O.OrderID ) from Orders WHERE OrderID = O.OrderID ) AS TotalCharges FROM Orders O WHERE UserID = 927 ), ContactsCTE(UserID, UserContactFirstName, UserContactLastName, UserContactAddress1, UserContactAddress2, UserContactCity, State, Country, UserContactZip) AS ( SELECT UserID, UserContactFirstName, UserContactLastName, UserContactAddress1, UserContactAddress2, UserContactCity, ( SELECT StateAbbreviation from States WHERE StateID = U.StateID ) AS State, CASE CountryID WHEN 1 THEN 'US' WHEN 2 THEN 'CA' ELSE 'US' END AS Country, UserContactZip FROM UserContact U WHERE UserContactTypeID = 1 AND UserID = 927 ) SELECT OrderID, O.UserID, OrderPONumber, OrderTotal, OrderDateTime, ProductSubtotal, TotalCharges UserContactFirstName, UserContactLastName, UserContactAddress1, UserContactAddress2, UserContactCity, State, Country FROM OrdersCTE O JOIN ContactsCTE C ON O.UserID = C.UserID
Do you have to do the work all in one query? Also, there is probably a number of ways that you might be able to rework this. Can you post the execution plan for both queries? Shooting completely from the hip, could you do something like this: SELECT O.OrderID , O.UserID , SUM(oi.OrderItemQty * oi.OrderItemPrice) AS ProductSubtotal , SUM(oi.OrderShippingTotal + oi.OrderTaxValue + (oi.OrderItemQty * oi.OrderItemPrice) AS TotalCharges INTO #tempTable FROM Orders O INNER JOIN OrderItems oi ON o.orderID = oi.orderID GROUP BY o.orderID, o.UserID) SELECT O.OrderID , O.UserID , O.OrderPONumber , O.OrderTotal , O.OrderDateTime , t.ProductSubTotal , t.TotalCharges , UC.UserContactFirstName , UC.UserContactLastName , UC.UserContactAddress1 , UC.UserContactAddress2 , UC.UserContactCity , s.stateabbreviation AS [state] , CASE s.countryID WHEN 1 THEN 'US' WHEN 2 THEN 'CA' ELSE 'US' END AS Contry , uc.userContactZip FROM Orders O INNER JOIN #tempTable t ON t.orderID = o.orderID INNER JOIN UserContact UC ON uc.userid = o.UserID INNER JOIN States s ON s.stateID = uc.stateID ) Disclaimer: It was late (for me anyway) when I wrote this so I would double check my logic for sure. And feel free to tell me that I'm wrong. ;-)
There are a couple of things that you could do which **might** help. I stress "might" there because without building up a full schema and having a relevant set of data, I couldn't say for sure that performance would be better, but my first inclination is that it should be. New code: SELECT O.OrderID, O.UserID, O.OrderPONumber, O.OrderTotal, O.OrderDateTime, Items.ProductSubtotal, OrderShippingTotal + OrderTaxValue + Items.ProductSubtotal AS TotalCharges, UC.UserContactFirstName, UC.UserContactLastName, UC.UserContactAddress1, UC.UserContactAddress2, UC.UserContactCity, S.StateAbbreviation AS STATE, CASE CountryID WHEN 1 THEN 'US' WHEN 2 THEN 'CA' ELSE 'US' END AS Country, UC.UserContactZip FROM Orders O INNER JOIN UserContact UC ON UC.UserID = O.UserID LEFT OUTER JOIN States S on UC.StateID = S.StateID OUTER APPLY ( SELECT SUM(OrderItemQty * OrderItemPrice) as ProductSubtotal FROM OrderItems WHERE OrderId = O.OrderId ) Items WHERE UC.UserContactTypeID = 1 AND O.UserID = 927; This differs in a couple of ways from your original query. First, it removes an unnecessary subquery to get the state abbreviation. I left that as an OUTER JOIN in the event that there may be nullable data or lookup failures; if you can guarantee that there wouldn't, I would change that to an INNER JOIN. Second, given the complexity of your query, there's a pretty good chance that SQL Server's calculating ProductSubtotal (the sum of order item quantity * order item price) twice. Moving that to an APPLY statement should cut down on the cost by removing that second calculation. I'd recommend checking the execution plan to make sure, though. Also, if each Order is guaranteed to have OrderItems associated with it, you can change the OUTER APPLY to CROSS APPLY. The nice effect, though, is that even if performance is exactly the same, I think this version is a bit more readable because you don't have to process those subqueries mentally and figure out what's going on. All of your statements are in the FROM section and they have reasonable labels n the SELECT section.
10 People are following this question.
