G
Guest
Takes over 5 minutes to run. Would coverting the query to VB to quicker?
There are a total of 300,000 records being processed.
There are a total of 300,000 records being processed.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Douglas J. Steele
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?
What's the query that you're trying to run? Where is the data coming from,
and to where is it being written?
J
John Spencer
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.
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.
G
Guest
Have a sequence of 6 queries. The 6th query does the append. Data is coming
from several tables (customer data, manager data, product data), and is being
appended to a clean/empty table.
For clarification, the total database is 300,000 records. The query filter
on a date range and a particular manager, so the append may be 10 records or
thousands of records (e.g., one day or a month of data for a particular
manager).
from several tables (customer data, manager data, product data), and is being
appended to a clean/empty table.
For clarification, the total database is 300,000 records. The query filter
on a date range and a particular manager, so the append may be 10 records or
thousands of records (e.g., one day or a month of data for a particular
manager).
G
Guest
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)
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)
D
Douglas J. Steele
I think John may have the best suggestion.