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
toON
and usingOPTION (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 toSIMPLE
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?
true
as the default value for itsbuffered
argument.