Performance issue with Get Dataset Versions Summary API

[ekraffmiller]

For a Dataset with a large number of files, the API call be slow, for example for this dataset: https://qa.dataverse.org/spa/datasets?persistentId=doi:10.7910/DVN/29236.
This issue is for reviewing the current implementation, to see if there are any performance improvements that can be made.

[ekraffmiller]

Hi @stevenwinship , Leonid pointed me to another Dataset that doesn't have a lot of files, but has 420 versions https://qa.dataverse.org/dataset.xhtml?persistentId=doi:10.7910/DVN/VH6GVH. It seems like adding paging to the API would improve performance for situations like this, do you think that would help?

[stevenwinship]

I ran re-indexing on QA's db and no difference was made.
On Prod the above request takes approximately 6 seconds to fulfill. On QA the same request takes approximately 16 seconds.

After analyzing the difference between prod and qa I have determined that the latency is most likely due to the different configurations between the two Postgres servers. As an example: Prod shared_buffers=997360 and QA shared_buffers=16384, a magnitude of almost 8 time greater on Prod.

One improvement I made to QA was to add an index on the filemetadata column 'label'. Since the query orders by this column, and it had no index, I was hoping this would help. I did not notice any difference.

The QA cluster was never meant to completely duplicate the Prod environment. Trying to do so would greatly increase the cost.

I will suggest adding this query to the Perf tests, since it's a heavy hitter, so we can monitor any degradation in future releases.

[stevenwinship]

Getting the version difference requires having all the filemetadatas for the version. I think if we get them a batch at a time it would probably take longer. I can look into that option.