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.. .
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.
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.
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 😉
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!
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.
no need to worry – the cache is synched – by default every 30 seconds…
Does truncate asks for it’s table keys to be disabled on SQL?
Don’t know for sure what you mean with that – but I don’t do anything special with keys .. so I guess not 😉
But SELECTLATESTVERSION update the NST cache in ALL tables. That do not good idea.
Well, I gave an alternative 😉