PST application: slow performance for investigations with large number of samples

[rssidlowski]

Stakeholders are reporting that the PST application is very slow for investigations with a large number of samples. An example is 114 E. Lexington St.

Can you take a look and see if any improvements can be made at the database level?

[rssidlowski]

To clarify, this is when a user selects an investigation and clicks on the samples button. It takes a long time (42 seconds) to load the list.

[gerrykelly]

1) I added a few indexes in SQL server that would have helped if the DB was the issue, but saw no improvement 2) The noted time (by Regina) for all 74 samples to display of about 42 sec is in line with what I saw (although even at that point not all the data is populated for the ones down the list) a. EXCEPT when I choose that investigation and click samples as the very first thing going into the app it only takes about 22 sec. Normal behavior with an RDBMS is that queries get faster when repeated bc the RDBMS caches them in memory and optimizes them 3) This led me to research the code (Sencha and web services) and look at things in Chrome debugger where I found: a. Roughly half the time is spent returning the sample features for the investigation. This is done through a call to ArcGIS server. Getting the data from SQL Server would likely be an order of magnitude faster i. I would not criticize this as a bad development choice up front. It’s consistent with dealing with the samples as ArcGIS features as is always done elsewhere in the app. Taking the SQL Server route would only be the apparent choice if the developer had known that investigations could have so many samples, performance would be an issue and you only needed attribute data for this part of the app ii. I don’t know if something can be improved on the ArcGIS Server side. It seems like it should not take this long. iii. Revising the code to do this part by SQL would be a significant effort b. The rest of the time is getting the lab data i. But something is wrong here. The app sends 2775 requests for lab data, repeating queries for the same sample ID again and again. This should be fixed but could wind up taking several hours.

So, turning it back to you for further direction. It might be worth asking Joe to see if anything can be improved on the ArcGIS server side, but it probably makes sense for me to do my part with the lab data issue first and then see where we are after that,

[gerrykelly]

the javascript part of this is fixed in dev. For the investigation with the most samples (421 with 117) response time went from 126 seconds to less than 6. I don't think we'll see quite that performance in prod (bc AGS is a lot busier there), but it should resolve the issue.
I don't know that there is really anything for you to test in dev, but you might just open the app and confirm that some of the basic functions still work. Otherwise I would like to deploy this to prod ASAP. Should I wait until they are done their workday? (and when would that be?)

[gerrykelly]

done in prod

[rssidlowski]

I verified. I will leave open until I receive confirmation from the stakeholders.

[rssidlowski]

Stakeholders verified. Closing.