Investigate: sort responses by date (oldest to newest) before download

[r00dgirl]

Natural order in which responses are sorted on most if not all form managers (many users are confused this isn't the default). Our csv download should sort by date by default instead of requiring form admins to sort as an additional step. This makes it easier to copy paste just the newest responses into another spreadsheet that the admin may already be maintaining or have made other edits / analyses on.

[karrui]

May not be possible; we write to the CSV file directly from the database cursor, and it is most likely not feasible to store and sort the data in memory as the response size is unbounded.

Running sort on the submissions collection cursor will likely also slow down the query drastically and increase memory usage (needs to be verified though).

Someone can feel free to chime in and correct me if I'm wrong!

[r00dgirl]

@liangyuanruo think you've worked a lot with this part of the code too, any thoughts? @mantariksh @tshuli too

[mantariksh]

agree with @karrui's assessment, barring implementing some kind of external merge-sort, which is used in databases to sort large queries with limited memory

[mantariksh]

I tried looking for external sort npm packages, but these all seem to have <20 weekly downloads, not sure if this problem is important enough to warrant using one of these or implementing our own
https://www.npmjs.com/package/external-sort
https://www.npmjs.com/package/external-sorting
https://www.npmjs.com/package/exmes

[liangyuanruo]

I think discussing the performance of the database is not helpful, because result ordering is no longer preserved due to concurrency during the decryption step in the worker pool. any sort will need to be performed client-side (which imposes limits on system resources) on a separate worker thread.

the simplest implementation that I can think of right now is to introduce another message-passing mechanism and use another Web Worker to sort the aggregated data right before the download step. I think we can try to get speedups with e.g. insertion sort if the natural order from the DB produces an almost-sorted array, but one still needs to contend with memory limits in the browser.

[mantariksh]

I think discussing the performance of the database is not helpful

Sorry if I wasn't clear - when I said "used in databases", I was just providing context for other uses of the same algorithm. I was proposing that we use this algorithm client-side to overcome the memory limitations of sorting a large number of responses.

[liangyuanruo]

I was proposing that we use this algorithm client-side to overcome the memory limitations of sorting a large number of responses.

Ah I see... see how regular Array.prototype.sort()'s in-place sort performs first? I'd expect memory usage to temporarily double when data has to be passed between the main thread and the web worker.