«

»

Print this Post

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.

5.00 avg. rating (97% score) - 2 votes

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

8 comments

Skip to comment form

  1. Steven

    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 πŸ˜‰

    1. waldo

      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!

  2. Peter

    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.

    1. waldo

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

  3. kahced

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

    1. waldo

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

  4. GLogrus

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

    1. waldo

      Well, I gave an alternative πŸ˜‰

Leave a Reply

%d bloggers like this: