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! 

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.

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.

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