a!queryEntity() timeout

Hello Sergeiz,

Multiple thought came to my mind. When you are talking about you are querying a view there are multiple possibilities.

About the view, query and objects:

* I guess your CDT is plain with no nested CDTs?
* does your query entity has the field selection? If not try that, even you can think about a new CDT
* another question does he view makes calculations, like formatting the date? Do you have comparatiosn against strings? Can you make competitions against numbers? Or dates which can simplify the where operations?
* any union in your view?
* Can you take out some calculations?
* in the view are you joining too much tables? Can you simplify the view? Can you use left join?
* Does your tables have proper indexes and primary Keys? If you are joining that is really important
* if you can reduce the amount of information passed to Appian the better because the transformation fromDB to Appian object takes time.
* Do you have any special anottations on the cdt for the view ?

Architecture
*are you on premise ? Or cloud?
*if on premise the DB is on the same network? above some mentioned latency, that is crucial when comes to complex queries. which application server you have? How is the concurrency and the jdbc pool configuration?

The problem just happens in that view?

Let’s start with this

Jose

Hello Sergeiz,

Multiple thought came to my mind. When you are talking about you are querying a view there are multiple possibilities.

About the view, query and objects:

* I guess your CDT is plain with no nested CDTs?
* does your query entity has the field selection? If not try that, even you can think about a new CDT
* another question does he view makes calculations, like formatting the date? Do you have comparatiosn against strings? Can you make competitions against numbers? Or dates which can simplify the where operations?
* any union in your view?
* Can you take out some calculations?
* in the view are you joining too much tables? Can you simplify the view? Can you use left join?
* Does your tables have proper indexes and primary Keys? If you are joining that is really important
* if you can reduce the amount of information passed to Appian the better because the transformation fromDB to Appian object takes time.
* Do you have any special anottations on the cdt for the view ?

Architecture
*are you on premise ? Or cloud?
*if on premise the DB is on the same network? above some mentioned latency, that is crucial when comes to complex queries. which application server you have? How is the concurrency and the jdbc pool configuration?

The problem just happens in that view?

Let’s start with this

Jose

Hi Jose,

The key problem is the compex data structure that is external and cannot be simplified. This leads Oracle to different execution paths. For instance, one query can return all 10k rows in 15 seconds but the next simpler query goes to minutes.

I understand, thinking about external data I always ask the users:
Do you really need the last data or you can work with a data from 5 minutes ago? 1 hour ago? 1 day ago?

And you can do this by replicating the external data to your oracle database to some kind of staging tables. Or execute que query as sugested in a materialized view. or use a staging table and try to query to the external resources just the delta.
If you find a way to sync the deltas that should be faster but requires more reengineering

If they really need real time data Then try to process as much as possible from the database side. stored procedure would be enough but can be resources consuming

Jose

Our external data reside nearby on the same Oracle, but it's external system.

The irony here is that the query that started to fail with timeout recently is a delta pull to our table.

In such case, you can think of re-designing the approach to pull the records from Database utilizing Interface/integration layer of Service. Create a light weight service which will pull the records from Database using PL/QSL based upon input parameters along with paging info (like start index, batchSize) and consume this service from Appian.