Slow Performance in Execute Query activity

0
I am using Execute query activity to connect to Oracle DB and fetch records. The query returns 10k records. The result object takes more than 20 seconds to parse the data. I need all the data so can’t reduce the result list.   The only option coming to my mind is to break the result by passing offset and call multiple activities in parallel and then combine the result. Any better suggestions to reduce the time it takes to get the result and give me the result list in less time.
asked
3 answers
2

What are you doing that you need all 10k records? If this is a data sync/migration operation, then 20 seconds seems acceptable. If it’s not a data sync, why do you need so many records? 

 

There are a few things to consider here:

  1. If you’re fetching the data to do some logic, can do you the logic in your query so that the heavy work is offloaded to the database and only the (smaller) result is returned?
  2. If you are indeed doing some sort of data sync/migration, perhaps run this operation in the off hours or asynchronously and alert the user when completed
  3. If you do truly need that many records but you’re NOT doing a migration, then the strategy you’ve mentioned of batching up the calls using limits and offsets makes sense
answered
1

Anything that cannot be fetches in <1 second should not be a blocking action for the user. This means that you either:

* fetch just a few records from oracle, e.g., by providing search expression (as mentioned by connor) or limit the records with offset/limit,

* or you do it in the background: create a microflow that runs once per hour/per day/on user login, that fetches a lot of records and caches them in the mx app database. This way the Mx UI can fetch just a few records from the database.

answered
0

Hi Romil,

 

Reading your question and comments on Conner, I think the approach is not the best way to go foward. The fact that you build an application that might give the avarage user a list of 3k records in a dropdown, is asking for trouble in my opinion. In addition, if the business prefer 3000 records in a dropdown instead of a search function (such as Connor mention) you and the business are facing hard times.

 

So, perhaps you could ask your stakeholderd (or product owner) why they think you need 10k records, put them in a dropdown and do not want to have this pre-filtered by search functionality? Perhaps with their motviation I can help you finding a solution on their request. Just wanting to process 10k records is just a too less info (in my opinion) to find a solution on your perfroamnce issue.

 

hopes this helps,

Jan 

answered