Hi I'm using the export data to Excel plug in to allow users to

@paulc Out of interest, may I please know how the data is being retrieved - native SQL query or query rules of Appian?

I had originally tried a Query Rule using a date window (from and to date) - when that limit was reached I'd then tried the Database Query Smart Service - which has now hit the 1000 records limit

@paulc You could opt for one of the approaches specified below:

1. This approach is with respect to query rules. Implement the batching technique, i.e. retrieve the records in batches where each batch retrieves a specific number of records. Let's take your instance - We have 4000 records to query, Get 1000 records (1 - 1000 i.e. rule!myQueryRule(topaginginfo(1,1000))) in first batch and proceed towards second batch and retrieve next 1000 (1001 - 2000 i.e. rule!myQueryRule(topaginginfo(1001,1000))) records and so on. I guess this is the only viable way to obtain the entire dataset with the limitations imposed by Appian on the way we fetch data by querying RDBMS.

2. This approach is with respect to query database smart service. I guess there is no other way to retrieve entire dataset other than running the query database smart service for multiple times.

Although there are ways to increase the limit on the amount of data that can be obtained using query rules or query database smart service, this might not be a best practice.

Additionally you can try for a performance gain by implementing a!queryEntity() as specified at https://forum.appian.com/suite/help/7.9/Querying_Data_From_an_RDBMS.html.

And with respect to export time limit, I guess we can't guarantee that the queries will be performed within the intended time limit because the time consumed by queries is directly proportional to the amount of data we are trying to query.

Hope that helps you to some extent.

It does indeed - thank you very much ... as I'm reasonably new to Appian Development is there a preference for database access - ie can I implement a!queryEntity within a script task node?, are query rules better for performance over the query database smart service ? Cheers

@paulc Yes you could use a!queryEntity() in a script task. It is similar to any other normal function where you can use it an script task or expression or sail interface etc. I am sorry but I can't make a statement about the performance as I haven't made a comparison till date. I would like to suggest to make use of query rules rather than Query database whenever possible because Query database requires the updates in native SQL queries when the database vendor changes. Further you can't invoke the Query database on the SAIL interface whereas the query rules can be.