Performance on Northwinds Database for Nested One2Many

[Siegfried Heintze]

Here is a LINQ that works but takes over three minutes to execute:

var customerOrders = from customer in dc.Customers
join order in dc.Orders on
customer.CustomerID equals order.CustomerID
join detail in dc.Order_Details on
order.OrderID equals detail.OrderID
select new
{
custID = customer.CustomerID,
companyName = customer.CompanyName,
orders = from subOrders in
customer.Orders
select new
{
OrderId =
subOrders.OrderID,
details = from
subDetails in subOrders.Order_Details
select new
{
product
= subDetails.Product.ProductName,
quantity
= subDetails.Quantity
}
}
};
This does almost the same thing over 100 times faster:

SqlCommand cmd = new SqlCommand(
"SELECT Customers.CustomerID, Customers.ContactName,
Orders.OrderID, Products.ProductName, [Order Details].Quantity " +
"FROM Customers INNER JOIN" +
" Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN" +
" [Order Details] ON [Order Details].OrderID =
Orders.OrderID INNER JOIN" +
" Products ON Products.ProductID = [Order
Details].ProductID ORDER BY Customers.CustomerID, Orders.OrderID");
try{
cmd.Connection = new SqlConnection("...");
cmd.Connection.Open();
using (rdr = cmd.ExecuteReader())
{


So the LINQ is definetly more elegant but WHAT A PRICE! 100 times slower! So
are folks using LINQ because they don't care about such a big penalty or is
there some other advantange I'm missing?

When I look at the debugger log when I execute my LINQ benchmark, I see lots
of SQL statements scrolling by non-stop for three minutes! Is SQL Server
parsing all thos statements as I see them scroll by? Wow! What a waste!

Now the LINQ code is nice in the sense that it does not create a table with
lots of almost redundant rows like the ADO does. Instead the LINQ code makes
a nice memory ready data structure. Is there a way I can have the best of
both worlds. Could a stored procedure help me out here? I think the answer
is no. I think stored procedures can only return rectangular tables.

Thanks,
Siegfried

 

[vanderghast]

The LINQ statement you refer to seems way too complex. Can you try:

var result = from customer in dc.Customers
from order in dc.Orders
from detail in dc.Order_Details
from product in dc.Products
where customer.customerID == order.customerID
&& order.OrderID == detail.OrderID
&& product.ProductID = detail.ProductID
select new {
custID = customer.CustomerID,
companyName = customer.CompanyName,
OrderId = order.OrderID,
product = product.ProductName,
quantity = detail.Quantity };




which should produce the same query execution plan than your SQL statement,
in ONE shot. The LINQ statement you show, with nested "new {}"s, make many
calls, a little bit like many subquery on the fly would do, rather than
doing it in one shot with join, and that is (probably) why it is so slow.

NOTE that I reach the 'table' names directly, not trough some 'navigation' ,
ie. product.productName, NOT subDetails.product.productName; which may
also be relevant against having only one or multiple SQL statements produced
at execution (lazy loading, option.LoadWith<>, but simply easier to access
the 'table' directly, imho.)


Vanderghast, Access MVP

Here is a LINQ that works but takes over three minutes to execute:

var customerOrders = from customer in dc.Customers
join order in dc.Orders on
customer.CustomerID equals order.CustomerID
join detail in dc.Order_Details on
order.OrderID equals detail.OrderID
select new
{
custID = customer.CustomerID,
companyName =
customer.CompanyName,
orders = from subOrders in
customer.Orders
select new
{
OrderId =
subOrders.OrderID,
details = from
subDetails in subOrders.Order_Details
select new
{
product
= subDetails.Product.ProductName,

quantity = subDetails.Quantity
}
}
};
This does almost the same thing over 100 times faster:

SqlCommand cmd = new SqlCommand(
"SELECT Customers.CustomerID, Customers.ContactName,
Orders.OrderID, Products.ProductName, [Order Details].Quantity " +
"FROM Customers INNER JOIN" +
" Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN" +
" [Order Details] ON [Order Details].OrderID =
Orders.OrderID INNER JOIN" +
" Products ON Products.ProductID = [Order
Details].ProductID ORDER BY Customers.CustomerID, Orders.OrderID");
try{
cmd.Connection = new SqlConnection("...");
cmd.Connection.Open();
using (rdr = cmd.ExecuteReader())
{


So the LINQ is definetly more elegant but WHAT A PRICE! 100 times slower!
So are folks using LINQ because they don't care about such a big penalty
or is there some other advantange I'm missing?

When I look at the debugger log when I execute my LINQ benchmark, I see
lots of SQL statements scrolling by non-stop for three minutes! Is SQL
Server parsing all thos statements as I see them scroll by? Wow! What a
waste!

Now the LINQ code is nice in the sense that it does not create a table
with lots of almost redundant rows like the ADO does. Instead the LINQ
code makes a nice memory ready data structure. Is there a way I can have
the best of both worlds. Could a stored procedure help me out here? I
think the answer is no. I think stored procedures can only return
rectangular tables.

Thanks,
Siegfried