Inside a loop, i am opening recordsets using the same variable at the beginning of each iteration... My question is, should I include a ".Close" at the end of the iteration or should I just keep re-setting it to a new value at the beginning. Is there any bad side to doing that?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Close recordset or just re-set it?
- Thread starter Paladyr
- Start date
- Thread starter
- #4
stoolpigeon
Programmer
Just a little to add from personal experience.
Sometimes closing a recordset can take a long time (compared to everything else)
When I've been doing a high number of iterations I've found that I increase my speed significantly by resetting a recordset instead of closing it. It may have taken more memory but that was not a problem- I wanted speed more than anything.
Just something for you to watch out for if you change your code.
Sometimes closing a recordset can take a long time (compared to everything else)
When I've been doing a high number of iterations I've found that I increase my speed significantly by resetting a recordset instead of closing it. It may have taken more memory but that was not a problem- I wanted speed more than anything.
Just something for you to watch out for if you change your code.
- Thread starter
- #6
- Thread starter
- #8
With DAO you only need to create your two recordset obects once (outer loop rs and inner query rs) because DAO has much better memory handleing vs ADO and you can keep on reusing the recordset object with out worring about memory. (See above)
If you are using DAO, then there is also most certainly a connection - with the database object. Keep this open.
I see no problem under DAO to just re-open the recordset, with-out closing it first, (as long as you are using it with-in the same scope), except, if you thought you re-opened it, but didn't - then you may bad wrong data. It is a little sloppy to do it that way.
But, sloppy is also a big subject and sometimes only in certain aspects. Sometimes it is better to be "sloppy".
Is it sloppy to use in a class public property variables instead of property proceedures? Probably. And it goes against encapsulating the data. But it is much slower to do so. They can really slow things down when speed may be important.
Under ADO, however, in all cases close the recordset first.
What I meant with "...Actually, it has nothing to do with an extra step...." was not the fact that the extra step causes extra work, and in turn slows things down. Actually you shouldn't notice it slowing things down anyways, under normal circumstances and usage, unless you also close the connection/database as well, and then re-open it.
The problem is with not just closing it. Under ADO you may run into other problems if you don't.....
I could list some specifics as to what problems, but I'm out of time today....
I see no problem under DAO to just re-open the recordset, with-out closing it first, (as long as you are using it with-in the same scope), except, if you thought you re-opened it, but didn't - then you may bad wrong data. It is a little sloppy to do it that way.
But, sloppy is also a big subject and sometimes only in certain aspects. Sometimes it is better to be "sloppy".
Is it sloppy to use in a class public property variables instead of property proceedures? Probably. And it goes against encapsulating the data. But it is much slower to do so. They can really slow things down when speed may be important.
Under ADO, however, in all cases close the recordset first.
What I meant with "...Actually, it has nothing to do with an extra step...." was not the fact that the extra step causes extra work, and in turn slows things down. Actually you shouldn't notice it slowing things down anyways, under normal circumstances and usage, unless you also close the connection/database as well, and then re-open it.
The problem is with not just closing it. Under ADO you may run into other problems if you don't.....
I could list some specifics as to what problems, but I'm out of time today....
- Thread starter
- #12
I've been using counters to track memory usage and sometimes it uses more memory when using .close and sometimes it doesn't so I guess the difference is negligible. Thanks for the replies!
Oh, and do you have any links to info on why ADO is better than DAO?? I have yet to see any difference other than syntax, and that isn't enough to make me want to start using it across the board.
Oh, and do you have any links to info on why ADO is better than DAO?? I have yet to see any difference other than syntax, and that isn't enough to make me want to start using it across the board.
Can you tell me whether this is using ADO or DAO or something else and if it is ADO, how would I use DAO in code?>
EXAMPLE:
Set MyDb = OpenDatabase("C:\MyDatabase.mdb"
'once only
Set MyRec = MyDb.OpenRecordset("SELECT * FROM MyTable etc", dbOpenDynaset) 'whenever I need to change the SQL
With MyRec
'use it here then
End With
MySet.close
set MySet=Nothing
I keep the Mydb open until the prog exits and only close the MySet each time so I can change the SQL to something else.
This doesnt seem to loose any memory and is as fast as you can click on the mouse on an indexed table with 10000 records
EXAMPLE:
Set MyDb = OpenDatabase("C:\MyDatabase.mdb"
'once onlySet MyRec = MyDb.OpenRecordset("SELECT * FROM MyTable etc", dbOpenDynaset) 'whenever I need to change the SQL
With MyRec
'use it here then
End With
MySet.close
set MySet=Nothing
I keep the Mydb open until the prog exits and only close the MySet each time so I can change the SQL to something else.
This doesnt seem to loose any memory and is as fast as you can click on the mouse on an indexed table with 10000 records
- Thread starter
- #15
- Status
Similar threads
- Question