how to retrieve thousands of rows from a store procedure efficiently

Question

Hello everyone,

I am using VSTS 2008 + C# + .Net 3.0 + ADO.Net + SQL Server 2008. And from ADO.Net I am invoking a store procedure from SQL Server side. The store 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

Answer 2

You don't say what you want to do with the data. However, assuming you need to process the results in .NET then reading the results using an SqlDataReader would be the most efficient way.

Answer 3

I'd use exists for one.

SELECT
    Table1.col2
FROM
    Table1
WHERE
    NOT EXISTS (SELECT *
         FROM
            Table2
         WHERE
            Table2.col1 = Table1.col1)

The query can be efficient (assume col1 is indexed but covers cols (very wide index of course), but you still have to shovel a lot of data over the network.

It depends what you mean by performance. 5000 rows isn't much for a report but it's a lot for a combo box