how to retrieve thousands of rows from a stored procedure efficiently

Question

I am using VSTS 2008 + C# + .Net 3.0 + ADO.Net + SQL Server 2008. And from ADO.Net I am invoking a stored procedure from SQL Server side. The stored procedure is like this,

SELECT Table1.col2
FROM Table1
LEFT JOIN Table2 USING (col1)
WHERE Table2.col1 IS NULL

My question is, how to retrieve the returned rows (Table1.col2 in my sample) efficiently? My result may return up to 5,000 rows and the data type for Table1.col2 is nvarchar (4000).

thanks in advance, George

Answer 1

You CANNOT - you can NEVER retrieve that much data efficiently....

The whole point of being efficient is to limit the data you retrieve - only those columns that you really need (no SELECT *, but SELECT (list of fields), which you already do), and only as much rows as you can handle easily.

For instance, you don't want to fill a drop down or listbox where the user needs to pick a single value with thousands of entries - that's just not feasible.

So I guess my point really is: if you really, truly need to return 5000 rows or more, it'll just take its time. There's not much you can do about that (if you transmit 5000 rows with 5000 bytes per row, that's 25'000'000 bytes or 25 megabytes - no magic go make that go fast).

It'll only go really fast if you find a way to limit the number of rows returned to 10, 20, 50 or so. Think: server-side paging!! :-)

Marc