1

My Entity Framework is set up to emit the SQL generated, followed by the time taken to run the query, to the Output pane.

When I run it locally, the EF query takes .064s (as can be seen in the Output pane), and the SQL (when run by itself in Management Studio) takes about the same. In production the EF query takes .660s, yet the SQL generated only takes .157s.

There are about 50 rows returned. All the other EF queries are running at the expected speed.

What can cause the EF to take so much longer to run than the SQL it generates?

Thank you for any ideas.

wezten
  • 2,126
  • 3
  • 25
  • 48
  • 2
    So many possibilities.... Indexes, statistics, data size, concurrency, sun spots? :) – DavidG Sep 15 '14 at 09:30
  • 1
    @DavidG Not at all - if it was any of these, then the SQL generated by EF would also be slow when run by itself. – wezten Sep 15 '14 at 09:32
  • @DavidG Can you give me an example please, of something that would make the SQL generated by EF faster when run in Management Studio, than when EF runs it? – wezten Sep 15 '14 at 09:51
  • 2
    You need to get the execution plans for both queries and compare them. Use SQL Profiler for the EF version. – DavidG Sep 15 '14 at 09:57
  • But as an example, your app probably connects to SQL Server with `ARITHABORT` turned off whereas SSMS it is on. This may cause it to use a different execution plan. – DavidG Sep 15 '14 at 09:58

1 Answers1

1

The simplest way to figure out why queries run differently when called from different locations is to check the execution plans for both of them. From SSMS it's simply a case of including the plan in the output. For a live SQL Server you can use SQL Profiler.

When you have both plans, compare them and figure out the differences.

One example when a query runs differently is the ARITHABORT setting. Your app probably connects to SQL Server with this turned off whereas in SSMS it is on. This may cause it to use a different execution plan.

DavidG
  • 113,891
  • 12
  • 217
  • 223
  • I did this, and I saw that EF wrapped the whole query in an exec. When running this, it took .7s - taking it out of the exec reduced it to .1s. Thank you. – wezten Sep 15 '14 at 10:22
  • The reason was because of [parameter sniffing](http://stackoverflow.com/a/1095858/428724). – wezten Sep 16 '14 at 08:53