×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Delete query takes FOREVER to run

Delete query takes FOREVER to run

Delete query takes FOREVER to run

(OP)
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.

RE: Delete query takes FOREVER to run

Looks like:

Currentdb().Execute "DELETE FROM RoadRepCommissions;", dbFailOnError


Should work pretty fast, according to this place

---- Andy

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

RE: Delete query takes FOREVER to run

(OP)
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.

RE: Delete query takes FOREVER to run

(OP)
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?

RE: Delete query takes FOREVER to run

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... ponder

As for WHERE clause:

CODE

Dim strSQL As String
Dim strLastName As String

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



---- Andy

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

RE: Delete query takes FOREVER to run

(OP)
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?

RE: Delete query takes FOREVER to run

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. smile

---- Andy

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

RE: Delete query takes FOREVER to run

(OP)
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.

RE: Delete query takes FOREVER to run

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.

RE: Delete query takes FOREVER to run

(OP)
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.

RE: Delete query takes FOREVER to run

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

RE: Delete query takes FOREVER to run

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close