0

I use EF6 in a winform project

I use a query to a view to populate a DataGridView.

With no condition the populate takes up to 3s (2500 rows).

With a specific condition, involving a ...where exists(select...) the populate takes up to 13s (212 rows) (same perf in linqpad).

I read :

that are quite similar to my cases.

In my both cases (with and without condition), the SQL query runs in less than 1s in SSMS (this makes me think that the SQL query complexity is not involved)

My local SQL Server is a Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64) Jul 22 2014 15:26:36 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I got better performance (less than 5s) on a distant SQL Enterprise server.

Does this mean that an EF application querying view is not adapted for a Express Edition ?

Where else can I look to understand why ?

===== =====

EF:

Trace with EF

SSMS:

Trace with SSMS

there is a factor 10 for performance. There must be something to find !

===== =====

I managed to degrade the performance of SSMS at those of EF using option (recompile) at the end of the query in SSMS.

How do I prevent EF to mimic option(recompile).

===== ===== =====

Always hoping for some ideas but right know EXEC sp_updatestats seems to fix the problem.

Community
  • 1
  • 1
tschmit007
  • 7,559
  • 2
  • 35
  • 43
  • 1
    One obscure issue I have run into is that EF runs with arithabort OFF by default. This can cause optimization problems and big slowdowns when using parameterized statements (which EF does). You can try taking the EF generated SQL and running it in SSMS with arithabort OFF. If it is slow there, then try turning it on for EF. The only way I have found is setting it before your query and wrapping them both in a transaction. – Peter May 12 '15 at 12:24
  • @Peter, good idea but arithabort is not involved in my case. – tschmit007 May 12 '15 at 12:34

0 Answers0