Optimizing updates

L

Leif

I'm try to update some VBA code to optimize the update of records in a table.
This is being done from within Excel (VBA) to an Access database.
Originally the updates were being done using ADO and SQL statementsby using a
delete and an insert. For example.

strSQL = "Delete from COMP_RD where ID = '" & Product & "' AND PROP=
'" & Property & "' AND Date_= #" & newDate & "# AND [CASE]='" & MBOCase & "'"
adoConn.Execute strSQL

strSQL = "Insert into COMP_RD (ID, PROP, DATE_, VALUE_, [CASE],
X_UPDATED) Values (" _
& "'" & Product & "', '" & Property & "','" & newDate & "'," &
newValue & ",'" & MBOCase & "', '" & Now & "')"

adoConn.Execute strSQL

So I though if I use a recordset instead, and use a seek to locate the
record, and only do an update instead of a delete and insert, it should run
much faster. My reasoning is that compile and executing the SQL statement
for each update is avoided. My new code looks like the following:


Set adoRst = New ADODB.Recordset 'NEW
adoRst.Index = "DATAKEY" 'NEW
adoRst.Open "COMP_RD", adoConn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect 'NEW

'Looping code and picking up Excel values here. Left out for clarity.

adoRst.Seek Array(sProduct, sProperty, dDate, MBOCase), adSeekFirstEQ
If adoRst.EOF Then adoRst.AddNew ' Record does not exist

adoRst!ID = sProduct
adoRst!Prop = sProperty
adoRst!date_ = dDate
adoRst!value_ = vValue
adoRst!case = MBOCase
adoRst!x_updated = Now
adoRst.Update

I'm very surprised that seek is no faster than executing SQL. In past
experience I found seek to be about 20x faster than find. Find will not work
here since it only accept 1 criteria, and I have 4.

The run times are basically the same. Any suggestions?

Thanks,
Leif
 
L

Leif

That seems to be the case. I'm surprised that even with the "compile" that
is necessary for a SQL statement, for every single record update, that it is
as fast as creating a recordset and using seek. Any suggestions on how
performance can be improved? I'm updating the table from an Excel
spreadsheet using individual cell contents (1 record/cell). I'm getting
about 500 records/min. running local on a Dell D600 laptop (1.6 GHz, 512MB
RAM).

Thanks,
Leif
Alex Dybenko said:
Hi,
seek is faster then find, because it uses primary key, but sql queries
should be as fast as seek (or perhaps faster), if you use same primary keys
fields for criteria

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Leif said:
I'm try to update some VBA code to optimize the update of records in a
table.
This is being done from within Excel (VBA) to an Access database.
Originally the updates were being done using ADO and SQL statementsby
using a
delete and an insert. For example.

strSQL = "Delete from COMP_RD where ID = '" & Product & "' AND PROP=
'" & Property & "' AND Date_= #" & newDate & "# AND [CASE]='" & MBOCase &
"'"
adoConn.Execute strSQL

strSQL = "Insert into COMP_RD (ID, PROP, DATE_, VALUE_, [CASE],
X_UPDATED) Values (" _
& "'" & Product & "', '" & Property & "','" & newDate & "'," &
newValue & ",'" & MBOCase & "', '" & Now & "')"

adoConn.Execute strSQL

So I though if I use a recordset instead, and use a seek to locate the
record, and only do an update instead of a delete and insert, it should
run
much faster. My reasoning is that compile and executing the SQL statement
for each update is avoided. My new code looks like the following:


Set adoRst = New ADODB.Recordset 'NEW
adoRst.Index = "DATAKEY" 'NEW
adoRst.Open "COMP_RD", adoConn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect 'NEW

'Looping code and picking up Excel values here. Left out for clarity.

adoRst.Seek Array(sProduct, sProperty, dDate, MBOCase), adSeekFirstEQ
If adoRst.EOF Then adoRst.AddNew ' Record does not exist

adoRst!ID = sProduct
adoRst!Prop = sProperty
adoRst!date_ = dDate
adoRst!value_ = vValue
adoRst!case = MBOCase
adoRst!x_updated = Now
adoRst.Update

I'm very surprised that seek is no faster than executing SQL. In past
experience I found seek to be about 20x faster than find. Find will not
work
here since it only accept 1 criteria, and I have 4.

The run times are basically the same. Any suggestions?

Thanks,
Leif
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi Leif,
in this case there in no big difference - you can use SQL update (or
insert), or recordset.
if you would update multiple records - then SQL update is faster.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Leif said:
That seems to be the case. I'm surprised that even with the "compile"
that
is necessary for a SQL statement, for every single record update, that it
is
as fast as creating a recordset and using seek. Any suggestions on how
performance can be improved? I'm updating the table from an Excel
spreadsheet using individual cell contents (1 record/cell). I'm getting
about 500 records/min. running local on a Dell D600 laptop (1.6 GHz, 512MB
RAM).

Thanks,
Leif
Alex Dybenko said:
Hi,
seek is faster then find, because it uses primary key, but sql queries
should be as fast as seek (or perhaps faster), if you use same primary
keys
fields for criteria

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Leif said:
I'm try to update some VBA code to optimize the update of records in a
table.
This is being done from within Excel (VBA) to an Access database.
Originally the updates were being done using ADO and SQL statementsby
using a
delete and an insert. For example.

strSQL = "Delete from COMP_RD where ID = '" & Product & "' AND
PROP=
'" & Property & "' AND Date_= #" & newDate & "# AND [CASE]='" & MBOCase
&
"'"
adoConn.Execute strSQL

strSQL = "Insert into COMP_RD (ID, PROP, DATE_, VALUE_, [CASE],
X_UPDATED) Values (" _
& "'" & Product & "', '" & Property & "','" & newDate &
"'," &
newValue & ",'" & MBOCase & "', '" & Now & "')"

adoConn.Execute strSQL

So I though if I use a recordset instead, and use a seek to locate the
record, and only do an update instead of a delete and insert, it should
run
much faster. My reasoning is that compile and executing the SQL
statement
for each update is avoided. My new code looks like the following:


Set adoRst = New ADODB.Recordset 'NEW
adoRst.Index = "DATAKEY" 'NEW
adoRst.Open "COMP_RD", adoConn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect 'NEW

'Looping code and picking up Excel values here. Left out for clarity.

adoRst.Seek Array(sProduct, sProperty, dDate, MBOCase),
adSeekFirstEQ
If adoRst.EOF Then adoRst.AddNew ' Record does not exist

adoRst!ID = sProduct
adoRst!Prop = sProperty
adoRst!date_ = dDate
adoRst!value_ = vValue
adoRst!case = MBOCase
adoRst!x_updated = Now
adoRst.Update

I'm very surprised that seek is no faster than executing SQL. In past
experience I found seek to be about 20x faster than find. Find will
not
work
here since it only accept 1 criteria, and I have 4.

The run times are basically the same. Any suggestions?

Thanks,
Leif
Click to expand...
Click to expand...
Click to expand...
 
Top