Simple DELETE takes over 12 minutes

[mahmed1]

Hi All

I am trying to delete all data from a table which is taking over 12 minutes each time (i do have over 500k records) to delete

DELETE * FROM tblRollingData thats my query but it takes forever to run (Can you please help me speed this up)

i also have another DELETE in another table but this takes long but not as long

DELETE * from tbl_tempData WHERE Date_ is not NULL

Please Please help me speed this UP

thank You

 

[Trevor G]

Some questions:

• Have you compacted the database recently
• Is the table related to other tables or other software like SQL
• Would it be easier to just delete the whole table
• Access VBA might help. In a module create a sub routine and add this code: CurrentDb.Execute “DELETE * FROM tblRollingData”

 

[mahmed1]

Hi this table is a temp table that is used to import data into - i then delete out rows that are blank and insert into another table

Nothing else is related
Via VBA - i run this command - DoCmd.RunSQL "DELETE * FROM tblRolling"

Ive read to Drop and recreate - is that the better option?

 

[Trevor G]

If I am reading this correct, you are appending data into another table before emptying this table.

Can you not delete the empty rows before they are imported from another app?

It is easier to delete the whole object.

 

[Joe4]

Do you have a Primary Key field set in your tbl_tempData table?
I believe that might possibly help speed things along.
If you do not already have one set, you can try it and see if that helps any.

 

[mahmed1]

Hiya - no i dont have any primary keys set at the mo - i dont have a unique identifier

 

[Joe4]

Hiya - no i dont have any primary keys set at the mo - i dont have a unique identifier

Just add an Autonumber field at the end of your table, and see if that makes any difference.
Sometimes table performance is better if there is a Primary Key field or Indexed field.

 

[mahmed1]

Would i need to add to end of the table where it pastes too?

 

[mahmed1]

Hiya - just realised that i do have an autonumber and primary key on the ID column

 

[Joe4]

Hiya - just realised that i do have an autonumber and primary key on the ID column

OK, well I was hoping maybe that would help, but obviously not.