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

Posted on 2006-03-24
Last Modified: 2010-04-07
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
Question by:johnlewe
    LVL 11

    Expert Comment

    Take a look at ADO pages:
    LVL 8

    Expert Comment

    by:Leo Eikelman
    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



    Author Comment

    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.

    LVL 8

    Expert Comment

    by:Leo Eikelman
    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

    LVL 15

    Accepted Solution

    ******** first time the timer runs ******************
    SELECT TOP 1000 *
    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 ***********

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now