Вернуться   AXForum > Microsoft Dynamics NAV > NAV: Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 30.06.2016, 05:19   #1  
Blog bot is offline
Blog bot
21,901 / 759 (69) +++++++
Регистрация: 28.10.2006
waldo: Delete all records in table with C/AL – Truncate

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.


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 ” 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.. .


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.

Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
Microsoft Dynamics CRM Team Blog: Leveraging bulk delete jobs to manage System Job log records Blog bot Dynamics CRM: Blogs 0 13.11.2008 22:06
wiki.dynamicsbook: Changes Made in Navision Attain 3.60 Blog bot Dynamics CRM: Blogs 0 02.09.2008 13:23
wiki.dynamicsbook: Changes Made in Navision Attain 3.01B Blog bot Dynamics CRM: Blogs 0 21.08.2008 20:43
wiki.dynamicsbook: Changes Made in Navision Attain 301 Blog bot Dynamics CRM: Blogs 0 19.08.2008 13:44
jerry-dynamics: How can you make sure that the end user can not delete a record in one table if there are related records in another table? Blog bot DAX Blogs 0 16.06.2007 11:20
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 01:44.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.