EF6 SQLQuery very slow but database is very fast

Question

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.

Answer 1

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 ;)