• State Verified Answer
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 9 subscribers
  • Views 1895 views
  • Users 0 members are here
Related Discussions
Home » Discussions » General

Performance of queryEntity on external database

scristalli
A Score Level 1

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.

  Discussion posts and replies are publicly visible

  • 0

    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)?

  • +1
    A Score Level 1
     in reply to Chris

    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.

  • +1 in reply to scristalli

    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.

© 2024 Appian. All rights reserved.