Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Westport, CT

    Unanswered: Improve Performance on Inserts

    I have a MS Access 2000 front-end for a SQL 2000 db. I have a form that inserts a record which uses the following code. The code works fine, but is too slow. I've changed the fillfactor to 80 in SQL Server, and that didn't help.

    rstSerials.Open "SELECT * FROM tblS", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    vCurrentSerial = Trim(UCase(txtPrefix)) & Trim(txtFrom) & Trim(UCase(txtSuffix))
    'On Error Resume Next
    intMaxEntry = intMaxEntry + 1
    rstSerials!SNumber = vCurrentS
    rstSerials!Job = vjob
    rstSerials!EntryOrderSeq = intMaxEntry
    rstSerials!EntryDateTime = Now()
    rstSerials!SubSetNbr = Me!txtSubset

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59

    finger trouble, as usual

    if you are inserting a row, why do you "select *" from a table immediately prior to the insert

    I'd have thought it would make more sense to insert directly. "insert into..."

    Not sure what the column 'IntmaxEntry' relates to -is this in effect an autonumber field (ie is the the table defintion suspect), alternatively would it be better to define an intial query to retrive the current highest intmaxentry then a new query to "insert into..."

    If you don't use the results from "select *....." , ie you do't actually do anything with the query why run it in the first place (this will have a significant impact on times especialy if the specified table has many rows).

    perhaps the strategy is
    query 1: find the current highest intmaxentry
    query 2: insert new record
    'doing it he way you are proposing could have problems unless you secure a table lock between tieh find the current intmaxentry, and the time you commit the new record. If you can use the autonumber filed then it absolves you from having to handle the risks of two (or more) users wanting to update the intmaxentry concurrently.
    Last edited by healdem; 12-02-04 at 17:22.

Posting Permissions

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