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

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



"Siegfried Heintze" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>