catalog/timeseries optimization

[adamkorynta]

Preface: I was initially testing this before grabbing the latest changes from: #117 and so the timings are a lot better considering there used to be 73k records in total with aliases. Before this pull request, it was taking up to ~20minutes to get all of the data. Since this is really the first usage of RADAR in this way I'm not sure if the timings are expected or the ideal way to optimize the client. The current cursor implementation does not allow for multi-threading the retrieval so Example 1 or Example 2 would be the preferred usage in the existing CWMS client.

Setup:

• Test Database has ~13k time series identifiers
• RADAR instance is on the same machine as client

Goal: retrieve all time series identifiers (example usage: CWMSVue time series tab)

Benchmark: ~1minute Using JDBC and calling cwms_cat PL/SQL procedures

Example 1: ~3 minutes (call against RADAR 2 times)

• retrieve 500 page size (need to get the total count)
• retrieve ~12.5k page size (total - initial 500)

Example 2: ~3.5 minutes (call against RADAR 2 times)

• retrieve 1 page size (need to get the total count)
• retrieve ~13k page size (= total)

Example 3: ~5.5 minutes (call against RADAR 27 times)

• retrieve 500 page size in a loop until next-page is null

[adamkorynta]

Example 4: ~5 minutes (Call against RADAR 14 times)

• retrieve 1000 page size in a loop until next-page is null

[MikeNeilson]

hmm. I clearly need to work on that query. Have you tried something larger like a 5000 page size?

[adamkorynta]

5000 took over 5 minutes as well

[MikeNeilson]

well. that's clearly failing in it's purpose. The smaller queries are supposed to take less time. I'll dig into it but it will be next week.

[MikeNeilson]

I was able to speed it up I think quite a bit. It could be better but I think further improvements will have to be on the database side. Looked like the query was ignoring some indexes.