Ensuring a SProc doesnt return too much data

Question

im wondering how you would ensure that the results returned from a SProc, and stored in a datatable, are not too large to be handled in memory.

Ive had a dig already and found the following Find size of object instance in bytes in c# but the selected answer suggests not doing this.

Im assuming this is an issue which has been solved before so id appreciate any pointers.

Thanks.

Does the sproc return variable-length fields, like `VARCHAR(MAX)`, `VARBINARY(MAX)` or similar? — Quassnoi, Jun 04 '12 at 10:59
Atm, the database just contains float, and datetime2 datatypes. — Hans Rudel, Jun 04 '12 at 11:01
just limit your resultsets with `SELECT TOP 1000` or similar. — Quassnoi, Jun 04 '12 at 11:02
im assuming its possible to have a loop set up in c# to select 1001 to 2000 etc? — Hans Rudel, Jun 04 '12 at 11:04

score 2 · Accepted Answer · answered Jun 04 '12 at 11:07

2

You may want to implement paging.

To do this, add @pageNum and @pageSize parameters to your SP and change the query:

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY whatever_field) rn
        FROM    (
--              here goes the old query
                ) q
        WHERE   rn BETWEEN @pageSize * @pageNum + 1 AND @pageSize * (@pageNum + 1)
        ) q
ORDER BY
        wnatever_field

answered Jun 04 '12 at 11:07

Quassnoi

413,100
91
616
614

i had just stumbled upon the same idea after u suggested returning the results in chunks. I'll experiment with the above and see how i get on. Thanks very much for your help, i really appreciate it. – Hans Rudel Jun 04 '12 at 11:10

Ensuring a SProc doesnt return too much data

1 Answers1