Producing queries based on user interface selection

Question

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).

Answer 1

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.