Posted by Plamen Ratchev on 02/01/07 05:45

DataReader by definition returns data as the query executes, one row at a
time. The only buffering occurs on the client where data is kept in the
network buffer until requested by the Read method. Based on that you should
be seeing faster response.

Do you use an ORDER BY clause in your query? That could be the issue, since
in that case the data needs to be sorted on the SQL Server side before the
first row is returned.

Is there any reason to display all 250,000 rows in your DataGrid? I have
implemented many similar client applications and the best way to handle huge
load has been to use paging. Normally 25-100 records per page are easy for
users to look through. With that page size returning data is instantaneous.
Then provide direct links to the other pages and allow users to jump quickly
to a page.

Here is an article with a few useful hints on optimizing ADO.NET
performance:
http://msdn2.microsoft.com/en-us/library/ms998569.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]