ADO performance question 
Author Message
 ADO performance question

Hi,
this is a performance question.  I have some code that
loops through a "customer table" to find each "customer
id", and for each "customer id" creates a record in a
second table for each month in a specified time range.  
(i.e., a loop within a loop)

I'd like to code the two loops as two separate functions,
i.e. one function loops through the customer table,
calling a subfunction (another loop) once for each
customer.  However, even though the code might look
cleaner, i'm concerned about slowing down performance if
I pass the recordset to the subfunction for each customer.

not knowing much about how VB manages memory, is it best
to put the loop and subloop in one function, or is it
okay to use the function/subfunction suggested?



Wed, 16 Nov 2005 07:11:28 GMT  
 ADO performance question
You do not how many records you generally deal with.

However, passing the reocrdset is not going to kill the loop performance.

What WILL KILL things is if you create the recordset each time in a
loop...that is slow.

Passing the recordset?..hum, only the "memory" pointer is actually passed in
the routine, and the data is NOT COPIED.

In fact:

Private Sub  MyTest(ByVal i as integer, ByRef j as integer)

The above ByVal actually "copies" the data into a new variable called "i"

If during my code, I actually modify the value of "i" it WILL NOT change the
value that was passed to the sub. On the other hand if I change the value of
"j" in my sub, IT WILL modify the original value (since ByRef means
reference to the original).

It turns out that the "default" for passing parms is ByRef. Some developers
actually from upon this fact (since any accidental change of the var is then
passed back to the calling routine). However, ByRef has the advantage of
being VERY FAST since the data is NOT copied. For large arrays and things
like reocrdsets, then ByRef is really good, as the "size" does not matter.

--
Albert D. Kallal     (MVP)
Edmonton,  Alberta Canada

http://www.attcanada.net/~kallal.msn



Wed, 16 Nov 2005 07:42:18 GMT  
 ADO performance question
Why not just use a SQL statement?
--
Joe Fallon
Access MVP


Quote:
> Hi,
> this is a performance question.  I have some code that
> loops through a "customer table" to find each "customer
> id", and for each "customer id" creates a record in a
> second table for each month in a specified time range.
> (i.e., a loop within a loop)

> I'd like to code the two loops as two separate functions,
> i.e. one function loops through the customer table,
> calling a subfunction (another loop) once for each
> customer.  However, even though the code might look
> cleaner, i'm concerned about slowing down performance if
> I pass the recordset to the subfunction for each customer.

> not knowing much about how VB manages memory, is it best
> to put the loop and subloop in one function, or is it
> okay to use the function/subfunction suggested?



Wed, 16 Nov 2005 11:35:00 GMT  
 ADO performance question
Hi Albert,

Just to pick you up on a point: ByVal/ByRef used with an object variable
(such as a recordset) only relates to the object variable itself and not to
the actual object, which isn't passed. The difference is that essentially
ByVal passes 'the value of a pointer to the object', whereas ByRef passes 'a
pointer to a pointer to the object'. Since both are 4 bytes, there isn't a
significant speed difference between the two.

----
Simon Lewis



Quote:
> You do not how many records you generally deal with.

> However, passing the reocrdset is not going to kill the loop performance.

> What WILL KILL things is if you create the recordset each time in a
> loop...that is slow.

> Passing the recordset?..hum, only the "memory" pointer is actually passed
in
> the routine, and the data is NOT COPIED.

> In fact:

> Private Sub  MyTest(ByVal i as integer, ByRef j as integer)

> The above ByVal actually "copies" the data into a new variable called "i"

> If during my code, I actually modify the value of "i" it WILL NOT change
the
> value that was passed to the sub. On the other hand if I change the value
of
> "j" in my sub, IT WILL modify the original value (since ByRef means
> reference to the original).

> It turns out that the "default" for passing parms is ByRef. Some
developers
> actually from upon this fact (since any accidental change of the var is
then
> passed back to the calling routine). However, ByRef has the advantage of
> being VERY FAST since the data is NOT copied. For large arrays and things
> like reocrdsets, then ByRef is really good, as the "size" does not matter.

> --
> Albert D. Kallal     (MVP)
> Edmonton,  Alberta Canada

> http://www.attcanada.net/~kallal.msn



Wed, 16 Nov 2005 22:55:03 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. ADO performance question

2. ADO Performance Question

3. ADO Performance question

4. ADO's Performance vs DAO's Performance

5. ado performance - ms-access vs odbc

6. ADO Performance

7. Performance problems with shaped record sets and collections - VB6, ADO

8. How to improve ADO performance

9. Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

10. ADO: Performance Testing vs. DAO

11. Improving ADO performances against SQL Server?

12. ADO "AddNew" Performance Issue

 

 
Powered by phpBB® Forum Software