SQL query independently executes in 1 sec. but with OutSystems getting time out error

Hi Team,

I am facing issue with my SQL Query. The Query is executing with in 1 sec. for the 14500 records without any error on MS SQL Server but When I used the same SQL Query in the Advanced SQL in OutSystems  , I am getting the execution timeout error after 30 sec. with below error message is showing in service center  :

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

Hi Shailendra,

I faced this issue a couple of times and there's no instant solution for that.

Contact OutSystems support providing as much as information you can.

Regards

Hi Shailendra,

Do you get the timeout in the running application or by using 'test query' in your Advanced SQL.

And are there any othere non-outsystems connection to the database that e.g. access your table and locked the table? Queries on the OutSystems database should be executed using the with(nolock) option, so that your tables won't be locked for outsystems.

regards Hans

Hi Hans,

I am getting this timeout error in running application and there is no other non-OutSystems connection available to access the OutSystems database.  

Shailendra, is there any subquery, CTE, Outer Apply?

That's exactly the same pattern I had.

If you can rebuild your query using temp tables instead, you may solve the problem.


Thanks Jose

If it takes 1 second in SQL Server then it should not take that much more from OutSystems.

Are there any network issues possible between OutSystems and the database ?

It's true, it shouldn't take more through OS but the reality is different.

I don't think there is a network issue because you probably have other queries working fine and only that specific query is timing out. It happened the same to me. 

There's somehow some limitation when using CTE or Subquery that slows down something but in my case the workaround with the temp tables fixed it.

I'm curious about if there are differences in the Execution Plans for running it onto the database itself and running it from OutSystems.

It could be the memory allocated to the session that affects the execution plan.

But well.. that is just to figure out the reason, a temp table might work. 

mvp_badge
MVP

Hi Shailendra,

Are you able to share your project, or a sample project that experiences the same issue?  Or can you share your query?  I'm curious to determine what's happening.

Sorry Greg I won't be able to share project or query as because things are on clients production environment.

mvp_badge
MVP

No problem, I understand.  If there's a way you could obfuscate your query, such as changing the column and table names to be generic (col1, col2, table1, table2 etc. ) that would be helpful.  Without seeing the structure of your query I won't have much to go on in terms of troubleshooting the issue or trying to reproduce the problem.

mvp_badge
MVP

Hi Shailandra,

This database where you are executing the query is an OutSystems database or external database?

If it is external,  are you connecting to the MS SQL locally to execute query directly there (which is returning data in 1 second) or through remote machine?

@Shailendra Shrivastava,

Do take a look this post. Even though no post was tagged as the 'Solution', it maybe worth a read as there are several tips from community members that might be of interest to you.

Regards,

AJ

Shailendra, have you been able to re-create the query using temp tables?

Jose, I didn't try this but I tried to replicate the same issue in lower environments (Dev/QA) and the query is working fine there and didn't get any error. If the issue in query so query performance should be same on all the environments with large data but query is working fine in lower environments and on Prod we are getting timeout error. 

I also had problems only in PROD as well, DEV/QA worked fine.

The query performance and its execution plan varies from env to env. It depends on a lot of factors like the version installed, features of the server, etc etc

The issue seems to be fixed and we have verified this on PROD. However, we did not do any changes from our end yet so wondering how this get resolved. I will check with Infra , DB  team to see if any changes made at their end.

mvp_badge
MVP

Hi Shailendra,

Since you are using SQL Server Management Studio, is this an on-premise database you have admin access to? If so, you could try a DBCC FREEPROCCACHE to see if it is a botched query plan causing the troubles. Also, you could start the profiler, and track down what query the Platform actual sends to the database.

In general, the main difference between what the Platform sends and what you execute in SQL MS is that the Platform encapsulates the query inside a sp_executesql while SQL MS executes the query directly. In the past I've seen that this indeed sometimes causes a huge difference in execution times, because of wrong query plans. There's no single solution for this unfortunately. Sometimes it may help to play around with OPTION OPTIMIZE etc., but there's not guarantee it'll work.

Note all this is not caused by OutSystems, but by SQL Server itself, that sometimes uses very bad query plans.

Hi Kilian,

I have captured the query through profiler and the query is correct and it is executing within one sec. on the database but It was taking time on server. We have checked the server utilization at the time of the query timeouts  and we have observed that read I/o has increased more that double in the past few days which has resulted in the query taking longer and getting timed out after 30s and since yesterday, This issue has been resolved without making any changes.


mvp_badge
MVP

Ok, cool, a server load problem then, most likely. Glad to see it resolved itself :).

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