Adding records slowing down (Access 97 SR2)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Rather than using the method you are trying, have you tried an INSERT INTO statement?
Pat

Reply | Quote

… 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?

Reply | Quote