Solved Delete Query

[MattBaldry]

Hi All,

I have a query to delete certain records from a link SQL table, but there are a lot (over 300k). Is there any way of creating a query to loop delete 100 records at a time so it doesn't get hung up trying to delete them all in one go?

~Matt

 

[ebs17]

This is unusual and there is no easy way to do it. It is the hallmark and strength of SQL to do everything at once and very quickly.

Your problem is probably not the deletion itself, but the determination of the certain records - ineffective formulation of the query, no use of the index.
Please show the SQL statement.

 

[arnelgp]

you can try, but maybe too slow:

Delete [yourTable].* From [yourTable] Where [PKField] In (SELECT TOP 100 [PKField] From [yourTable)

 

[MattBaldry]

This is unusual and there is no easy way to do it. It is the hallmark and strength of SQL to do everything at once and very quickly.

Your problem is probably not the deletion itself, but the determination of the certain records - ineffective formulation of the query, no use of the index.
Please show the SQL statement.

Hi ebs17,

Please see code below.

DELETE dbo_StockRecordAuditLog.StockRecordAuditLogID, dbo_StockRecordAuditLog.StockRecordID, dbo_StockRecordAuditLog.Username, dbo_StockRecordAuditLog.Action, dbo_StockRecordAuditLog.ActionDateTime
FROM dbo_StockRecordAuditLog
WHERE (((dbo_StockRecordAuditLog.Action) Like "Product Image Updated*" Or (dbo_StockRecordAuditLog.Action) Like "Label Printing*" Or (dbo_StockRecordAuditLog.Action) Like "Web Image Updated*" Or (dbo_StockRecordAuditLog.Action) Like "Aggregate Demand Days*" Or (dbo_StockRecordAuditLog.Action) Like "Replenishment Horizon Days*"));

We have 3rd party MRP software that has a bug and it creates these 3 audit log entries for every stock code, every day. The software company have it in their next bug release, but it is not due out for a long time.

Is there a way of tidying up the SQL I have generated?

~Matt

 

[MattBaldry]

you can try, but maybe too slow:

Delete [yourTable].* From [yourTable] Where [PKField] In (SELECT TOP 100 [PKField] From [yourTable)

I am doing this at the moment, but it is still slow, and with this may records I need to run the query so many times, which is why I was hoping I could get Access to loop it for me. I could then set it on another machine and let it loose for me.

~Matt

 

[arnelgp]

you can put a Stored procedure in you SQL db that will do the deletion? If you can, you can call it from MSA.

 

[Minty]

Use a passthrough query and sent the delete directly to the server, it will be quicker.

Edit: It also might be quicker to only send one of the like statements at a time ?

 

[ebs17]

WHERE
   ((
      (dbo_StockRecordAuditLog.Action) Like "Product Image Updated*"
         OR
      (dbo_StockRecordAuditLog.Action) Like "Label Printing*"
         OR
      (dbo_StockRecordAuditLog.Action) Like "Web Image Updated*"
         OR
      (dbo_StockRecordAuditLog.Action) Like "Aggregate Demand Days*"
         OR
      (dbo_StockRecordAuditLog.Action) Like "Replenishment Horizon Days*"
   ))

The Action field is indexed? Then it should be possible to use the index up to the wildcard (*).

If you have influence on structures: The Action field “screams” for another normalization step.
Something like this would look much better:

WHERE dbo_StockRecordAuditLog.ActionID IN (2, 4, 5, 7, 9)

It might also help if the % character is used as a wildcard when switching to SQL Server-compatible syntax (ANSI 92) in the options.

 

[Pat Hartman]

I agree with Minty. I would use a pass through query to do bulk deletes and bulk updates also if possible. When you run action queries against SQL Server BEs, Access encloses them in a transaction. How can you tell? Because Access asks you "Do you want to delete x records?" How did it know how many records would be deleted if it hadn't already processed the delete?

Once you switch to the pass through query, you will no longer get the prompt. The delete will just happen.

I also question your schema and logic if you are deleting 300k records at a time. If that represents the entire contents of a table, it is far better to use DROP than DELETE. DROP is almost instantaneous. DELETE has a lot more work to do and so takes much longer for large quantities of rows.

 

[MattBaldry]

I agree with Minty. I would use a pass through query to do bulk deletes and bulk updates also if possible. When you run action queries against SQL Server BEs, Access encloses them in a transaction. How can you tell? Because Access asks you "Do you want to delete x records?" How did it know how many records would be deleted if it hadn't already processed the delete?

Once you switch to the pass through query, you will no longer get the prompt. The delete will just happen.

I also question your schema and logic if you are deleting 300k records at a time. If that represents the entire contents of a table, it is far better to use DROP than DELETE. DROP is almost instantaneous. DELETE has a lot more work to do and so takes much longer for large quantities of rows.

Hi Pat,

Sadly this not my table, I can access it to see the issues, but cannot change anything. Basically it adds 3 lines to the audit log for every product each time someone opens any record. This is a bug in the software, nothing that I have done. The 3 lines that get added are not needed at all. They just say, basically, fieldname "no changes". But do it all the time. If I can delete them every day, there would only be a few thousand to delete. It is only so large as I have left it so long hoping they would issue a fix.

~Matt

 

[Pat Hartman]

I don't think I said that you should change anything. OK - you still should be using a pass through query for bulk deletes if the BE is SQL Server.

 

[Minty]

If you can delete the records from Access, then you can pass a query through to delete them directly on the server.
A delete on the server will still record a transaction log for each record, but as it's internally handling that it should be significantly quicker.

Reading between the lines, I doubt you can drop the table as you need some of the records.

 

[Pat Hartman]

A delete on the server will still record a transaction log for each record, but as it's internally handling that it should be significantly quicker.

Logging the deletes is not the same as encasing the action in a transaction. Putting an update action inside a transaction allows you to back out or apply the changes once all the changes are made in memory.

 

[MattBaldry]

I can close this now. Managed to get the 3rd party to login and do the delete for me, so all good.

~Matt

 

[Pat Hartman]

So, you never bothered to test the pass through query?

 

[MattBaldry]

So, you never bothered to test the pass through query?

Hi Pat

I didn't get the chance. I will give it a go though as their bug is still creating the records. Just working on something else at the moment with a tight deadline.

~Matt