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

 

[mahmed1]

whats another way around it - Ive read if you Drop and then create but i dont know - is there also a way of deleting out a read lock file - its saying i cant delete but no one has it open

 

[xenou]

if no one is in the database you can safely delete the lock file. Dropping and recreating a large table should be significantly faster than deleting all the rows in a large table.

 

[mahmed1]

Hi Xenou - Problem im facing is that we dont know whos in the lock file - even though ive checked with all users who have ran it to see if they have it open in the background

Users are constantly updating the database through excel and every so often end up locking the database and its a nightmare trying to delete this lock file if i cant find whose opened it

The drop and create does works faster for sure but lock issue is annoying- hoping You can advise a better way of updating the database

 

[petertenthije]

Maybe you could create a new database specifically to delete the unnecessary rows? Then copy from that database into the live database.

That way you can be sure noone is in the database, which might result in fewer calculations to reset forms, queries etc.

Last point, why important empty rows only to delete them? Can you not import complete lines only?

 

[mahmed1]

There is a text file that gets imported in that always has blank rows hence why i delete out the blank rows

once amended i apend the data on to a rolling table and then delete out all existing data from
This temp table

hope this makes sense

 

[Joe4]

There is a text file that gets imported in that always has blank rows hence why i delete out the blank rows

For your rows that contain data, is there always at least one field that is always populated?
If so, you can make that field Required/Mandatory in your temp table setup.
Then, when you go to import the file, all the blanks rows will error out (because they are missing that Required field).
So you won't have to delete those rows, since they never successfully get imported into your table. Only the good records will get imported.

 

[jimrward]

Try adding an index to the field in your WHERE clause run a few tests comparing times
SELECT * from tbl_tempData WHERE Date_ is not NULL
then index Date_ and repeat the select otherwise it might be doing a whole table sequential scan on Date_

 

[mahmed1]

Thank you

what do you mean by this?

repeat the select otherwise it might be doing a whole table sequential scan on Date_

 

[jimrward]

Do a select with no index on data_ and time it
Then implement an index on data_ and repeat the select and time it compare the times
Without an index the where clause will scan the data table on the column you specify sequentially to resolve the where clause

 

[james_lankford]

is tblRolling a linked table or is it actually in your database ?
is the primary key in tblRolling a foreign key in any other table ?