• State Suggested Answer
  • +2 person also asked this people also asked this
  • Replies 16 replies
  • Answers 1 answer
  • Subscribers 11 subscribers
  • Views 15132 views
  • Users 0 members are here
Related Discussions
Home » Discussions » General

a!queryEntity() timeout

sergeiz

Hello!

We encountered a strange issue, one of our queries runs ok from oracle developer but sometimes ago started to fail in Appian. Appian reported it as rule error but in the log there is a timeout. We have it as 30 seconds and from oracle developer the query runs in 1-2 seconds.

What can cause such difference between using query from database tool and from appian?

  Discussion posts and replies are publicly visible

  • 0
    Certified Lead Developer
    There are various reasons this can occur - is there latency between Appian and the Oracle DB? are the filters applied exactly the same (maybe there are indexing issues or maybe the query isn't *exactly* the same)?

    Can you share the queryEntity expression as well as the query in Oracle?

    Can you try running a simple select in Oracle and Appian and compare execution time? This might isolate a latency/network issue.

    Also, I would start by looking into the data store logs - docs.appian.com/.../Logging.html. If you are on Appian Cloud, the perf_monitor_rdbms.csv log that is enabled by default is very helpful in analyzing the query.
  • 0 in reply to Justin Watts
    Basically the query is select * from db_view where ref_id = some_value with the same value. It returns 20 rows in db tool and fails in Appian.

    Thanks for the perf_monitor_rdbms and database performance log reference. It looks like something was recently changed on the database side that made the view very slow.
  • 0
    I still have this issue. As I understand Appian queries database in two steps: first, getting count and then taking data.

    My query returns first data fast enough but the count is very slow.

    Is there a way in Appian to just quickly grab the first N records?
  • 0
    A Score Level 2
     in reply to sergeiz
    Hi Sergeiz, you can get the N number of records using pagingInfo and with out giving Filter parameter for Query Entity.
    We generally see that there will be noticeable difference between the running query directly in oracle DB and through Appian. But this is because some times the network latency and the load on the server.
    if you still see the issue. can you check all the servers are up and running fine?
  • 0 in reply to chandu
    We have timeout of 90 seconds on Appian. I run a query in other tool and it starts return data in 25-110 seconds. But count takes 75-110 seconds. Query from Appian fails either almost on 90 seconds (timeout on count) or can return results in 130 seconds (count was below 90 seconds) which is longer than 90.
  • 0

    If you cannot find another solution, I have found success in the past by using the "Execute Stored Procedure" shared component to execute long-running queries, as that Smart Service is immune to the timeout of queryEntities (though I have never used the function that also comes with the shared component). This will also allow you to bypass the two-step query process that Appian uses and just get the desired rows, which going off what I've read in this post will reduce the time of the query to below the timeout threshold anyway.

  • 0
    A Score Level 1
     in reply to gianninol
    I agree with , We have also used same approaches to get the data back from DB if it is taking too much time using query entity. Since query entity is running with in the boundaries (like time outs and other stuff) of Appian where as Execute Stored Procedure does not. If you are using Oracle EE 12 then I think it is now allowing the json format reading and returning the data from DB. Where are you using this query like are you showing data inside the grid or just to export it to excel? Scenarios may vary based on the need.

    If you still want to continue with the query entity then you may heck the following. Please ignore if yo have already tried below things.

    1) You can have more indexes or filters in the query or pagination as @chandu suggested.
    2) You can increase query time out in the properties file but it will affect all other queries.

    I hope this may help.
  • 0
    Certified Lead Developer

    Hi  as per my understanding, you can also consider the following points as mentioned below:

    1. Make the use of Query entity with paging info with less batch size, let's say between 25 - to -100.

    2. If you are trying to query the View using query entity, then I would recommend switching to materialised view as this will give you much better performance compared to Views.

    3. Try to avoid clumsy manipulations as much as you can , such as multiple filter conditions, aggregations etc..

    4. Try to avoid multiple columns or huge amount of data entry for each row in DB.

    5. If possible, try optimising your expression rule.

    5. Once you are done with above mentioned steps, and still facing the issues, try querying one row and check how much time it's taking to return the response.

    6. Additionally could you please check with your system administrator about all the engine status and the CPU usage/ utilisation of the server where your Appian instances are running, because sometime due to some other application/ root cause you might be facing the issues,as you said, it's stopped working at sudden.

    Also, when you query the data from database in Appian / any other tools/programming language, first they need to retrieve the data and need to format the response as per their standard, such as datasubset/dictionary in case of Appian. Hence due to this you can expect minor difference in query execution duration while comparing with executing the same directly in DB Console.

    Hope above mentioned step will help you in debugging and solving this.

  • 0
    Hello Sergeiz,

    Multiple thought came to my mind. When you are talking about you are querying a view there are multiple possibilities.

    About the view, query and objects:

    * I guess your CDT is plain with no nested CDTs?
    * does your query entity has the field selection? If not try that, even you can think about a new CDT
    * another question does he view makes calculations, like formatting the date? Do you have comparatiosn against strings? Can you make competitions against numbers? Or dates which can simplify the where operations?
    * any union in your view?
    * Can you take out some calculations?
    * in the view are you joining too much tables? Can you simplify the view? Can you use left join?
    * Does your tables have proper indexes and primary Keys? If you are joining that is really important
    * if you can reduce the amount of information passed to Appian the better because the transformation fromDB to Appian object takes time.
    * Do you have any special anottations on the cdt for the view ?

    Architecture
    *are you on premise ? Or cloud?
    *if on premise the DB is on the same network? above some mentioned latency, that is crucial when comes to complex queries. which application server you have? How is the concurrency and the jdbc pool configuration?

    The problem just happens in that view?

    Let’s start with this

    Jose
  • 0 in reply to gianninol
    Thanks, using stored procedure to some long data preparation looks promising. I'm curios why appian doesn't have a parameter to skip paging if needed.

© 2024 Appian. All rights reserved.