a!queryEntity() timeout

Thanks Alok, your list looks quite comprehensive.

I tried all of that apart materialized view that was in my plan.

Unfortunately, our database structure is pretty complex and Oracle sometimes runs the same query with different execution plan and times long.

As data volume would continue to grow I started to think to organize some staging table with all needed data filtered by a stored procedure call without so strict timeout.

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.

Hi there,

queryEntity indeed does two calls to DB. First to get the total number of lines returned and second to get the actual data. But if you use "-1" as batchsize Appian skips the first call and only a single call to DB is needed. We added this to our internal best practices for the cases where the number of returned lines is fixed or very small. Depending on the overhead and network latency of the DB connection this speeds up DB calls quite a bit.