Performance degradation when extracting the result of a dozen thousand lines query
I have a query that is almost instantaneous in JVM environment:
WITH RECURSIVE q AS (
SELECT 1 as c, 'raiz' as str
UNION ALL
SELECT c + 1 as c, 'derivado ' || c as str
FROM q
WHERE c < 100*1000
)
SELECT * FROM q
Extracting all the information in a and putting then on a simple list costed me almost 30x more in Win32 executable than in JVM. More about the tests here. In a environment with real data, the time for a full read of the result set in \approx
40s (private issue #693), for about 70k lines. The culprit was GC calls, between 50% and 75% of the time spent was GC. For some reason, repeating this read would lead to less time spent GC-ing.
This was observed with TotalCross 4.3.8 and 4.4.1. Reproduced in a controlled environment in Win32, reported by users in Android.