Is it possible to return a large ADO query one batch at a time?

Hello All,

I am using VB 6 and ADO.  I run a query like "SELECT * FROM Products" which returns a recordset of 86,000 records. Watching the memory on my machine, it uses up 500 Mb of RAM.  Does anyone know if it is possible to run this same query using ADO, but to have it only load for example 1000 records into memory at a time and then would there be a method to call to retrieve into memory the next batch of 1000 records?

I have found the .nextrecordset method, but that appears to only work if you pass multiple queries to the ADO recordset and what I need is to pass only one query, but return the results in chunks of 1000 records at a time.

I have done my best to search the web looking for a solution, but I have not found one. I know it must be possible.

John Lewe
Orlando, FL
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Take a look at ADO pages:
Leo EikelmanDirector, IT and Business DevelopmentCommented:
RecordSet paging

Take a look at this code sample.  This is done in ASP and there is extra functionality but you can extract the paging concept from here.

You can find this example and an explanation here:

'Declare variables
Dim iCurrentPage
Dim iPageSize
Dim i
Dim oConnection
Dim oRecordSet
Dim oTableField
Dim sPageURL

'Declare constants
Const adOpenStatic = 3 'Open a RecordSet using a static cursor
Const adLockReadOnly = 1 'Open a RecordSet in read-only mode

'Retrieve the name of the current ASP document
sPageURL = Request.ServerVariables("SCRIPT_NAME")

'Retrieve the current page number from the QueryString
iCurrentPage = Request.QueryString("Page")
If iCurrentPage = "" Or iCurrentPage = 0 Then iCurrentPage = 1

'Set the number of records to be displayed on each page
iPageSize = 3

'An ADO connection string
oConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=PUBS_DATABASE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID= DATABASE_SERVER; User Id=PubsDBUser;PASSWORD=gt6Te4Ja;"

'An SQL statement
sSQLStatement = "SELECT * FROM Publishers"

'Create an ADO RecordSet object
Set oRecordSet = Server.CreateObject("ADODB.Recordset")

'Set the RecordSet PageSize property
oRecordSet.PageSize = iPageSize

'Set the RecordSet CacheSize property to the
'number of records that are returned on each page of results
oRecordSet.CacheSize = iPageSize

'Open the RecordSet
oRecordSet.Open sSQLStatement, oConnection, adOpenStatic, adLockReadOnly

'Move to the selected page in the record set
oRecordSet.AbsolutePage = iCurrentPage

'Display the opening HTML of a table
Response.Write "<table border=""0"" width=""50%"" cellpadding=""2"" cellspacing=""0"">"
Response.Write "<tr>"

'Loop through the fields in the RecordSet and
'display a column heading for each field
For Each oTableField In oRecordSet.Fields
Response.Write "<th width=""50%"" bgcolor=""#008080"" align=""left""><font color=""#FFFFFF""><b>" & oTableField.Name & "</b></font></th>"

Response.Write "</tr>"
Response.Write "<tr><td width=""50%"" bgcolor=""#C0C0C0"">"

'Use the GetString method to display the database rows
'The GetString method has the following parameters:
'StringFormat = This should be set to 2 (or the adClipString ADO constant)
'NumRows = Number of RecordSet rows to be used
'ColumnDelimiter = Delimiter to be used between columns
'RowDelimiter = Delimiter to be used between rows
'NullExpr = Expression to use for null values
Response.write oRecordSet.GetString(2, iPageSize, "</td><td width=""50%"" bgcolor=""#C0C0C0"">", "</td></tr><tr><td width=""50%"" bgcolor=""#C0C0C0"">", " ")

Response.Write "</td></tr></table>"

'Release database connectivity objects
set oRecordSet = nothing
set oConnection = nothing


johnleweAuthor Commented:
Looking at the code that you have supplied, we now have a recordset with a pagesize of 3 records.

How do you then loop through the records in each page? How do you know when you have reached the end of the page?

In my tests if you use oRecordset.Movenext, you can keep moving past 3 records, so how do you know when you have reached the end of a page?  Do you have to create a manual counter to count how many records you have looped through and then compare that to the known number of .PageSize?

Also, in my test, using the .PageSize code and method, all of the records are still loaded into memory all at once, so I still have the issue of 500 Mb of records are getting loaded into the computers RAM at one time.

Leo EikelmanDirector, IT and Business DevelopmentCommented:
yeah this is a common problem.

If you have an incremental Primary Key (such as 1-100000)

you can create a loop where it selects one record at a time and keep incrementing by 1 to get one record at a time.

Not the best solution but it could work.
I'll try to keep digging, but everyoen seems to be having the same problem

******** first time the timer runs ******************
FROM YourTableName
WHERE YourCondition

****** second time the timer runs ********************
SELECT top 1000  * from tablenmame where your <condition> not  in  (select top 1000  from tablename)

******** third time *******************************

SELECT top 1000  * from tablenmame where your <condition> not  in  (select top 2000 from tablename)

******** do the repeatition until you reach youre last record ***********

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.