Performance tuning of nested SELECT query - creating Nested XML

Question

Hi,

I have a SQL query

============================

SELECT OrderID, CustomerID, EmployeeID,
(select OrderDetails.OrderID, ProductID, UnitPrice, Quantity
from OrderDetails
WHERE OrderDetails.OrderID = Orders.OrderID FOR XML PATH('OrderDetails'), TYPE)
FROM Orders FOR XML PATH('Orders'), TYPE;

============================

This gives the result as attached 234853-result.xml

But this nested SELECT query is performing very slow - agreed. But is there any other way so that performance can be improved without distorting the nested XML output.

Schema of the DB is as below:

Quick advice is highly appreciated.

Regards,
Partha

Answer 1

Hi @Partha Das ,

You need to check existence of indexes on the following columns:

• Orders.OrderID
• OrderDetails.OrderID

Well-formed XML shall have a root element. That's why I added it via ROOT(...) clause to the SELECT statement below.

SQL

SELECT OrderID,  
       CustomerID,  
       EmployeeID,  
       (  
           SELECT OrderDetails.OrderID,  
                  ProductID,  
                  UnitPrice,  
                  Quantity  
           FROM OrderDetails  
           WHERE OrderDetails.OrderID = Orders.OrderID  
           FOR XML PATH('OrderDetails'), TYPE  
       )  
FROM Orders  
FOR XML PATH('Orders'), TYPE, ROOT('root');  

Answer 2

Very slow? When I run it on my copy of Northwind, the result is instant. When I run it on my extended version known as Northgale with a million orders, it takes a least a minute, but that is not that surprising.

Answer 3

Hi @Partha Das
How about using Outer Apply instead of subquery, like this:

SELECT *,A.*  
FROM Orders OUTER APPLY (SELECT OrderDetails.OrderID, ProductID, UnitPrice, Quantity  
					     FROM OrderDetails  
						 WHERE OrderDetails.OrderID = Orders.OrderID   
	                     FOR XML PATH('OrderDetails'), TYPE ) AS A  
FOR XML PATH('Orders'), TYPE, ROOT('Root');  

Best regards,
LiHong