Advanced SQL timeout question

Good morning all.   I'm having an issue with Advanced Query timeouts.   The Siebel query shown does a union from 3 different sets of tables, digging through a few hundred million records.   We allow the users to query for Values for a date span and then take the results, convert to binary and make them available for download when it has completed.     


This all normally works great, the problem I'm having is that if one of the 3 table groups is having an issue the timeout setting gets ignored.   I believe what is happening is that the query runs but only fetches a bit of data to see if there are records available, then when I get to the step to convert the data to binary it tries to fetch ALL of the data and will essentially run forever, ignoring the 5 minute timeout.   Is there some way to make the initial query actually fetch all of the records before moving on in the flow?


The problem presented is a little confusing (in my view). There is a very good article on timeouts! 

https://www.outsystems.com/forums/discussion/15702/tip-performance-of-query-in-outsystems-platform-slower-than-running-in-database/

I hope it is useful.

Hi Josh,

Try use with(nolock) in yours query, and list duplicate, example:


Regards.


This is an Oracle Database query.   NOLOCK isn't available in Oracle is it?     When I manually run my query in TOAD I see the same issue, TOAD incrementally fetches by sets of 500 rows and when it hits the 3rd union it totally dies.   I'm working with the DBA to correct that issue but I would also like to find a way to make Outsystems actually time out when this type of issue occurs in the future.

The document was very useful, but I couldn't find any way to resolve the timeout issue with my particular query.   I opened a support ticket to see if this unions may just be a flaw in the logic.