3

I am running a query that returns ~250k results.

  • It is basically SELECT * FROM my_schema.my_view (and the view is somewhat complex, and all table references in it are two-part)
  • I am not passing any parameters
  • I have tried setting ARITHABORT to ON and using OPTION (RECOMPILE)
  • Within 2 seconds of executing, SSMS has returned ~23k results
  • SSMS takes ~1m to return all ~250k results
  • With ADO and the same query, it takes ~1m to obtain the first result from the DataReader
  • PARAMETERIZATION is set to SIMPLE

Code example (using Dapper):

foreach (var result in connection.Query("SELECT * FROM dbo.my_view"))
{
    // process each result in here
}

I have read http://www.sommarskog.se/query-plan-mysteries.html and about a couple dozen other SO/DBA answers trying to make sense of this but I just can't seem to get my results streaming any faster. It almost seems like when I use ADO, SQL Server tries to store all of the results in a temporary table before returning.

What else can I check or try to get my results streaming immediately like they do in SSMS?

  • Try not returning all 250K rows at once. Is your DataReader really going to eventually display 250K rows to the user? Why? What sane user can consume 250K rows of anything? – Aaron Bertrand Feb 27 '15 at 16:38
  • This is a console application which rebuilds an Elasticsearch index. – tuespetre Feb 27 '15 at 16:42
  • 1
    Son of a... well, I think I found the issue. I didn't realize Dapper has true as the default value for its buffered argument. – tuespetre Feb 27 '15 at 16:44
  • 1
    That's why you should post code in cases like this...Dapper isn't even mentioned. – Pablo Montilla Feb 27 '15 at 22:33
  • You're right... – tuespetre Feb 27 '15 at 23:06
1

As Pablo Montilla mentioned in a comment on the question, I should have added a code sample to indicate that I was using Dapper. (I have since added the sample.)

The solution was to pass an argument to Dapper's Query method to tell it not to buffer all of the results before letting me iterate over them. The default behavior of Dapper is to buffer the results first.

foreach (var result in connection.Query("SELECT * FROM dbo.my_view", buffered: false))
{
    // process each result in here
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.