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.

4.00 avg. rating (83% score) - 3 votes

Permanent link to this article: https://www.waldo.be/2016/06/30/delete-all-records-in-table-with-cal-truncate/

8 comments

Skip to comment form

    • Steven on June 30, 2016 at 7:14 am
    • Reply

    I love TRUNCATE 😉
    One thing to be careful about is that you can’t (or might not be able to) use it on a table that participates in an indexed view or in a replication. Also, sql triggers in that table are not fired, since the row deletions are not individually logged. But anyway, sql-triggers on NAV tables are, in my opinion, not recommended 😉

      • waldo on June 30, 2016 at 7:17 am
        Author

      Absolutely true .. if one uses SQL triggers – he/she should cut his/her fingers ;-).
      Very valid point on the replication and indexed view though! Thanks!

    • Peter on June 30, 2016 at 8:48 am
    • Reply

    Regardless the previous hint by Steven:
    Does SELECTLATESTVERSION update the NST cache in all NSTs or only in that one I’m connected to? If not that might be another issue to be careful.

      • waldo on June 30, 2016 at 9:10 am
        Author

      no need to worry – the cache is synched – by default every 30 seconds…

  1. Does truncate asks for it’s table keys to be disabled on SQL?

      • waldo on July 4, 2016 at 9:31 am
        Author

      Don’t know for sure what you mean with that – but I don’t do anything special with keys .. so I guess not 😉

    • GLogrus on August 5, 2016 at 9:16 am
    • Reply

    But SELECTLATESTVERSION update the NST cache in ALL tables. That do not good idea.

      • waldo on August 5, 2016 at 12:44 pm
        Author

      Well, I gave an alternative 😉

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.