Performance problem with 2400 rows

subhankarb over 8 years ago

I have a database view (2400 rows), and using this I am displaying a multi layered report in tempo interface. I am currently facing issue with the expression execution response time based on "Interface Designer" Performance View matrix.

In my expression I used "a!pagingInfo", and the system response time is as below based on the "a!pagingInfo" configuration.

CASE 1:

local!pagingInfo: a!pagingInfo(
                                         startIndex: 1,
                                         batchSize: 20
                                         ),
Response Time: 9.02 Seconds

CASE 2:

local!pagingInfo: a!pagingInfo(
                                         startIndex: 1,
                                         batchSize: -1
                                         ),
Response Time: 1.01 Seconds

I am surprised to see the difference in system "Response Time" based on the paginginfo configuration.
Can anyone, please provide an explanation on this.

Discussion posts and replies are publicly visible

0 Eduardo Fuentes
Appian Employee
over 8 years ago

I'm thinking that the second case is not returning all the results but erroring out due to a memory threshold being reached for the query rule or a timeout therefore the response time shows less seconds.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 subhankarb over 8 years ago

I tested this in our Pre Prod environment, and experienced the above response time based on the pagingInfo configuration. The attached performance graph shows the expression rule response view in Pre Prod environment.
The expression fails to load in the production environment. Although I am not able to reproduce the same response in pre prod environment as on today. Can we consider the system engine load as a factor for this?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Eduardo Fuentes
Appian Employee
over 8 years ago

RE: "The expression fails to load in the production environment."

When it fails you need to review the application server log and focus on the Caused by clauses. They'll tell you why it fails, e.g. memory threshold, timeout, etc.

The size/amount of data retrieved is usually what makes the big difference other than the load.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 subhankarb over 8 years ago

Hi Eduardo,
You were right about the "timeout" factor. Here is the server log which shows, "query did not finish within the timeout period". I am going ahead with the CASE 2 configuration in the local!pagingInfo expression. I was curious to know why the "batchSize" is playing a significant role for the above scenario as shown in the graph.
Server Log.txt
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Eduardo Fuentes
Appian Employee
over 8 years ago

What batchSize is the one that times out? 20 or -1?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 subhankarb over 8 years ago

it's 20.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 gauravg568 over 8 years ago

Could it be due to sort? To return the first 20 results, could it be that the entire dataset has to be sorted first? Also, does the database table have an index specified in the database? In the past, adding an index (such as on Primary key column) has resulting in speeding up the execution of the queries.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 jamesm90 over 8 years ago

I know this is an old post, but I was wondering if you ever found the root cause? I am experiencing very similar behavior, and it makes no sense to me. Even if this is due to having to sort the entire view if batch size is 20, I don't understand why this results in such a performance hit, considering that -1 would still also have to sort the entire data set.

If you recall the root cause to this problem, or the solution you implemented, could you share that insight with me?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel