Linq query runs slow but query runs fast in isolation

Question

We have a REST service to get a member given an ID. The SQL runs fast in isolation (5ms) but when run from Linq (using Entity framework 6), it runs very slow (230ms).

I don't this question is a duplicate of this, this or this as it feels Linq/EntityFramework related.

Here are the stats: The time taken for a client to call member get is about 360ms The time taken to execute the Linq query from the C# code is about 230ms The time taken to execute the SQL on the SQL Server is about 228ms

SQL tracing in production is of similar performance (141ms to execute the SQL on the SQL server) so the numbers feel real.

I tried running the Linq query six times in a row to see if perhaps the cost of establishing a connection from the datacontext was a problem. Each of those Linq queries took the same amount of time to run.

If I use the same datacontext to run the SQL directly (ie: what Linq generates), the runtime (measured from C#) drops from 230ms to 19ms.

Running the SQL directly on the server (SQL Server management Studio) takes about 5ms.

C# code (all in the same routine, using the same datacontext, no using block) produces these numbers:

Linq original query run =227ms
Raw SQL query: 19ms
Linq run 0=228
Linq run 1=227
Linq run 2=229
Linq run 3=229
Linq run 4=232

The Linq query looks like this:

DateTime start = DateTime.Now;
        var memberDetail = await (from member in DataContext.Members.AsNoTracking()
                                  join memberName in DataContext.MemberNames.AsNoTracking() on member.UID equals memberName.MemberID into nameOutput
                                  from mn in nameOutput.DefaultIfEmpty()
                                  join memberProperty in DataContext.Properties.AsNoTracking() on member.PropertyID equals memberProperty.UID
                                  join membershipCycle in DataContext.MembershipCycleHistories.AsNoTracking() on member.UID equals membershipCycle.MemberID into cycleOutput
                                  from co in cycleOutput.DefaultIfEmpty()
                                  where member.ReferenceNumber.Equals(memberNumber) &&
                                        memberProperty.ExternalID.Equals(property, StringComparison.InvariantCultureIgnoreCase)
                                  select new
                                  {
                                      member.UID,
                                      member.Created,
                                      member.LastUpdated,
                                      PropertyName = memberProperty.ExternalID,
                                      member.ReferenceNumber,
                                      member.Active,
                                      member.IsAwaitingSync,
                                      member.Class,
                                      mn.FirstName,
                                      mn.LastName,
                                      mn.PreferredName,
                                      MembershipCreditBalance = co.MembershipCredits,
                                      member.DOB
                                  }
                                 ).FirstOrDefaultAsync();
        System.Diagnostics.Trace.WriteLine(String.Format("linq run original={0}", (DateTime.Now - start).TotalMilliseconds));

And the connection string is:

Data Source=SQLServer123;Initial Catalog=DB123;Persist Security Info=True;User ID=User123;Password=PWD123;MultipleActiveResultSets=True

Do not use `DateTime.Now` to perform timings under 500ms, please re-run your tests using the `Stopwatch` class. Also, how did you get the raw SQL to run against the server and how exactly did you run it? Lastly if you use Sql Profiler how long does the linq query take to execute server side? — Scott Chamberlain, Apr 02 '15 at 00:20
@ScottChamberlain - I reran these with stopwatch. The results were within 5% of the numbers provided. The SQL was extracted from both the linq query (tostring()) and the SQL trace. Running the query directly in management studio has a runtime of 5ms. — dave, Apr 02 '15 at 01:08

score 2 · Accepted Answer · answered Apr 07 '15 at 04:55

2

After a fair bit more investigation, I found the problem. The database uses varchars for all of its strings. When Linq passes its parameters to SQL Server, it passes them as Unicode. SQL Server looks at the data type and figures it cannot use the varchar indexes, so falls back to linear scans.

By changing my database from varchar to nvarchar, my query speed went from 258ms to 3ms.

answered Apr 07 '15 at 04:55

dave

1,567
2
21
34

In this case was it only the parameters in the where clause that were causing this or was also returning rows which were themselves strings also causing the problem ? – Gilles Mar 17 '16 at 15:38
@Gilles - the problem is the type mismatch. As long as the parameters match the actual database column types, you should be okay. That being said, .Net passes all strings as unicode so the DB string columns should always be nvarchar. – dave Mar 17 '16 at 23:35
1

What if you can't control the type of the db columns? – Shavais Feb 17 '17 at 00:52

Linq query runs slow but query runs fast in isolation

1 Answers1