-
Performance issues when ordering by subquery row count
Looking for some expert help on tuning a slow query for MS-SQL. Would very much appreciate some pointers!
"Customers" Table 1 has 3,000 records.
"Orders" Table 2 has 50,000 records.
I am using this query to return a recordset containing total completed and incomplete orders (plus a success rate as a percentage) for each customer.
SELECT Customers.FirstName, Customers.LastName, Branches.Name,
(SELECT COUNT(*) FROM Orders WHERE Orders.CustomerId=Customers.CustomerId) AS TotalOrders ,
(SELECT COUNT(*) FROM Orders WHERE Orders.CustomerId=Customers.CustomerId AND OrderStatusId=2) AS TotalCompletedOrders,
(SELECT COUNT(*) FROM Orders WHERE Orders.CustomerId=Customers.CustomerId AND OrderStatusId=3) AS TotalIncompleteOrders ,
(100 / nullif((SELECT COUNT(*) FROM Orders WHERE Orders.CustomerId=Customers.CustomerId AND (OrderStatusId=2 OR OrderStatusId=3)),0))*(SELECT COUNT(*) FROM Orders WHERE Orders.CustomerId=Customers.CustomerId AND OrderStatusId=2) AS OrderCompletionRate
FROM Customers LEFT JOIN Branches ON Customers.BranchId=Branches.BranchId WHERE Customers.BranchId IN (1,2) ORDER BY Customers.FirstName ASC OFFSET (0) ROWS FETCH NEXT 25 ROWS ONLY ;
The above query works fine, however if I order the results by"TotalOrders", "TotalCompletedOrders", or "OrderCompletionRate" instead of by "Customers.FirstName" then the performance of the query really suffers. I appreciate this is because SQL Server is having to compile results on all rows and then sort them before returning the first 25. Is there another way of doing this so that the performance is better?
Many thanks
-
After some more investigation, the part that is killing the performance is:
(100 / nullif((SELECT COUNT(*) FROM Orders WHERE Orders.CustomerId=Customers.CustomerId AND (OrderStatusId=2 OR OrderStatusId=3)),0))*(SELECT COUNT(*) FROM Orders WHERE Orders.CustomerId=Customers.CustomerId AND OrderStatusId=2) AS OrderCompletionRate
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|