how to over come the Timeout Error ?

I am getting below exception while updating one of the records in the database..my database contains more than 2000 records.

exception:

Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

please help me to overcome this error

Hi santhu MS,

Please refer to the attached screenshot.

You can increase manually the server request timeout.

Hope this helps!
Thanks

Hi santhu,

I don't know what is the case, but if it's bulk operations, use SQL statements and not aggregates (bulk operations are faster with SQL statements).

I think that you need to evaluate if it makes sense to you run this action asynchronously. You can create a timer, wake up that timer in your current action and that timer runs the logic that you need. In that logic you can define a variable to set a local timer and, every bunch of data that you update, verify if that timer as expired (define it with lower time than the server timeout). If that timer as expired, recall that action waking up that timer again and ending the actual one. Remember that you need to mark what was treated was what was not (you don't want to update the same data that was already updated).

Hope this can help.

Best regards,

Ricardo

You need a batch processing timer. Each execution for example process 500 records and if there's more records wake the timer.

Other option is checking timer elapsed time, when is almost timeout you wake the timer.

Viraj Kataria wrote:

hi Viraj Kataria

is it applicable for webAplication  iam using 10.0.9 version

thank you 

santhu

Ricardo Pereira wrote:

hi Ricardo

yes it is bulk operation only for that i used sql but it is taking more time (greater than 60 sec).it will affect the performance of the application.

thank you

santhu

Hello Santhu,

Follow Nelson advice, please.

If the action is triggered by the user, save the raw data (or excel or whatever) to a table and then wake a timer that will take care of the processing.

In the timer logic, you do as it was suggested. Check every X records (or even after each record) if a limit threshold (based on the timer timeout) was triggered. In that case, you wake the timer again (from the logic) and ends the current execution. You just need to guarantee that processed records are deleted or marked as so, to avoid processing again records already processed.

This way, the timer will keep going on while there are records and if the timeout is about to be reached, it (the timer) will restart itself, zeroing the timeout.

Cheers

santhu MS wrote:

You can change : 

You can use set request timeout - ( HTTPRequestHandler )

If you use a SqlQuery, you can place a higher value for timeout.

Hope i could help.

Best regards

Pedro Vila Nova wrote:

hi Pedro 

now i used sql, for sql it is working fine but it is taking more than 70 sec to update a database.how to over come this .

and where can i use the setRequestTimeout in my Api.

thanks 

santhu

Eduardo Jauch wrote:

hi Eduardo

 i got the point but if i use this  it might take more time to than usual.(now it is taking 70 sec with sql query update)

thanks 

santhu

Hi Santhu,

You are updating a lot of records. You didn't specify (or I didn't see it) if you are doing the exact same thing on all the records (update same fields to the same values), using a single UPDATE, or if you are updating different fields and values, that will require a single update per record (looping).

If it is the first case, it is strange that it takes so long.

If you are doing a "looping" to update the database, even using SQL will take time, as every update will be a request to the database. In that case, more records to processing will be converted in more time. So, if the number of records to processing is not known or may increase in time, you will have a problem and will not be able to solve directly from the interface.

I would opt by start a batch process (timer) and use the given solution with timers, as this will solve the uncertainty of the number of records to process, and the user will be able to keep working. After the records are processed you can alert the user, for example, with an email, or change a message somewhere to let the user know the records were processed.

There are no miracles here.

In any case, 200 hundred records should be updated VERY fast, almost instantly. So, I don't think the UPDATE itself is the problem, but the looping, the processing code itself, and maybe the upload time if the records are being uploaded through a file to the server.

In any case, I don't recommend changing the Timeouts without much ponderation on the effects and knowing that this will not solve the time being consumed to execute the action.

Cheers. 

santhu MS wrote:

You can set the requestTimeOut before your query.

Other solution on configuration Tools you can change your timeouts.

Hope I could Help.

Eduardo Jauch wrote:

Hi Eduardo

I am updating only one record at a time with some fields are same value and some fields are user values.

and one more thing i am not  using  loops in my API.

thanks 

santhu

Santhu,

Where is data coming from? A form in the screen or a file being uploaded? Or is a REST/SOAP service you are exposing? What are you doing?

70 seconds to UPDATE a single record is something that is not acceptable, unless you are trying to fetch data from external sources, in the action that updates the record, that is causing the delay.

Another possibility is if the record or the table are locked. This could also cause the timeout...

Cheers.

Nelson Inácio wrote:

Hi Nelson 

i didn't use timers till now i don't know how to  use in Application can you please elaborate using screenshots.

thank you

Santhu

Hi santhu,

About timers I think you can take a look at this:

https://www.outsystems.com/learn/lesson/1749/heavy-timers/

And this one (the last part of the video):

https://www.outsystems.com/learn/lesson/1133/master-class-on-best-practices-and-timers/

Hope this can help.

Regards,

Ricardo

Eduardo Jauch wrote:

"Another possibility is if the record or the table are locked. This could also cause the timeout..."

Probably this is the issue... it's the only way to take so much, even by webservice, it's not normal take 70 secs to update a record. 

Pedro Vila Nova wrote:

Hi Pedro

sorry for late replay.

how to over come that.

thanks 

Santhu

santhu MS wrote:

I attached a photo, from the ODC in Portugal, for the best practiced for how to build a timer.

santhu MS wrote:

If you can go to your SQL Server, try run your query with "WITH NOLOCK" . To try if you get timeout .

Best Regards

Pedro Vila Nova wrote:

Hi Pedro

i tried it is throwing an error as Error executing query.

thanks 

Santhu

Read about no lock.

https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

An update with (nolock) is impossible I think :-) I hope so at least .. with (nolock) is meant for select statements .. 

Best option is to look in management studio of sql server who has locks on the table. If it's a page or row lock is also important. If someone else is updating records in a transaction and the default is set to page locking (which is much quicker for multiple updates in a transaction) then he might be locking a page in which your data is kept also. Easy way to fix is to set the table page-lock option off. If you are locking yourself (also an option) go back to the drawing board, carefully check what you are doing. If you are updating multiple things in one transaction it's always possible you accidentally are locking yourself.. 

regards,

Wim

Wim van den Brink wrote:

Hi Wim

yes i tried using with nolock  in update sql query it is throwing an error as Error executing query,

iam using sql query to update the particular row  of  data base in API ,it is taking 80 secs to update 

can you please give me some suggestion

thanks

Santhu

60 secs for 2k rows is way too much. What does the update script looks like ? Are you updating using a indexed identifier or on an unindexed field ? If you are updating on basis of an identifier (update xx set field = 'newvalue' where id = 10)  is much quicker (if id is indexed) as update xx set field = 'bla' where name = 'wim' if there is no index on the name field.

If it's taking soo long you can see in sql management studio the status of your update and were it's waiting for .. 

I think (been a while since I done ms sql) that the status can be waitlock, then it's waiting for a lock on the row or page

Also, when you are updating an indexed field, or even the identifier field (definitely a no-no..) then updating the index can take a while, but still with 2k rows it's shouldn't take so long, I have worked with table with millions or even billions of rows and sql would still be very fast.. 

regards,

Wim

Wim van den Brink wrote:

Hi wim

sorry for late replay.

update query look like below

UPDATE {StDetails}
SET{StDetails}.[name]=@Name,{StDetails}.[age]=@Age,{StDetails}.[address]=@Address,{StDetails}.[joinDate]=@JoinDate  where {StDetails}.[Id] = @Id  and {StDetails}.[name]=@Name;

here Id is identifier ...even i set name as Index

Thanks

Santhu

Hello Santhu,

Hou don't need to compare against the name if you're using ID. Identifiers are unique and are able to guarantee that only the correct record will be updated.

If removing this comparison does not solve your problem, you need to investigate possible locks on the record being updated. This can be caused by the logic itself.

Cheers

Like Eduardo says, if you already have id as identifier, adding the name is useless. Also this can be done using the normal update action flow on a entity .. just get the record, assign using the blue assign thing and use the update action on the entity, no use for the direct sql here .. 

I feel like you are doing things to complicated, 2000 rows in a table is nothing for a good sql server. Maybe you are updating the same row from different processes in different threads, this could cause deadlocks and slow performance. 

Regards,

Wim

Eduardo Jauch wrote:

Hi Eduardo

even if i use only Id in "where" condition it is taking same time to update the database

Thanks

Santhu

Wim van den Brink wrote:

Hi Wim

I tried this one before Using Sql query But Updating Using inbuilt Action throwing Time out error.

Thanks 

Santhu

Then i'm pretty sure you are facing a lock from another process or your own, did you try debugging ? 

Find all the places where you are touching this entity and set a break point on them. Then start the debugger and wait. If nothing happens then start doing something where the result would be the update of your entity. Maybe you have a timer that runs too often forcing updates on your entity, or maybe if multiple different timers ? 

regards

Wim.

Wim van den Brink wrote:

Hi Wim

I Did'nt use Any timers and processes. Now I used Debugger it stops at where i wrote sql query at some amount of time(68 sec) after executing sql it continuing with further Actions.

Thanks 

Santhu

it seems to be a lock, do you have an open connection to the database in which you lock the table? Or perhaps in Service Studio? And is the DB Read Committed? 

Another reason might be, but I don't think it is, that you pass @Id = NullIdentifier and that your attribute is not mandatory. But in that case, there almost should be another update/delete action active. . 

good luck. Of course you can increase the timeout of the query itself.