Facing issue with query entity

How is the Explain statement looking for your view?

sayalic0004 said:

when i apply filter to one of the calculated column which has 0 rows present, queryentity still throws below error. 

Since its a view, the filter will work after fetching the entire dataset. When you try to fetch 1 row without any filters is it working? If yes, How much time it took to return the row. 

Is the view opening and performing well in database end?

Basis the above answers, first and important step is to optimise the view only. 
If that also doesnt work then better to move to records instead of query entity and utilise Appian's Data Fabric to boost performance. This will need extra effort and care to replace joins with record relationships/ manipulations with custom record fields etc though! 

Views are notoriously known for performance issues. Any reason for not using synced records?

How long does the view itself take to load a standard display-screen of paged data when doing a similar filter?  if it's anything more than, maybe, half a second or so, then the Appian query has no hope of being performant.

Small nuance here - non-materialized views will (generally) still use indices of columns, and filter result sets rather than querying the entire dataset and then filtering. If the view doesn't have complex logic / sub-selects / CTE's, it can have dozens of indexed joins with indices on common filters, and will be very performant when Appian applies a column filter.

If CTE's or "sub"-selects are used in a view, then indices / filters are used sometimes - it really depends on the view's logic. Generally, an outer filter is NOT applied internally to a sub-select, but it's more of a variable scope issue rather than performance issue, and sometimes there is a way to have an outer filter reference the internal variables. If the SQL compiles / runs with an outer filter that references an internal CTE / sub-select variable, then indices / filters will be honored before the dataset is returned. Explain analyze can demonstrate this as well. The bigger issue with CTE's and sub selects - it's not a great practice to rely on references like this since they become difficult to modify without breaking a lot of logic.

If UNION is used in a view - commonly the entire dataset of each union'ed query is returned prior to an outer filter is applied, and indices are only honored internally to each query inside the union - i.e. the Appian filter's column index will be ignored.  Explain analyze will show this as a materialization of the underlying columns inside the union'ed queries.

Materialized (aka "calculated") columns inside of a view cannot be indexed, and thus the entire dataset is queried before a filter is applied. On top of that, it will have to do a 1:1 comparison of text for the OP's particular filter, with no indexing available. An explain analyze output would demonstrate this since there would be a huge amount of time spent after the materialized column creation.

All in all, I'm not sure that this use case can be properly optimized to work within Appian's timeout without pre-calculating the column - i.e. remove the logic from the view.

Ultimately the value of sync'ed records shine here - with an uncommon caveat about data stewardship.

If the view is in a remote data source that another team controls, then the Appian environment also (usually...) has access to the view's underlying tables and thus could sync / manage logic more directly. However, if that other team also controls the logic of the view, then suddenly that logic has to be synchronized across multiple technical teams. It turns into a people / governance problem at that point.

View is used because - 

1. We have many calculated columns to show in view.

2. Data to be fetched from multiple tables

The view evaluates calculated columns on all 20,000 rows before applying filters, causing timeout even for 0-row results. Views often cause performance issues. Since you already mentioned that you tried optimizing your code, I recommend using Record Types with Data Sync.​​​​​​​​​​​​​​​

Sync'ed records support both of these points, and excel at the latter.  Is there anything specific about your view's calculations that cannot be replicated in sync'ed record logic?

If sync'ed records are truly out of the question, then must the calculations be completed 'live', or can the calculations be completed and stored every time the data is changed in specific ways? In my experience it is rare that calculations MUST be live, but rather, the calculations are done live because that is easy, saves development time, and this method doesn't become a problem in most cases. For your case, if the calculations cause query timeouts, (especially at scale), then it's worth investing some time into a little refactoring.

• Process Re-engineering/Non GFT/ITeSS (and other valid values) would get stored into a non-view table (let's call it "Inventory_Calcs" for now) in table's currentReductionStage column
• Every time something relevant to the calculation of currentReductionStage changes for the relevant records, the currentReductionStage for the associated eucInventoryId gets updated to the proper valid value. This can be done with an Appian process (preferred if you control all of the logic), or database function / stored procedure if a non-Appian team controls the logic
• Your current view (whatever backs ELMRISKREDUCTIONLANDINGPAGEVW) would do a simple join to the Inventory_Calcs table and retrieve the pre-calculated value.

If the view must be calculated live for business-related reasons, then does every column need to be calculated every time the view is called? If not, it may be more efficient to split your view up into multiple views and multiple queries in order only perform the calculations when they're needed on the UI or in process. Depending on the use of sub-selects / CTE's in the view, it is possible that swapping from the equivalent of (SELECT * FROM <view>) to (SELECT <columnA ... columnN> FROM <view> ) will not yield any performance since the optimizer can't sus out all of the indirect relationships of the selected columns to CTE's, so it calculates everything to ensure data integrity. This particular case was why I refactored a particularly tough view into sync'ed records last year, fwiw.

Great explanation Jesse! Workings and optimisations - all Depends on how the view is built to begin with.