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:
Cust_order_header contains the datetime field
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
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).