Delete query takes FOREVER to run 1

[TheresAlwaysAWay]

I have a table that is used to calculate monthly commissions. Once the month is over and data has been saved there's no need for any of the info in the table, so it is deleted entirely awaiting the start of the following month. It couldn't be simpler. All data is removed and it's usually not more than about 10k records or so, yet it takes several minutes to complete.

This is the statement. DELETE RoadRepCommissions.* FROM RoadRepCommissions;

Can someone please assist on this? What's even odder is that if I open the table itself and highlight all the records in it, it still takes just as long to delete. Something's weird and I haven't a clue what.

As always, thanks in advance for any assistance.

 

[Andrzejek]

Looks like:
[tt]
Currentdb().Execute "DELETE FROM RoadRepCommissions;", dbFailOnError[/tt]

Should work pretty fast, according to this place

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

Thanks for this. Unfortunately, the accountant started the commissions for this month today and once she's working on it I can't start deleting data for test. Once she finishes for the month then I'm free to do whatever I want until the next month. She usually takes from somewhere around the 10th of the month to somewhere around the 20th. It's aggravating but there's nothing I can do to test this until she finishes.

If you can, I'd appreciate an explanation of what this code does vs. the original.

Thanks.

 

[TheresAlwaysAWay]

One other question. If I were to use this in another circumstance where there's a WHERE clause, do you know how to include that in this expression format?

 

[Andrzejek]

The two Delete statements are pretty much the same, both should work fast. Unless there is something wrong with your data base. Maybe it needs repairs?

But, having the table for one month, delete all records and start over - is not the best design.
"Once the month is over and data has been saved..." - why not saving the data wherever it needs to be without the RoadRepCommissions table...

As for WHERE clause:

Dim strSQL As String
Dim strLastName As String

strLastName = "Smith"[green]
'Delete all records for Smith[/green]
strSQL = "DELETE FROM RoadRepCommissions WHERE LAST_NAME = '" & strLastName & "'"
Currentdb().Execute strSQL, dbFailOnError

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

Thanks for your help again.

The answer to your question about why the records are deleted from that table is because the corrected, updated info after the accountant is finished is saved in other tables and spreadsheets. It is her working table to edit the records and manipulate them as she needs to. Once that process is complete and the finalized info is stored elsewhere the table is deleted.

You could think of it as a temporary holding table that is used just long enough for her to finalize all commissions.

After hearing my explanation does it still seem weird to you?

 

[Andrzejek]

Yes, kind of...
I would (probably) keep the time stamp on the records and display just the records for the current month, and keep all of it in the table.
But - if that is what the user wants, that's what she gets.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

Like I said, the information holds no value after the end of the month, and that's why it's deleted.

It's not gonna be until after the 20th before I can test this. I'll let you know then.

 

[jedraw]

Have you considered?
- a copy of the database for your testing totally separate from the copy being used by the accountant
- a database /table designed specifically for you to "tune your skills" before working with the "official" database

Either option would allow you to check/revise the performance as necessary.

Perhaps there are other features of your database and/or concurrent users/processes that are making your query painfully slow.

 

[TheresAlwaysAWay]

I've finally had the chance to test this and I run into this error message.

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

Can you provide further guidance?

Thanks.

 

[TheresAlwaysAWay]

I was able to work out the issue with the dbSeeChanges option and test this. It worked well. Thank you for your help.

 

[jedraw]

Glad you have it resolved. Didn't realize you have SQL server BE.