Title
#general
f

fritz

07/14/2020, 5:01 PM
Knowing very little about sqlite wizardry, could you utilize a COUNT function to determine total number of rows, then use CTEs to return the data in sets eg. If rpm_packages returns 15k rows and the rpm_db chokes when trying to return all at once, could you partition the result-set using row_id and then reassemble afterwards with a UNION? (eg.
WITH rpm_result_set1 AS (SELECT * FROM rpm_packages WHERE rowid > 0 AND rowid < 1000), rpm_result_set2 AS (SELECT * FROM rpm_packages WHERE rowid >= 1000 AND rowid <= 2000)... SELECT * FROM rpm_result_set1 UNION ALL SELECT * FROM rpm_result_set2
)
packetzero

packetzero

07/14/2020, 5:26 PM
I'd say this counts as wizardry... ๐Ÿง™โ€โ™‚๏ธ๐Ÿฝ
zwass

zwass

07/14/2020, 5:50 PM
This would depend on the implementation of the rpm_packages table. Is it able to optimize the query using the rowid?
packetzero

packetzero

07/14/2020, 5:53 PM
Good point. It does index on "name" column. Might be doing table scans for each sub select. I would be interested in seeing the comparison.
zwass

zwass

07/14/2020, 5:55 PM
If that's the case I think this query will perform dramatically worse because it will force multiple full reads that are later filtered by the sqlite engine.
๐Ÿ‘† 1
s

seph

07/14/2020, 6:33 PM
Iโ€™d bet that itโ€™s indexed on name, because if you give it a name, itโ€™ll fetch the rpm info for that name. I would be surprised if there was any kind of cleverness in trying to do pagination.
6:34 PM
Depending on what the issue is. there might be optimizations to be had akin to the just merged use of generators for shell history. But Iโ€™d have to look for real and not just speculate ๐Ÿ˜‰
f

fritz

07/14/2020, 7:26 PM
Thanks for chiming in guys, I figured it was a long shot but worth mentioning as a possibility ๐Ÿ™‚
a

Ahmed

07/15/2020, 11:28 AM
thanks all, at least i learned something new ๐Ÿ˜„