Reputation: 1074
I have following query which takes almost 1 minute to execute.
public static Func<Entities, string, IQueryable<string>> compiledInvoiceQuery =
CompiledQuery.Compile((Entities ctx, string orderNumb) =>
(from order in ctx.SOP10100
where order.ORIGNUMB == orderNumb
select order.SOPNUMBE).Union(
from order in ctx.SOP30200
where order.ORIGNUMB == orderNumb
select order.SOPNUMBE)
);
It filters on basis of ORIGNUMB which is not my primary key, i can not even put any index on it. Do we have any other way to make it faster? I tested on sql server and found that only query
from order in ctx.SOP10100
where order.ORIGNUMB == orderNumb
select order.SOPNUMBE
or
select SOPNUMBE
from SOP10100
where ORIGNUMB = @orderNumb
is taking more than 55 seconds. Please suggest.
Upvotes: 0
Views: 270
Reputation: 2597
One of the big problems with LINQ to SQL is that you have very little control over the SQL being generating.
Since you are running a union and not a join, it should be a pretty simple SQL. Something like this:
SELECT *
FROM SOP10100
WHERE ORIGNUMB = 'some number'
UNION
SELECT *
FROM SOP30200
WHERE ORIGNUMB = 'some number'
You can use SQL Server Profiler to see the SQL statements that are being run against the database to see if the SQL is like this or something more complicated. You can then run the SQL generated in SQL Server Management Stuido and turn on Include Client Statistics and Include Actual Execution Plan to see what exactly is causing the performance issue.
Upvotes: 0
Reputation: 20330
If it's taking 55 seconds on the server, then it's nowto to do with linq. Why can't you have an index on it, because you need one....
Only other option is to rejig your logic to filter out records (using indexed columns), before you start searching for an ordernumber match.
Upvotes: 1