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

John

I checked the query property adn, from tne beginning, the Use Transaction
property has been set to no.

(anyhow, thanks for the insight into transactions)

"John Spencer" wrote:

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: Combo box bound field change
    ... Modify the row source to include both fields. ... Make the bound column the company name column. ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ...
    (microsoft.public.access.forms)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • RE: Combo box bound field change
    ... You do want the companynbr field rather than the company ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ... I'd run a report to show what all that company purchased. ...
    (microsoft.public.access.forms)
  • 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: Combo box bound field change
    ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ... Modify the New transaction table and change the company name field to ... I'd run a report to show what all that company purchased. ...
    (microsoft.public.access.forms)