Geek Answers Handbook

Linq vs sql (or .NET application against SQL Server management studio)

I have a linq query that retrieves rows from a view based on id column (where id = @id)

This request takes 4 seconds. I used SQL Server Profiler to verify the query that linq is executing, and if I copy this query directly to the management studio and execute, the query takes only 56 ms.

This exponential increase in time is consistent across all LINQ queries with the views in my application. What could be the reason for this extended runtime in my (WPF) application when the same requests are executed <100 ms?

== EDIT ==

I was able to further highlight, comments show the duration of the profiler;

/* 3953ms, 111487 reads */ context.SkuView.Where(p => p.TermId == 35 && !p.IsDeleted).ToList(); /* 90ms, 173 reads */ context.SkuView.Where(p => p.TermId == 35).ToList();

If I embed (sql rendered) linq queries directly in ssms, I get;

 /* 250ms, 173 reads */ SELECT * FROM SkuView WHERE TermId == 35 AND IsDeleted = 0 /* 250ms, 173 reads */ SELECT * FROM SkuView WHERE TermId == 35

So the problem is reading count through linq when using! p.IsDeleted ...

+4
source share
4 answers

Possible culprits are:

  • discord. When launched from Linq, other application actions are line locking and thuse, which causes the request to stop waiting for locks. When starting from SSMS, there is no other activity, and thus the request ends quickly.
  • difference in types of parameters. Passing the NVARCHAR parameter for comparison with the VARCHAR column results in a full scan (the index cannot be used due to data priority rules ). This is caused by an invalid LINQ column character. When starting from SSMS, the request is usually copied without errors and the type of the parameter changes to VARCHAR.
  • cold run versus warm run. The query is first launched by LINq, and this heats up the cache (retrieves data from disk to memory). When restarting from SSMS, there is no I / O wait.

In any case, investigation tools are at your disposal.

  • compare the number of Reads from the two queries ( RPC: Complete , TSQL: BatchComplete in the profiler)
  • compare plans. Use the Showplan XML event .
  • see what the LINq query does: sys.dm_exec_requests wait_type, wait_time and wait_resource columns
  • Compare query statistics for two cases: sys.dm_exec_query_stats . the things you need to look for are the big differences between the two cases in logic_reads and physical_reads, which indicates completely different plans (scanning versus search) or wild differences in elapsed_time, but with a similar working country (which indicates a lock, probably a lock).
+7
source

updating db statistics fixed this problem.

 exec sp_updatestats

Many thanks to Remus for the training;)

+2
source

This request takes 4 seconds to run ... if I copy this request directly to the management studio and execute it, the request takes only 56 ms.

There is no magic difference between your application and management studio. Both programs create a connection to the database and issue sql text commands (once inside the database server: a query plan is generated, IO and CPU are consumed, and the results are transferred back). Since the only difference here is the “connection making application”, you should check the connections. Start with connection strings ...

Assuming there is no problem in the connection string, go to Installation Settings . In particular, SET ANSI_NULLS must be enabled, as it can interfere with computed columns and views with cluster indexes.

0
source

ARITHABORT is enabled by default in SSMS and OFF by default for a SqlClient connection.

This solved a similar problem for me:

 new SqlCommand("SET ARITHABORT ON", connection).ExecuteNonQuery();
0
source

More articles:


All Articles