Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding records slowing down (Access 97 SR2)

    With the following method inserting data into a table linked from an SQL database, would there be any specific reason why it would be slow adding a single record?
    I suspect its due to the fact that the table has 300,000 records but I'd like a second opinion on this, any help would be much appreciated.

    Set SQuery = dbs.OpenRecordset("PJStockTestEdcoms", dbOpenDynaset, dbSeeChanges, dbOptimistic)
    With SQuery
    ' Add new record to end of Recordset object.
    .AddNew
    !ProductID = 1
    !teacherid = 1
    .Update ' Save changes.
    End With

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Adding records slowing down (Access 97 SR2)

    Since you are using Access, I presume you are using ODBC linked tables and DAO. (the version of SQL Server would be useful as well - I assumed 7 or 2000.)
    Yes, adding a record to a 300K record table will likely be slow. The reason is that the way you are opening the recordset puts it on the first record of the table, and then it has to scroll through the entire table to get to the and and perform the AddNew. An approach that would net some improvement would be:

    Set SQuery = dbs.OpenRecordset("PJStockTestEdcoms", dbOpenForwardOnly, dbAppendOnly, dbOptimistic)

    This may still not be satisfactory, but should be a fair bit faster. To get maximum speed you probably should look at either ODBC Direct, or pass data to a PassThrough query which executes a stored procedure.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records slowing down (Access 97 SR2)

    Thanks for your reply Wendel, sorry about the thin information.
    You were right about ODBC and SQL is 2000.

    Ill give you're suggestion a try and see how it goes.

    I have thought of an alternative solution but its a bit long winded:
    Have a table of stock and a table of stock_entries. Use an SQL job to append the entries to the main table and empty the entries table every night keeping the entry table low, and for any reports etc, a union query can be used, but I realise this could slow down the reports a bit.

    Hopefully your solution will be sufficient, thanks again Wendel.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records slowing down (Access 97 SR2)

    I tried the following:
    Dim SQuery As Recordset, dbs As Database
    Set dbs = CurrentDb
    Set SQuery = dbs.OpenRecordset("PJStockTestEdcoms", dbOpenForwardOnly, dbAppendOnly, dbOptimistic)
    and got "operation is not supported for this type of object" when attempting the with squery .add command.

    I changed it to:
    Set SQuery = dbs.OpenRecordset("StockTransactions", dbOpenDynaset, dbAppendOnly, dbOptimistic)
    This worked fine in the .mdb but as soon as I changed it to an .mde it stopped working and returned the error:

    Object Variable or with block variable not set.

    The whole code is, there was standard error handling but I removed it to post here:
    Public Sub insertTest()
    Dim SQuery As Recordset, dbs As Database
    Set dbs = CurrentDb
    Set SQuery = dbs.OpenRecordset("PJStockTestEdcoms", dbOpenDynaset, dbAppendOnly, dbOptimistic)

    With SQuery
    ' Add new record to end of Recordset object.
    .AddNew
    !ProductID = 1
    !teacherid = 1
    !Quantity = 1
    !daterequest = Date
    !Status = "A"
    !TransactionType = "REQUEST"
    .Update
    End With
    SQuery.Close

    End Sub

    I also forgot to mention that the database is multi user, i.e. an MDE gets sent to a data entry team, sorry.
    I have never used ODBC direct, so I may have a go at the stored procedure use if I cant get my current code working.
    Any help would be much appreciated.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records slowing down (Access 97 SR2)

    Does the table in SQLServer have a lot of indexes on it ?
    - if I'm doing a lot of data insertions, I try and drop the indexes before the load, then recreate them after the insertions are all done

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records slowing down (Access 97 SR2)

    I did think about this yesterday, but after my initial test with Wendel's idea worked I dismissed it, but seeing as the working version wont currently work in an .mde I may as well have another look into it.............

    There are currently 10 fields in the table, 4 of which had indexes. I removed 3 of these leaving just the primary key indexed and the update speed didn't change. When a record is added the indexes get updated server side don't they? If this is the case it should be okay as our server is powerful.

    I think it is solely the speed of the current method i'm using to add a record due to Wendel's explanation (going through the records before adding it).
    Thanks for your suggestion though.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Adding records slowing down (Access 97 SR2)

    Something else appears to be amok here. Creating an MDE version of the database shouldn't cause any real problems with execution. (BTW, your use of the dbOpenDynaset is correct - I took something from another app and intended to change it, but didn't.)

    One of the challenges when using DAO with ODBC linked tables is that ODBC errors often get translated into something that looks like an Access error, but is really some sort of problem with the execution of the code. I suspect that is what happened with your MDE version. Is it possible that you tried to insert a record that would have created a duplicate key, or some other type of data integrity problem?

    After a bit more reflection, I doubt a stored procedure would significantly improve your insert performance - it would probably be faster, but not all that much, as you would have to create a QueryDef in order to use a PassThrough query to supply the details to the server. With Access 97, I think your code should give reasonable performance - we have done inserts of tens or hundreds of records into tables containing a million or more records and gotten acceptable performance.
    Wendell

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding records slowing down (Access 97 SR2)

    Isn't the whole point of a pass-through query that is does, in fact, execute on the server and bypass the ODBC connection? In effect, you're running a stored procedure in SQL Server and you have to observe the syntax for SQL Server when you create one.
    Charlotte

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Adding records slowing down (Access 97 SR2)

    That's my view of the situation. Unfortunately, to use a pass-through query that does an insert, you first have to create (or modify) the QueryDef in order to pass the values to be inserted to SQL Server. The time taken in Jet storing the QueryDef tends to wipe out the advantage of bypassing the ODBC layer and it's overhead.
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Adding records slowing down (Access 97 SR2)

    Rather than using the method you are trying, have you tried an INSERT INTO statement?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records slowing down (Access 97 SR2)

    ... and use a docmd.runsql statement? I have yet, no but thank you for the suggestion, ill give it a go.

    In the mean time, I may have found something out, i'm just testing it some more:

    Concerning the Object Variable or with block variable not set error in an .mde I found the error didnt appear after i'd opened the table. Therefore I put the following 2 lines in the code and my test version now seems to work!

    Set SQuery = dbs.OpenRecordset("PJStockTestEdcoms", dbOpenDynaset, dbSeeChanges, dbOptimistic)
    SQuery.close

    Is it me or is that a bit strange?

Posting Permissions

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