hdsjunk

I have a VB6 application that allows the user to provide criteria that is sent into parameters of a SQL stored procedure. The stored procedure selects records based on the criteria, and returns the data to a disconnected recordset in VB. My issue is that some of the recordsets being returned are very large, thus the stored procedure runs slow. My questions is this:

Is there any way to tell the stored procedure to only return so many, and then somehow in VB, tell it to get the next batch of records when needed? I'm assuming maybe by using fetch or something? I am pretty desperate at this point, so any advise, code snippets, or other links would be more than appreciated.

Thank you!

Optikal

If your using Oracle as your database you can use a filter on rowid, but as far as I know Oracle is the only DBMS that supports that.

VBJoe

I've run into this problem before, and I haven't found an easy solution.

I don't know what Database engine you're using, but if you're using SQL Server, you can try this little trick:
1) In the Stored Procedure, rather than returning all those records, populate them into a temporary table that has a separate identity field.
2) In your code, build a query that uses TOP to return only the first x rows from the temporary table, then do what you need to with those records.
3) Find the value of the last record returned, and write a DELETE query that kills every record before that one.
4) Repeat steps 2 and 3 until you run out of rows to process.

I know that it seems a little clunky, but I've actually used this technique successfully.