Delete all records in table with C/AL – Truncate

Edited: added a “warnings” section below

Did you ever have to empty a certain table entirely in NAV? Well, I admit, you don’t need to do this too often. And definitely not in a repetitive way.

Well, I came across a situation where I needed to delete all millions of records from a table every single day. So I didn’t only need a way to fill the table with millions of records in a fast way (that’s a whole other story), but I also needed a fast way to remove all the records.

Don’t use DELETEALL

First thing you think about while using C/AL is obviously the DELETEALL statement, which is generating (if you’re lucky) a “DELETE FROM table” in SQL. The outcome is slow, and it’s locking the entire table all the time.. . You don’t want this – believe you me .. . Definitely not for something repetitive, for a process that needs to be AFAP (as fast as possible ;-)).

When you start googling it, you can find that it’s advisable to not delete all records in one transaction – to not lock all the time.

In fact, you don’t have to search long to find another way: by using the “TRUNCATE <table>” statement (which removes all records without adding anything to the transaction log – be aware of this!).

Hey, altering data in SQL by not using C/AL code is not done because of the NST cache, dude

That’s a problem indeed. If I would call the TRUNCATE-statement in C/AL by simply call the statement with .Net Interop, then my NST Cache will not be notified of any transaction, which means: possibly, the content of that truncated table is still in cache. If I would read the content of the truncated table, I would still get the cached records – while in reality on SQL server they are gone.

You don’t want this! Your NST cache should be up-to-date at all time.

So, how can I delete all records very fast, and still have an updated NST cache?

Well, first, I just call the truncate, by using .Net Interop. Here is part of the code:

As you see, a simple SQLConnection and SQLCommand to execute the “truncate table” statement.

To update the NST cache, I tried two options which all seemed to work just fine. I first liked this one:

Which basically meant: try to delete all the records. Since the table is empty after the truncate, this statement will be fast. The only disadvantage which I didn’t test out is that I didn’t know how it would react when I would try this on a table which has an activated “OnDatabaseDelete” trigger. By default, it would generate a delete statement per record (to be able to call the logic OnDatabaseDelete) – but if the NST would use cache for it, I would be .. screwed.

That’s why I decided to go for this one:

Which basically forces the NST to a new read on SQL. This seemed to work as well, and I’m at least sure it is not going to start any hidden loops.. .

Warnings

It’s may be a good idea to summarize some warning for using this:

• first of all, all SQL statements you execute through .Net Interop are “out of your transaction”. It’s a separate transaction which will not be rolled back when something goes wrong further in your internal NAV transaction.
• There is some confusion on whether the TRUNCATE statement is logged or not.  Erik Hougaert (MVP) sent me this link through twitter which indicates that all is fine: http://sqlperformance.com/2013/05/sql-performance/drop-truncate-log-myth .
• You need to manage your NST cache – as mentioned above.

Disclaimer

Well, use at your own risk, obviously. And know the risks!

Personally, I’m doing this on a table which I can always rebuild with business logic, based on other tables – it’s some kind of “rainbow table” which I use to speed up searches – as said: I can rebuild it if necessary. So if it’s not part of any transaction log – I don’t really care. The rebuild should be as fast as possible.