SSMS returns results immediately; ADO takes longer

Question

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?

Answer 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
}