CODEWITHSUNDEEP
wcfentity-frameworkstored-procedures
MicMit
MicMit

Reputation: 2462

Stored procedure call is very slow from Entity Framework 4 due to inexplicable reason

Stored procedure (query ) when run in Management Studio takes 4-8 seconds. However, when launched inside WCF service via Entity Framework it may take more than minute. WCF runs in VS 2010 in debugging mode with ASP.Net Development Web Server.

The actual call 

requests = transactionEntities.spRequestsForRescreening(cutoffDate).ToArray();

Since all SQL code encapsulated in a stored procedure don't know what I could look at to understand the reason causing this slowness.

Upvotes: 1

Views: 943

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294307

While the call is running from the WCF call, you have a full minute to investigate what's going on. Check sys.dm_exec_requests and inspect the WCF request. The relevant columns are:

  • wait_time, wait_type and wait_resource to see what is blocking the execution (IO, locking, resource contention, slow client etc)
  • blocking_session_id to see what other session is blocking execution
  • sql_handle, statement_start_offset and statement_end_offset to understand which statement inside the SP is slow (cross apply sys.dm_exec_sql_text)
  • plan_handle to inspect the execution plan (cross apply sys.dm_exec_query_plan)

Inspect these values several times during execution, from an SSMS query window, to get a grasp of what is the call issues from WCF doing and why it takes so long.

Upvotes: 1

Related Questions