Does anyone know why

[sportsguy]

I have an Access database delete query going against a linked table on the same pc, and I am trying to delete about 2 million rows, and when i look at windows task manager, access gets only about 1 - 2 % cpu time every couple of seconds. . . .

i defragged my disk , but i only have 11g of 60 gs available, and the disk is not optimized, (not much contiguous free space)

is this the issue?

 

[DCrake]

Why are deleting so many records in one fail swoop? Access is trying to flag all the records in memory so that at the end of the process it can ask you if you want to delete the records. So if you say no it can deflag them.

If you can do them in batches and remove any Access Warnings.

 

[sportsguy]

doesn't matter why i want to delete so many records, they have to go. .

putting it in a macro and setting warnings would be faster, i agree.

However, wouldn't access get 50% of the cpu (1 of the cores) instead of just 1% every couple of seconds?

i get 50% utilization on certain append queries. . . . without the macro setting. .

sportsguy

 

[accessfever]

your pc might need more memory to process the query?

 

[SOS]

Well, actually Windows is managing the different tasks and the default is set to normal priority so it works when the cpu can do it. If you want it to work more on that task, open up task manager, go to the MSAccess.exe process and right click and under PRIORITY set the priority to HIGH. See if that helps.

 

[georgedwilkinson]

Is there a where clause or join in your "query"?

What happens when you do the delete from the back-end? Is the back-end Access or something else (SQL Server, MySQL, etc.)? If it's something else, you might consider doing a "truncate" instead of a "delete" (if there is no join or where clause).

Is this a one-time delete or will it be happening on a regular basis? If on a regular basis, it might be faster to delete the table and re-create/relink it (for instance if you are doing a refresh from a data-warehouse).

 

[georgedwilkinson]

Here's something else interesting:

Lock promotion

When a SQL statement is executed or when a transaction is executed that modifies a large number of records in a table, Write locks will be placed on all corresponding index and pages in the database. Although the placing of discrete locks maximizes concurrency, it can significantly decrease performance because of the overhead involved in setting and maintaining the locks. This is particularly true when the database is on a server and is being accessed over a local area network.

Capability has been added that permits a user to open a table exclusively and modify records in a table without locks being placed on either corresponding index or pages. This reduces concurrency (only one user is being permitted to update the table), but will increase the performance where large numbers of records are being modified. As an option to requiring a user to programmatically request exclusive access to a table for performing updates, Microsoft Jet will, when large numbers of page locks are being placed on a table, attempt to promote the page locks to an exclusive table lock. Whether or not this capability is turned on is controlled by a registry entry, PagesLockedToTableLock. The default value for the registry entry is 0, which disables the capability. A value greater than 0 specifies the page lock count at which promotion to an exclusive table lock should be attempted. For example, if the PagesLockedToTableLock entry is set to a value of 50, then on the 51st page lock, Microsoft Jet will try to promote the user's shared table read locks to an exclusive table lock. If the attempted promotion is unsuccessful, it will retry on the 101st page lock, and so on.

http://support.microsoft.com/kb/275561/en