Slow performance interface

Question

Team, 
 I have a below code which is mix of rules and query. Code quires DB to get data on a quarterly basis which is around 50k every quarter then compares them with rule data and show count. 
 Interface is very slow to load and also takes lot of time to show second drop down when first one selected. Can you guys suggest some improvements to improve the performance .

Marcel Paradies · Answer

You will probably need to go into the interface designer, on the perfomance tab, and see which rules are contributing to the slow perfomance. From my personal experience, performance drops happen when you just load the database into an interface and do calculations in advance, rather than letting the DB do its calculations through optimised queries.

Dave Lewis · Answer

How many users does Appian have to query from the group, and how many does it have to grab from the DB? 
 Are you querying DB for all rows, then for each row checking to see if that user is in groups? You could be suffering as much as you are running that many queries against the groups engine. An Appian deployment can have as many as 32 pairs of analytics and process engines, but even then will still have only 1 groups engine. If you ask for a particular user a few thousand times in a row it could get bogged down. 
 If you pull all users once, and pull all rows from DB and use Appian to do the compare, that could also be very slow. Especially if you compare each row of Groups against each row of DB. That leads to a Cartesian Product. Comparing just 32 users against 32 rows that way is over 1000 operations. Its the number of users you're checking TIMES the number of rows in the DB, which I think you said is 50k. 50k times anything is a lot. 
 Perhaps try gathering all usernames from Groups. That will be a huge query, but only 1 huge query. Then pass that as a parameter to your DB query, returning COUNT FROM yourTable WHERE userName IN (*your list*) This should have 2 advantages; running only 2 queries and returning the least amount of data possible from your DB. Make DBMS do calculations including counting the rows; it's much faster at data aggregation.

ManuelHTG · Answer

are you looping the expression for generating the count? - If yes, there are there is one optimization i can think of 
 1) Do the calls to the DB on the highest interface possible (i.e. on the grid interface) and passed as a reference - Avoid any call-loopings 
 As well, where() is faster than wherecontains() so perhaps you can use some logical expression to evaluate instead.

Dave Lewis · Answer

Very good, IN operator is key. I would try seeing what you could do with 100 users. Benchmark going through the whole set in batches of 10 and batches of 100. I think you'll find through benchmarking that larger batches are actually much more efficient. 
 In general, well formed queries scale logarithmically, which means after paying an up front cost, you can always do double the operations in less than double the time, and you can always do 10 times the operations for less than 10 times the cost. The more rows you return, the less noticeable any further increase in rows becomes in terms of time spent. Unless of course the DB isn't well optimized. 
 You might find it most efficient to query absolutely all the users at once.