Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Exclamation Unanswered: ADO Update Efficiency

    Hi All,

    I tried my luck in the Access forum and I've search the web and MSDN for an answer with little luck.

    Simply, is it better to update a table via an UPDATE query or Recordset manipulation?

    I have read that if you were to update 10,000 records an UPDATE query is more efficient (obviously), but does that transend down to say 1 - 10 updates?

    i.e. There are six unique updates I want to make to 6 different rows. Should I code the backend VB to execute 6 different queries or seek and update a recordset?

    It's a MS Access XP app with ADO 2.8.

    My gut feeling on this is that making 6 update queries is more efficient, both with system resources and record-locking issues; I'd just like another opinion on the matter.

    I appreciate your help!
    Thanks,
    Warren

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about this....

    store the keys and values in an access table and join the sql server table to it and perform the update....

    UPDATE s
    SET Col1 = a.Col1
    FROM SQLTable s INNER JOIN AccessTable a
    ON s.key = a.key
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Hey Brett,

    While that would be efficient, however the data is being populated via a form. To update a "search table" then run the query would include extra transactions: 6 queries/seeks to update the search table then another query to update the main table.

    I decided on this:

    Code:
    'con = open connection
    Con.Execute "UPDATE ...", , adExecuteNoRecords
    I felt this would be more efficient than

    Code:
    'rs open with appropriate connection; seek and index support
    rs.Index = "PrimaryKey"
    rs.seek "pkval1", "pkval2", adSeekFirstEQ
    rs!val1 = newval1
    rs!val2 = newval2
    rs.update
    I really can't find any documentation benchmarking the efficiency of ADO updates; it just doesn't seem to be out there.

    -Warren

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •