CODEWITHSUNDEEP
performancelinqentity-framework
dave
dave

Reputation: 1587

Linq query runs slow but query runs fast in isolation

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

Upvotes: 0

Views: 3803

Answers (1)

dave
dave

Reputation: 1587

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.

Upvotes: 2

Related Questions