• State Not Answered
  • Replies 16 replies
  • Subscribers 12 subscribers
  • Views 25151 views
  • Users 0 members are here
Related Discussions
Home » Discussions » AI and Rules

Database Query

bryant.st39

We are having issues with querying data from a view in one of our datasources.  When attempting to run the queryEntity, we get the following error.

"Expression evaluation error at function a!queryEntity [line 20]: An error occurred while retrieving the data."

The server log indicates a timeout occurred, however if I run a process model with the Query Database node and a SQL statement, the node returns the correct values in considerably less time than our timeout settings.

Thoughts?

 

Bryant

+++++++++++++++ Code Snippets ++++++++++++++++++

 

Query Entity:

a!queryEntity(
  entity: cons!SMTS_EV_PROJECT_SUMMARY_DSE,
  query: a!query(
    selection: a!querySelection(
      columns: a!queryColumn(
        field: "subprojectId"
      )
    ),
    filter: a!queryFilter(
      field: "subprojectId",
      operator: "=",
      value: 30509
    ),
    pagingInfo: a!pagingInfo(1,1)
  )
)

 

SQL Statement:

select subprojectId, bac from evm.ProjectCurrentBaselineSummary where subprojectId = 30509

 

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer
    Quick questions:
    (1) is the subprojectId a PK or at min have an index
    (2) How many rows will this pull back

    Thanks
  • 0
    Certified Lead Developer
     in reply to ChristineH
    is on the right lines IMO. To add to what she's said, try running your SQL with EXPLAIN in front of it to see what is returned. If you see any full table scans and/or large numbers of rows, you'll need to look at rewriting your view or possibly add indexes where required. FYI - if you run a simple select as you've described and it takes any more than around 0.1 seconds, your view is probably far too slow.
  • 0 in reply to PhilB
    The simple select takes about 0.1 seconds...the database tables we are querying from to build the view were, IMO, designed to optimize DB writes and not reads and as a result read queries perform extremely poorly. This is evident even in the client-server app that uses this data. We may have to take a different approach from using the view if Appian query entity can't/doesn't perform the same as the query DB node in the process model. As of now, we are considering using a process model to perform the simple select, write the data to a "reporting" table and use that to display on the dashboards. The downside of this would be the refresh rate of the data would mean the data is not real-time.
Reply
  • 0 in reply to PhilB
    The simple select takes about 0.1 seconds...the database tables we are querying from to build the view were, IMO, designed to optimize DB writes and not reads and as a result read queries perform extremely poorly. This is evident even in the client-server app that uses this data. We may have to take a different approach from using the view if Appian query entity can't/doesn't perform the same as the query DB node in the process model. As of now, we are considering using a process model to perform the simple select, write the data to a "reporting" table and use that to display on the dashboards. The downside of this would be the refresh rate of the data would mean the data is not real-time.
Children

© 2024 Appian. All rights reserved.