Hi All,
we have field on the interface, when user enters a values and tabs out, we query a table to get information and load the rest of the page.
Issue is when a user logs in for the first time and enters the value, the query takes lot of time (7-8 seconds to load, soemtimes 10+seconds and the screen errors out).
But if he tries subsequently 2nd or 3rd time, the querying becomes very fast (100-200 ms).
The logs show that the execute times is what takes the maximum time.
The table has 1.5 million rows and the column that we are searching on is properly indexed. So the table is properly optimized.
What could the possible reasons for it being really slow the first couple of times (1-2 times)
Discussion posts and replies are publicly visible
Hi Siddharth
Having an Index on a table of in itself is no guarantee that the Database engine is using it! Have you asked the DBA to perform an EXPLAIN on that table? If the Database internal statistics table doesn't reflect that actual data in the table (i.e. if the Database thinks there are only 100 rows rather than the 1.5 million rows) then the Database will perform a full table scan and ignore the index because it believes this is the most efficient way of retrieving the data. The DBA should be performing a RUNSTATS (this is the DB2 command) - or whatever the equivalent is for your Database platform - in order for the statistics tables to be updated. Even if this is up to date the EXPLAIN should still help you determine what the data retrieval plan is and whether other indexes need to be applied.
This could be as simple as the sort you're applying... Are you actively applying a sort within the query entity?
No just a!pagingInfo(1,-1)
The logs seems to show that the query is taking a specific column.
PagingInfo[startIndex=0, batchSize=-1, sort=[Sort[sl asc]]]]"
I'm guessing that's the primary key? Is the table already sorted in a particular order? Not sure it'll make a difference but might be worth checking. Also can you run the equivalent query directly on the database?
Thats not the primary key.
Actually the table does not have a primary key on a single column. It has a composite primary key on 3 columns. i have made one of those 3 columns as the primary key when i mapped the cdt to the table.
Well that will be a separate problem then - you may have missing values when you retrieve from this table, as Appian requires a single unique primary key.
If you run the query equivalent to the one appian is running directly on the database, what sort of performance do you get?
we are not able to recreate the slowness directly in the database. Its pretty quick (200 ms). Even in APPIAN it happens only the first time (10+ seconds). The screen errors out and if we try again its pretty quick.
we are trying to compare the APPIAN logs against the DB trace. in the process of getting more data points to come to a better conclusion.
So far we have only 2 data points -
Appian log shows 6.5 execution time, DB trace shows 6.4 seconds,
Appian log shows 9.5 seconds execution, DB trace shows 6.5 seconds. (Not sure where the 3 seconds delay is)