How do I make a Linq to SQL query faster that includes multiple predicate criteria?

Question

This query is 3 times slower in execution when I execute it from the code than when I execute the equivalent SQL from SQL Server Management Studio

code

var myDataContext = new AccessMyDatabaseDataContext();
            myDataContext.ObjectTrackingEnabled = false;
var schedule = (from s in myDataContext.MyTable
                            where s.Date >= StartDate
                            && s.Date <= EndDate
                            && s.Name == "MySchedule"
                            && (s.Status.Equals("LIVE") || s.SomeOtherField.Equals("MyString"))
                            select s)
                            .ToList();

I have a variable number of expected records, so I can't assign a direct number of expected records. I think the reason for the extra-time is that Linq2sql executes select top 1 statements as long as it finds records.

Does somebody know a way to solve this issue?

EDIT

At the End, it was more me than Linq2Sql. SORRY!

I took 3 Tables with (almost) the same syntax (above), to assign my business-object with needed values in a foreach-loop. The problem was, that one of those access-queries was without the ToList-Command. The foreach loop took to values from my List-Objects an one from my Database.

An awkward beginner-mistake. Sorry again and thanks for your help!!!

I often use stored procedures for complex SQL. The performance gains are worthwhile. The problem is that `myDataContext` becomes out-of-date. Wrap you code in `using var myDataContext = new AccessMyDatabaseDataContext(){}` — Peter Bill, Apr 29 '16 at 21:03
Show us the equivalent SQL generated that you're running in SQL Server Management Studio, as well as the indexes you have on that table and that table's structure. — George Stocker, Apr 29 '16 at 21:13
Are you sure the Select top1 is coming from this query and not from a foreach() over the contents of the list where you are referencing some related entity-information. From the contents of this query I would be very surprised to see N select top 1 statements. — Pleun, Apr 30 '16 at 09:00
@PeterBill: I think i have to go this way too. I tried it with the "using-wrap" but the execution time is almost the same... — Joshit, Apr 30 '16 at 12:03
@GeorgeStocker: `code` Select * FROM [MyDatabase].[dbo].[MyTable] WHERE Date >= '20160422' and Date <= '20160429' and ScheduleName = 'MySchedule' and (Status = 'LIVE' or SomeOtherField = 'MyString') ` "Database-Insides" are currently "not my business". I think my colleagues are not happy if I post their work somewhere. I must find ways in code... — Joshit, Apr 30 '16 at 12:35
@Pleun: After reading your comment, I proofed it again and I found out, that I had a different way of doing my access in another method. In fact I forgot the "ToList"- Method and in fact it ran with the statement into the foreach. Weekend-Mode - shame on me -.- I made it a little bit more reuseable now. — Joshit, Apr 30 '16 at 13:11
@Joshit Put it in your question, not in a comment. No one can follow code in a comment. — George Stocker, Apr 30 '16 at 15:23

score 1 · Answer 1 · edited May 23 '17 at 12:09

The lifetime of the DbContext is described on MSDN and on SO. When I replaced my class variable DbContext, I noticed a great improvement (a test loop that repetitively got all data from 2 joined tables reduced from 7 minutes to 4.3 seconds).

I believe that any complex SQL is better in a stored procedure. The RDBMS has to optimise it only once. You only return the rows you want. There are many resources to help with optimising queries in SQL Server, such as https://msdn.microsoft.com/en-us/library/ff650689.aspx.

Often overlooked is the database reports feature of SQL Server Management Studio (introduced in 2005). Right-click on the database name in Object Explorer and choose Reports -> Standard Reports -> any report. Experiment with the reports. I found this site http://www.sqlshack.com/sql-server-management-studio-performance-reports/.

score -1 · Answer 2 · answered Apr 29 '16 at 21:05

Always check the SQL queries generated, either by logging out the SQL or by starting SQL profiler. My guess is that the SQL generated will not be bad. The reasons why you might loose time:

ERM frameworks are always a bit slower due to object materialization. If you don't need all fields, introduce .Select() clauses to select only the parts you actually need. Typically, serialization into non-entity types, e.g. into anonymous types, are faster.
The parsing of the expression trees can consume quite some time. There is a notion of compiled query in Linq2Sql, which parses the expression tree only the first time. Have you enabled this? There are a number of tutorials on how to do it.

Having said this, a factor of 3 is not atypical for ERM frameworks. Depending on what you need, you might consider if EntityFrmaework is faster. Alternatively there are a few ultra-lightweight ERM frameworks which are optimized for query performance but are not very nice to use for managing objects.

As a last alternative, EntityFramework (and probably also Linq2Sql) allow you to obtain the direct SQL strings. You might load a DataTable using this string.

Thanks for your Ideas! I am already done with most of the implementation with linq2sql. Its a prototype and i wanted to have time to learn EF. I didn´t have much time to deliver this solution and for now I only try to improve the performance of db-access. I have read about CompiledQueries and I understood, that for my UseCase this wont be a faster solution... — Joshit, Apr 30 '16 at 13:20

How do I make a Linq to SQL query faster that includes multiple predicate criteria?

code

2 Answers2