Which Is Faster ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have tblProduct with 100,000 records and with columns like ProdID, Cost,
QtyRemained
I have a tblInvoiceDetail with 10,000 records and with columns like ProdID,
ProdCost, SalesQty, ....
Now, I have to loop through tblInvoiceDetail and get the Cost from
tblProduct for every row in tblInvoiceDetail.

Which Approach is faster - 1 or 2 ?

Approach 1:-
set rstInvoiceDetail = dbs.OpenRecordset("Select * from tblInvoiceDetail",
dbOpenDynaset, dbseechanges)

set rstProd = dbs.Openrecordset("Select * from tblProduct",dbOpenDynaset,
dbseechanges)

Do while rstInvoiceDetail.eof
rstProd.FindFirst "strProductID = """ & rstInvoiceDetail("strProductID") &
""""
rstInvoiceDetail.Edit
rstInvoiceDetail("ProdCost") = rstProd("Cost")
rstInvoiceDetail.Update
rstInvoiceDetail.MoveNext
Loop

Approach 2:-
set rstInvoiceDetail = dbs.OpenRecordset("Select * from tblInvoiceDetail",
dbOpenDynaset, dbseechanges)

Do while rstInvoiceDetail.eof
strSQL = "Select * from tblProduct where strProductID = """ &
rstInvoiceDetail("strProductID") & """"
set rstProd = dbs.Openrecordset(strSQL,dbOpenDynaset, dbseechanges)

rstInvoiceDetail.Edit
rstInvoiceDetail("ProdCost") = rstProd("Cost")
rstInvoiceDetail.Update
rstInvoiceDetail.MoveNext
Loop

Thank You
Woo
 
Woo Mun Foong said:
Hi,

I have tblProduct with 100,000 records and with columns like ProdID,
Cost, QtyRemained
I have a tblInvoiceDetail with 10,000 records and with columns like
ProdID, ProdCost, SalesQty, ....
Now, I have to loop through tblInvoiceDetail and get the Cost from
tblProduct for every row in tblInvoiceDetail.

Which Approach is faster - 1 or 2 ?

Approach 1:-
set rstInvoiceDetail = dbs.OpenRecordset("Select * from
tblInvoiceDetail", dbOpenDynaset, dbseechanges)

set rstProd = dbs.Openrecordset("Select * from
tblProduct",dbOpenDynaset, dbseechanges)

Do while rstInvoiceDetail.eof
rstProd.FindFirst "strProductID = """ &
rstInvoiceDetail("strProductID") & """"
rstInvoiceDetail.Edit
rstInvoiceDetail("ProdCost") = rstProd("Cost")
rstInvoiceDetail.Update
rstInvoiceDetail.MoveNext
Loop

Approach 2:-
set rstInvoiceDetail = dbs.OpenRecordset("Select * from
tblInvoiceDetail", dbOpenDynaset, dbseechanges)

Do while rstInvoiceDetail.eof
strSQL = "Select * from tblProduct where strProductID = """ &
rstInvoiceDetail("strProductID") & """"
set rstProd = dbs.Openrecordset(strSQL,dbOpenDynaset, dbseechanges)

rstInvoiceDetail.Edit
rstInvoiceDetail("ProdCost") = rstProd("Cost")
rstInvoiceDetail.Update
rstInvoiceDetail.MoveNext
Loop

Thank You
Woo
Click to expand...

Those are both *terribly* inefficient was to do it! As far as I can
tell, all you really need to do is run a simple update query with SQL
like this:

UPDATE
tblInvoiceDetail INNER JOIN tblProduct
ON tblInvoiceDetail.ProdID = tblProduct.ProdID
SET tblInvoiceDetail.ProdCost = tblProduct.Cost;
 
Thanks Dirk.
But for the sake of discussions which approach do you think yield a faster
result?
I should have try code and run both but maybe somebody out there already
knows the difference.

Thank You
Woo
 
Woo Mun Foong said:
Thanks Dirk.
But for the sake of discussions which approach do you think yield a
faster result?
I should have try code and run both but maybe somebody out there
already knows the difference.
Click to expand...

For the sake of discussion -- since they're both terrible ways to do
it -- you're asking which would be better:

(A) Open both recordsets once, loop through one, and inside the loop use
a FindFirst on the other to locate the single record that matches the
current record in the looping recordset,

or

(B) Open one recordset and loop through it. Inside the loop, open a
recordset on a query the returns the single matching record from the
other table.

I'm only guessing here, but I think (B) would prove more efficient,
since I think it would make use of an index on the key field ProdID
which I don't think the .FindFirst method used in (A) would. Also, it
would only bring the matching records across the network, and it would
only bring them one at a time.

However, that's all speculation, and only careful benchmark will tell
you for sure.
 
Dirk said:
For the sake of discussion -- since they're both terrible ways to do
it -- you're asking which would be better:

(A) Open both recordsets once, loop through one, and inside the loop use
a FindFirst on the other to locate the single record that matches the
current record in the looping recordset,

or

(B) Open one recordset and loop through it. Inside the loop, open a
recordset on a query the returns the single matching record from the
other table.

I'm only guessing here, but I think (B) would prove more efficient,
since I think it would make use of an index on the key field ProdID
which I don't think the .FindFirst method used in (A) would. Also, it
would only bring the matching records across the network, and it would
only bring them one at a time.

However, that's all speculation, and only careful benchmark will tell
you for sure.
Click to expand...

If you're limited to using FindFirst, I generally agree with
Dirk, but in **some** cases, I have found that method (A)
using Seek instead of FindFirst can be much faster.

If most/all of the records in the second recordset will
eventually be retrieved anyway, you're better off getting
them with a single query. And, since Seek requires an
index, it is very fast.

However, if there is any way to do this in a single query
that Joins the two tables, it will be far superior to any
code looping approach.
 
Marshall Barton said:
If you're limited to using FindFirst, I generally agree with
Dirk, but in **some** cases, I have found that method (A)
using Seek instead of FindFirst can be much faster.
Click to expand...

Agreed. But the use of the dbSeeChanges option in the original code
suggests that the recordsets are opened on linked SQL Server tables,
which would make it impossible to open a table-type recordset and use
Seek.
 
Dirk said:
Agreed. But the use of the dbSeeChanges option in the original code
suggests that the recordsets are opened on linked SQL Server tables,
which would make it impossible to open a table-type recordset and use
Seek.
Click to expand...


I missed that little nuance ;-)

Kind of makes my comments irrelevant :-(

I better learn to read more carefully. Either that, or keep
the mouth closed so my foot doesn't fall in.
 
Back
Top