Performance of queryEntity on external database

Question

Hi, I'm facing a performance problem on a queryEntity. The query is done on a DSE mapped to a view in an external SQL Server database. When connecting to the DB through a client, a SELECT query with a couple of WHERE conditions takes less than 5 seconds, but when done through the queryEntity, it takes more than 10 seconds and causes a timeout in Hibernate. The environment is in cloud, so the timeout can't be overridden. I'm looking for ways to debug this discrepancy in timings. Here's the code (in reality there are 5 filters). I tried setting fetchTotalCount to false to improve performance, the result does not change. 
 a!queryEntity(
 entity: cons!EXTERNAL_VIEW,
 fetchTotalCount: true,
 query: a!query(
 pagingInfo: topaginginfo(1, - 1),
 logicalExpression: a!queryLogicalExpression(
 operator: "AND",
 ignoreFiltersWithEmptyValues: true(),
 filters: {
 a!queryFilter(
 field: "field1",
 operator: "=",
 value: ri!value1
 ),
 a!queryFilter(
 field: "field2",
 operator: "=",
 value: ri!value2
 )
 }
 )
 )
) 
 
 Thank you.

scristalli · Accepted Answer

I confirm that all records are needed. Even if the retrieve of a single record was faster, that wouldn't reveal much about the root cause of the problem. It seems that the difference between the query executed from Appian and the same SELECT executed manually might be due to the way Appian uses Hibernate to map objects, possibly resulting in a prepared statement that differs from a plain SELECT and causes problems in the execution plan (due to the way the view is structured). We are going to explore this road with the DB administrators by tracing the queries, as I don't see any other plausible explanations for this problem.

Chris · Answer

I agree with reviewing this situation with the DBA's. There could very well be a difference in the actual query executed, but that is nothing we would be able to modify in /design, and not an issue I've ran into with my experience (as far as major performance decreases within Appian vs the DB directly). Likely some performance tuning such as indexing on the view would assist here.

Chris · Answer

It sounds here like the view performance is not great in the external DB - "under 5 seconds" through SSMS is not what I would call phenomenal performance in SQL. Aside from view performance tuning (indexing, etc), there isn't too much that can be done in the interface besides turning off fetchTotalCount, and adjusting your pagingInfo. Do you need to return all records at once? How is performance when testing with a!pagingInfo(1,1)?

Performance of queryEntity on external database

Top Replies