92
Views
5
Comments
Unable to delete large amount of data using SQL query
Question

Hi,

When I tried to delete a large amount of data (more than 1800 man records) using an SQL query, it timed out.


Even if  I use the method below, it timed out. 

・Use TOP to reduce the number of records deleted at once. 

・Use WHERE clause to reduce the number of records deleted at once.

 


Is there any solution?

UserImage.jpg
Muhammad Khoir Al Alim Manurung

Hi @Hazuki Ishikawa,

You can increase the server request timeout in the interface property. 

You can also use the delete syntax as well like this:

DELETE {Log_Extension_bk};

to delete amount of data.

Thanks 

2025-01-09 14-56-57
IQ78

My 2 cents for time out matters either:

1. increase the server request time out in the module or action

2. use sql advance delete from*

3. use timer best practice 

4. use backend tools (like sql dashboard, toad, etc) 

regards 

UserImage.jpg
Nani

Instead of increasing module server request timeout.
You can individually increase each server request timeout.

2024-02-16 07-43-18
Amit Verma

Hi @Hazuki Ishikawa ,

Please read the article linked below; it provides an easy method to complete this task according to OutSystems best practices. 

https://medium.com/@nuno.felix.fernandes/outsystems-best-practices-data-purge-timer-93c5efdb78ac

Let me know if you need further help :)

Thanks,

Amit


2024-07-05 14-16-55
Daniël Kuhlmann
 
MVP

Hi,

For the where clause to be efficient the attributes need to be indexed properly, or else you get a full table scan resulting in the time out.

Try filter only on indexed columns in combination with TOP, use trail and error approach to find the max number of records that don't result in a time out.

Regards,

Daniel

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.