Efficiency of SQL UPDATE vs Recordset .edit/.update

[-Michelle-]

Hi

Using A2003 on XP

I am wondering from the MVP's and others, what is the most efficient way (in
terms of time to process) of updating data in a table, using the
docmd.RunSQL or Recordset 'Edit' and 'Update'?

eg: (if you need it)

1.--------------------------

mysql = "Update [mytable] SET [SomeField] = [somevalue];"
docmd.RunSQL mysql


2.-----------------------

set myrecordset = mydb.OpenRecord set([tablename / sql])
myrecordset.edi t
myrecodset.fiel ds("myfield") = [somevalue]
myrecordset.upd ate


TIA
Michelle

[Steve Jorgensen]

Re: Efficiency of SQL UPDATE vs Recordset .edit/.update

On Wed, 4 Feb 2004 14:30:44 +1000, "-Michelle-" <milkyswirl@yah oo.com> wrote:
[color=blue]
>Hi
>
>Using A2003 on XP
>
>I am wondering from the MVP's and others, what is the most efficient way (in
>terms of time to process) of updating data in a table, using the
>docmd.RunSQL or Recordset 'Edit' and 'Update'?
>
>eg: (if you need it)
>
>1.--------------------------
>
>mysql = "Update [mytable] SET [SomeField] = [somevalue];"
>docmd.RunSQL mysql
>
>
>2.-----------------------
>
>set myrecordset = mydb.OpenRecord set([tablename / sql])
>myrecordset.ed it
>myrecodset.fie lds("myfield") = [somevalue]
>myrecordset.up date
>
>
>TIA
>Michelle[/color]

Well, whichever way is faster, SQL Update vs Recordset, DoCnd,RunSQL is a very
inefficient way to execute a SQL command, and there are other good reasons not
to use it from code. It's better to use the .Execute method of a DAO Detabase
or QueryDef object, or of an ADO Connection or Command object.

With regard to whether the SQL update or the recordset is more efficent, it
depends an awful lot on context, but the SQL is generally better. For one
thing, before you can update a record using a recordset, you must find it. If
this is done with a Where clause in a SELECT statement, you are no making
several calls through the database layer instead of just 1. If you do it with
FindFirst, you are searching through the rows in a recordset with no
optimization at all.

Another factor is that any query, whether it is a SELECT or an UPDATE must
first be compiled before it is run. If you execute a SQL statement
repeatedly, compiling it each time, this will be much slower than if you
compile it once, then execute it multiple times. If you do this with a saved
query that takes parameters, the query will be compiled the first time you run
it, and the compiled state will be saved with the query and reused next time.
If you use a temporary querydef in code and reuse the same querydef multiple
times, it will be compiled the first time you execute it, and the compiled
state will be preserved until the querydef variable is released.

Here's an example using a DAO temporary querydef from code.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prmFooID As DAO.Parameter
Dim prmFooName As DAO.Parameter

Set dbs = CurrentDB()
Set qdf = dbs.CreateQuery def("")
qdf.SQL = "UPDATE tblFoo SET tblFoo.FooName= prmFooName " & _
"WHERE tblFoo.FooID=pr mFooID"
Set prmFooID = qdf!prmFooID
Set prmFooName = qdf!prmFooName

prmFooID.Value= 1: prmFooName.Valu e="ABC"
qdf.Execute dbFailOnError ' Takes a brief time to compile before running.
prmFooID.Value= 2: prmFooName.Valu e="DEF"
qdf.Execute dbFailOnError ' Still comiled from previous Execute.
prmFooID.Value= 3: prmFooName.Valu e="GHI"
qdf.Execute dbFailOnError ' Still compiled.

' DAO can be unhappy if we don't clean up our objects in reverse order of
' dependency.
Set prmFooID = Nothing: Set prmFooName = Nothing
Set qdf=Nothing
Set dbs = Nothing

[Salad]

Re: Efficiency of SQL UPDATE vs Recordset .edit/.update

-Michelle- wrote:
[color=blue]
> Hi
>
> Using A2003 on XP
>
> I am wondering from the MVP's and others, what is the most efficient way (in
> terms of time to process) of updating data in a table, using the
> docmd.RunSQL or Recordset 'Edit' and 'Update'?
>[/color]

I prefer Currentdb.Execu te strSQL. RunSQL is fine too.

I sometimes will use Add/Update when I want to do an insert using the Insert
Into Table (fields....) Values (.....) command because of all the quote and
comma permutations required to write the SQL string that can be parsed by
Access....a PITA to write....and if the data has single or double quotes it gets
to be even more frustrating whileusing a recordset is easy to write and requires
little or no debugging.

[-Michelle-]

Re: Efficiency of SQL UPDATE vs Recordset .edit/.update

Thank you both for replying. Again, this newsgroup has provided an
invaluable service with information coming from real people in an
understandable format, not just from a reference book.

I couldn't live without the newsgroups.

Thanks
Michelle