5
\$\begingroup\$

I have written a module in my application that makes extensive use of dynamic LINQ to produce Linq/SQL queries based on user interface selection. Currently, the Linq-SQL translation is resulting in very unoptimised query results, so I am looking at ways to get LINQ to improve the SQL that it is producing.

The following is a typical scenario:

Firstly, the user interface controls are selected, resulting in a LINQ statement of:

Company_contacts.Any(Cust_order_header.Any(Order_time >= @0))

The table relations are as following:

  • Companies 1-*
  • Contacts 1-*
  • Cust_order_header

The Cust_order_header contains the datetime field Order_time.

Don't worry about @0 - that is a dynamic Linq parameter that is passed in containing the datetime object.

The query translates to:

Give me all companies that have placed orders within the last (insert days based on datetime) days

This results in a query that takes 5 minutes to complete, and kicks the hell out of the server.

The SQL it has created (which I have retrieved using ((ObjectQuery)groupQuery).ToTraceString()) is:

SELECT *
FROM [dbo].[Companies] AS [Extent1]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM ( SELECT 
        [Extent2].[Company_cont_key] AS [Company_cont_key]
        FROM [dbo].[Company_contacts] AS [Extent2]
        WHERE [Extent1].[Company_reference] = [Extent2].[Company_reference]
    )  AS [Project1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Cust_order_header] AS [Extent3]
        WHERE ([Project1].[Company_cont_key] = [Extent3].[Company_cont_key]) AND ([Extent3].[Order_time] >= convert(datetime, '2012-05-20 00:00:00.000', 121))
    )
)

Now, firstly - there are no joins in there, which leads me to think that the SQL it is producing is significantly unoptimised.

AS, if I do to the following in SQL Management Studio manually:

SELECT *
FROM [dbo].[Companies] co
join  [dbo].[Company_contacts] cc on co.Company_reference = cc.Company_reference
join  [Cust_order_header] coh on coh.Company_cont_key = cc.Company_cont_key
WHERE  [Order_time] >= convert(datetime, '2012-05-20 00:00:00.000', 121)

Then it's < 1s.

So, I have spent 3 weeks putting together my Dynamic-LINQ based user controls/solution; getting rid of it isn't an option.

I should also mention I am using an SQL2000 database. There is nothing I can do about this by the way.

Whilst playing around with the SQL to see if I can get improvements, I have noticed that bizarrely if I change the operator from '>' as it should be to '<', which takes the set of rows I was to ignore, it returns 18k rows (instead of the intended 300) but only takes 2 seconds instead of 4 minutes for the smaller query.

I'm wondering what the options are, or specifically how I should go about configuring LINQ to do things a bit better. I'm even starting to wonder if it's my dynamic LINQ extension code that is unoptimised (downloaded from here).

\$\endgroup\$

migrated from stackoverflow.com Jul 25 '12 at 21:30

This question came from our site for professional and enthusiast programmers.

  • \$\begingroup\$ How do you actually generate the LINQ query? \$\endgroup\$ – Jeff Mercado Jul 25 '12 at 17:07
  • \$\begingroup\$ Can you clarify what ORM you are using? More than one uses LINQ. Is this entity framework? \$\endgroup\$ – Jim Counts Jul 25 '12 at 17:18
  • \$\begingroup\$ The LINQ query is generated using the dynamic linq library in a generic context. Therefore the line is : DataContext.CreateObjectSet<T>().AsQueryable().Where<T>(queryString, paramArray); The ORM is Entity Framework. Beta 5.0 \$\endgroup\$ – Patrick McCurley Jul 25 '12 at 17:57
  • \$\begingroup\$ What happens if you construct the linq query manually (without using DLINQ), what SQL is generated? Does that query perform? If so, then it's the translation of the string value to the expression. Not sure how to improve this though :-( \$\endgroup\$ – Maarten Jul 25 '12 at 18:33
  • \$\begingroup\$ Thanks for the suggestion Maarten, I tried that and it produced the same statement. In a bizarre progression though if I change the datetime operator to '<=' instead of the intended '>=' it takes 2s, and returns 11k rows.. the original >= is 300 rows over 6 minutes. The db is SQL2000. ideas? (updated post too) \$\endgroup\$ – Patrick McCurley Jul 25 '12 at 19:54
3
\$\begingroup\$

Company_contacts.Any(x => x.Company_contacts.Cust_order_header.Any(y => y.Order_time >= @0))

That should put the joins in for you. Right now it is not referencing the objects you use in the query through their relationships so it is writing the SQL out as though they were subqueries.

\$\endgroup\$
  • \$\begingroup\$ Are you sure that holds true for dynamic linq? The statement above is the raw query string that is passed to the dynamic linq extension method and therefore doesn't need to have embedded variable declarations. I have tried the query that you have written too (outside of dLINQ) and printed the SQL through '.ToTraceString()' - it has the same generated SQL which produces adverse results. \$\endgroup\$ – Patrick McCurley Jul 25 '12 at 21:03
  • \$\begingroup\$ +1 as your comments about subqueries triggered me to add the joins explicitly. \$\endgroup\$ – Patrick McCurley Jul 25 '12 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.