Best way to truncate tables?

Best way to truncate tables?

  

Hi,


I need to empty a table with about 9 million records every 2 weeks.  I tought I could simply truncate the table with a query, but that doesn't work.


What is the most easy and fastest way to clear the records?


Regards,


Raymond



Raymond Lips wrote:

Hi,


I need to empty a table with about 9 million records every 2 weeks.  I tought I could simply truncate the table with a query, but that doesn't work.


What is the most easy and fastest way to clear the records?


Regards,


Raymond



You can create a job in the database.

Regards,

Ib


Hi Raymond,

I've some questions in return, because there can be a lot of reasons why it is not working, but there are also a lot of solutions.

Can you explain what you tried and why is the truncate is not working? 

Do you want to clear from OutSystems and did you try the Advanced Query?

Do you want to clear from the database directly? Are you in the OS Cloud or on premise?

Kind regards,

Remco Dekkinga

Hi Remco,

In sql I'm used to "truncate table <tablename>". I am looking for an alternative in Outsytems. I don't want to do this by stored procedures or jobs directly on the database. Now the only solution I see is a sql query to delete the top x records and to run that over and over, until the table is empty. But deleting records takes a lot of time, while truncate takes a few seconds.

To answer your other questions, we have outsystems installed on Azure, so in the cloud.

Regards,

Raymond

Solution

Hi Raymond,

There are two options:

1. Use Advanced SQL with the following command: DELETE FROM {TABLENAME}, but this is time consuming and will take up a lot of your resources. You might need to cut this process into smaller steps (DELETE TOP xxx FROM {TABLENAME}) within a loop until the table is empty to keep within the boundaries of your resources i.e. Transaction log doesn't grow outside your available database size, causing your environment to become unavailable.

2. Use Advanced SQL with the following command: TRUNCATE TABLE {TABLENAME}, but you need to extend the rights of your user on the database with enough rights to execute TRUNCATE / DROP statements on this specific table. 

Be aware that option 2 has a security risk that data might get lost when you grant TRUNCATE rights on all tables, because TRUNCATE doesn't keep track of changes in the transaction log.

Kind regards,

Remco Dekkinga

Solution

Thanks Remco,

The solution I am looking for aparently doesn't exist. Your 2nd option seems to be the next best thing.

Kind Regards,

Raymond