vendredi 27 février 2015

SSMS returns results immediately; ADO takes longer


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


I have read http://ift.tt/V20pUB 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?





Aucun commentaire:

Enregistrer un commentaire