3

Very odd slowdown when calling the Data Access Application block.

The SP ("QuestionsToBeAnswered") it's calling returns 58 rows with three columns (two GUIDs and an integer: 21AF77DA-2E76-47DB-AB54-0E5C85CD9AD8, 21AF77DA-2E76-47DB-AB54-0E5C85CD9AF0, 2) in less than 1 second when executed directly on the server. My SQL experience is pretty good, and I'm convinced the problem doesn't exist on the SQL server.

However, when it's called through DAAB, it's taking a very long time to return the collection of objects. ExecuteSprocAccessor(...) normally returns an IEnumerable, and the SP isn't executed until the collection is enumerated or otherwise consumed, so this problem doesn't show up until consumption occurs.

DatabaseInstance.ExecuteSprocAccessor<T>(storedProcedure, rowMapper, args);

Given that the same code has no problem returning >200 rows of considerably more complex information, I am baffled as to why this code is taking so long (55 seconds!) to execute.

Any ideas would be welcomed...

David Waldock
  • 31
  • 2
  • Try recompiling the stored procedure execution plan. – Jack Oct 31 '12 at 15:30
  • @Jack I've re-engineered the SP as part of this problem (which drastically reduced execution times to < 1 s), but it's the population of the class from the returned results which seems to take ~1 min. Thanks for your thought though! – David Waldock Nov 01 '12 at 07:29
  • I just want to confirm: this query is always fast in ssms, but is always slow in daab. Right? – John Tseng Jul 26 '13 at 13:27

1 Answers1

0

You could try running SQL Profiler to see what the actual call looks like when it run using DAAB. With that in hand take a look at the execution plan that it is using.

In the past I wrote some code that ran fine as a SQL query but was really slow as a stored procedure. Turns out do to some duplicate indexes the query optimizer was using a different execution plan for the SQL Query then the stored procedure. After getting the indexes sorted both ran at the same quick speed.

Kyle Wormsbacher
  • 56
  • 5