I would like to populate a table on a record view that reads its data from an Or

mohamedb over 8 years ago

I would like to populate a table on a record view that reads its data from an Oracle VIEW. I use the a!queryEntity to query the data. The query works fine when reading from existing tables in DB; but gives the attached error when I apply it on the view.

When I try to query the Oracle View from Oracle developer, I realize it really takes a long time to obtain the results. So, most likely it is a time out issue.

Do you recommend any optimizations to Oracle View to better integrate with Appian?

Discussion posts and replies are publicly visible

0 mohamedb over 8 years ago

The error
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Tim
Certified Lead Developer
over 8 years ago

Check the underlying source tables and consider adding an index to the field used in your query filter. Also examine the explain plan for the query with your DBA.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sikhivahans over 8 years ago

@mohamedb Hi, though the view is taking a long time to execute, in Appian, you are just querying one record(as per the batchSize:1 in your expression). May be it would be better to confirm the same by inspecting the logs before taking an action, as logs will let you know the exact root cause.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sagarl511
A Score Level 2
over 8 years ago

@mohamedb - we faced similar issue where query optimization was required. We added index on tables which were joined in the query. Also we divided the queries into multiple small queries and checked which part of view is slow. Another option we tried was to use stored proc as it limits the data to minimum extent as compared to views.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 8 years ago

We have experienced the same issue as well, adding indexing to the Oracle view solved our performance issues and we could then load the data.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 mohamedb over 8 years ago

I created the incidences on the table columns feeding the view; since we can not create indices on the view itself. I am still getting the same error (attached). I received no problem while creating the CDT that corresponds to the view; so I assume it is working fine. I am attaching both the query and the error I am getting.

Any advice is much appreciated?
for Appian.txt
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 mohamedb over 8 years ago

and the error
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sikhivahans over 8 years ago

@mohamedb If possible, I would suggest attaching the log contents especially the time at which the error has been generated. 'Caused By' in the logs might quickly help us determine the issue at times.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 mohamedb over 8 years ago

Sure, do you have an idea where/how to find it?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 eliav over 7 years ago

Mohamedb we have the same problem did you solve the issue ?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel