• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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
0
johnlewe
Asked:
johnlewe
1 Solution
 
leclairmCommented:
Take a look at ADO pages:

http://www.pbdr.com/vbtips/db/adopages.htm
0
 
Leo EikelmanCommented:
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:

http://authors.aspalliance.com/brettb/EasyADORecordSetPaging.asp

<%
'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>"
Next

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
oRecordSet.Close
set oRecordSet = nothing
set oConnection = nothing
%>

Leo


0
 
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.

0
 
Leo EikelmanCommented:
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

Leo
0
 
JackOfPHCommented:
******** 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 ***********
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now