Re: Append query is slow! Would VB be faster?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Adding 300,000 records in 5 minutes is not really that slow if you have a
lot of indexes and if the query is taking place in a transaction.

If you are just executing the query from the database window, then you are
probably running it in a transaction. With that many records, there is a
chance that Access may have to write some of the data out to a swap file.

Try going to the query property and setting the query property use
transaction to No. See if that improves the speed.

From the help file
When a make-table, delete, append, or update query has its UseTransaction
property set to Yes, the Microsoft Jet database engine stores the returned
records in a cache and, if necessary, stores the results in a temporary
database on disk. When the query has finished, the Jet database engine reads
the records from the temporary database and writes them back to the original
database.

You may get substantial performance benefits by setting the UseTransaction
property to No in the right circumstances. When the UseTransaction property
is set to No, the user will never encounter problems associated with too
many lock requests. In addition, the Jet database engine doesn't store query
results in a temporary database, thereby achieving a substantial performance
increase.

If there is a large number of records that must be written to the temporary
database, performance may suffer as a result of running an action query as a
single transaction. In addition, when running delete or update queries in a
shared database, a large number of lock requests are generated. This may
cause performance to suffer and in some Network Operating Systems like
Netware it can cause the query to fail when the lock requests exceed 10,000.

If a transaction can't be completed, an error message appears and you have
the option of saving any changes.

When the UseTransaction property is set to No, the only way to roll back an
entire transaction is to use the ADO RollbackTrans or DAO Rollback method in
Visual Basic.



"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:eJeffz94GHA.3404@xxxxxxxxxxxxxxxxxxxxxxx
Using a query is almost always significantly faster than using code.

What's the query that you're trying to run? Where is the data coming from,
and to where is it being written?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jsccorps" <jsccorps@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D04963B3-9776-43F5-984F-1C3B4134EF52@xxxxxxxxxxxxxxxx
Takes over 5 minutes to run. Would coverting the query to VB to quicker?
There are a total of 300,000 records being processed.




.

Relevant Pages

  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... is possible for others to add other new orders to the database which I will ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)
  • RE: Import external data - web query
    ... Your reply for my query is very extensive, this is for importing a file from ... The data source I want isn't listed in the Select Data Source dialog box. ... information used to connect to a database. ... Check your driver First, make sure you have the right ODBC driver (Open ...
    (microsoft.public.excel.misc)