Query Database Smart Service

It looks like that view has a performance issue. Too many rows may be getting return causing that view to timeout on the Appian side. Lower the batch size and try running it again. You may want to make sure you're following best practices. I know removing sub-queries and joining on tables has better performance.

We've reduced batch size to 1, no impact. We've tested query performance directly in DB and it executes in well under 1 sec. We've tested using the query DB smart service, mixed results depending on whether the "where" parameter is hard-coded or not. Unfortunately, we cannot reduce any subqueries because of the way the data is organized in the tables.

I agree with Jamal's estimation. To improve performance, could you update the source to be a materialized view or some snapshot table that's updated at regular intervals?

If that's not possible, you can look into adding indexes or refactoring the query to be more performant.

Not sure how much more performance I can get from the query or the view that I am querying, my real concern is over the drastic difference in performance on the Appian side between using an ACP to define a dynamic parameter for the "where" condition versus hard-coding a value into that condition. When executed from SQL command line, the query can pull 4 rows of data in less than 100 milliseconds. When executing using hard-coded values in the "where" condition using the Query DB Smart Service, I get similar response time. However, by simply substituing the hard-coded values for an ACP that I set external that node now takes > 2 minutes to exectute. Seems more like an Appian related issue than a database problem.

Using the Query Database Smart Service:
method 1: select bac, acwp, bcws, bcwp from evm.subprojectevdata where subprojectid = 20321, < 100 milliseconds to execute
method 2: select bac, acwp, bcws, bcwp from evm.subprojectevdata where subprojectid = ac!subprojectId, >2 mins to execute, with ac!subproject = 20321

We are trying to do this to create a snapshot table to reduce the impact of extremely poor table design in another system. Right now, we are developing a smart service to perform the query...not sure if that will improve performance or not.

Thanks for recommendations...we'll rexamine our efforts in those areas.

This is interesting if there is a view problem then, running the query with hardcoded value should take more time , In your case its taking more time only when where clause is not hard coded. Strange one. Please confirm this are u getting data after 2 mins? are node got simply struck? Please post back here how u resolved this. I will have a try.

I can confirm that it is returning data and that the node is not stuck. Glad I'm not the only that finds this odd..lol...We are close to being ready to test our new smart service. I'll update any progress here. Thanks.

not sure it is possible to create another view with required data in the source system and then just simple select from that view

Quick update...we created a new smart service that we pass two parameters to (1. JNDI of the data-source, 2. SQL string (ex. ="select * from evm.projectEVDataView where subprojectId = " & pv!subprojectId)...the smart service returns a JSON with the desired data, in an acceptable time (< 500 ms)...we are now implementing some security measures on the smart service to prevent "bad stuff" from "accidentally" happening...

Thanks for providing an update. Happy to hear you were able to resolve your issue.