CODEWITHSUNDEEP
c#.netentity-frameworkvisual-studio-2013profiling
RAMMFAR
RAMMFAR

Reputation: 133

EF6 SQLQuery very slow but database is very fast

I have a performance problem we have done a bunch of analysis and are stuck. Hopefully one of you have seen this before.

I'm calling DbContext.Database.SqlQuery the database portion takes 3ms but the full execution takes 9 seconds.

We've used EF Profiler to discover this and we also run the SQL directly in SQL Server Management Studio and it is instantaneous.

We also used glimpse and couldn't see deep enough into the process.

The result type is not an entity from the model and therefore we are confident that tracking is not involved.

We also know that this is not the first query executed against the context therefore we are not paying EF startup cost on this query.

We tried the .net profiler and had so many problems running it that we decided we should just ask.

Any tips on how to dig in and figure this out ?

EDIT: The result set for this query is 1 row with 4 columns (decimal)

The line of code is just:

var list=contextInstance.Database.SqlQuery<nonEntityType>(sqstring).ToList();

The SQL itself is not a very long string. We will use a more detailed profiler to find out where in the process this is getting hung up.

Upvotes: 12

Views: 6850

Answers (2)

Mikael Eliasson
Mikael Eliasson

Reputation: 5227

If the client for some reason can't use a profiler as Frans suggest you will have to play the guessing game and exclude possiblities.

First of all I think a critical piece of information is missing. Does it always take around 9 seconds or does it vary?

First step:

Decide if the delay is before or after the query hits the database. Should be possible to do either with EF profiler and looking at the timestamps in Sql profiler.

Either way you will have limited the possibilities a bit.

Second step:

Exclude as much as possible

  • Indexes (No, the query is fast)
  • Returning too much data (No, according to the info you have)
  • Slow query compilation (No, raw sql query is used)
  • Slow data transfer (No, the other queries works well)
  • Slow DbContext initialization (No, you said it's not the first query)
  • Row or table locks (Not likely, That would probably show up as a long running query in the profiler)
  • Slow materialization (No, to few fields unless there is a serious edge case bug)

Third step:

What's left? That depends on the answer to #1 and also if it's always 9 seconds.

My prime suspects here is either some connection issue because another call is blocking so it has to wait for a connection or some second level cache or something that doesn't work well with this query.

To exclude some more alternatives I would try to run the same query using plain old ADO.NET. If the problem persists you know it's not a EF problem and very likely a connection issue. If it goes away it could still be both issues though.

Not so much as an answer as some rants, but hopefully something you didn't think of already.

Upvotes: 1

Frans Bouma
Frans Bouma

Reputation: 8357

We've used EF profiler to discover this and we also run the SQL directly in SQL server management studio and it is instantaneous.

This doesn't prove anything. The query might run fast, but the data might result in 100MB of data which is then transported to the client and materialized in objects. This might take more time than you think.

The query in SSMS might return instantaneous because it shows only part of the data. You didn't say what the data was.

Use a real .NET profiler, like dotTrace or Ants. This way you can see where time is lost exactly on the line. EF Prof (or my own ORM Profiler: http://www.ormprofiler.com) will tell you which part of the total route taken (ORM->DB->ORM) takes what time. Even EF prof does ;)

Upvotes: 1

Related Questions