This is a follow up fix on #3227 and build on top of #3253.
This PR eliminates the JOIN between the testcases and sessions arrays as is this very detrimental to performance for sessions that run many testcases. The problem with the current implementation is that the JOIN will replicate the whole session json_blob for every testcase record bloating the memory consumption and hurting performance significantly!
The approach taken in this PR is replacing the single JOIN query with 2+1 SELECT. First we get the distinct session UUIDs that correspond to the test cases matching the selection criteria. Then we use those session UUIDs to extract their blobs from the sessions table decoding them and indexing them in memory. Finally, we get the testcase UUIDs matching the criteria selection and from this we are able to quickly pick the JSON data of each selected test case.
I tested this impelmentation a DB of 163 sessions and 38K test cases on two systems and we get 6-10x performance improvements on the --performance-compare / --performance-report options (from 20s to 2s and from 27s down to 4s). Now, the DB query is no more the limiting performance factor, but rather the JSON decoding and the aggregation of the results.
This is a follow up fix on #3227 and build on top of #3253.
This PR eliminates the JOIN between the
testcasesandsessionsarrays as is this very detrimental to performance for sessions that run many testcases. The problem with the current implementation is that the JOIN will replicate the whole sessionjson_blobfor every testcase record bloating the memory consumption and hurting performance significantly!The approach taken in this PR is replacing the single JOIN query with 2+1 SELECT. First we get the distinct session UUIDs that correspond to the test cases matching the selection criteria. Then we use those session UUIDs to extract their blobs from the
sessionstable decoding them and indexing them in memory. Finally, we get the testcase UUIDs matching the criteria selection and from this we are able to quickly pick the JSON data of each selected test case.I tested this impelmentation a DB of 163 sessions and 38K test cases on two systems and we get 6-10x performance improvements on the
--performance-compare/--performance-reportoptions (from 20s to 2s and from 27s down to 4s). Now, the DB query is no more the limiting performance factor, but rather the JSON decoding and the aggregation of the results.