https://github.com/osquery/osquery logo
#general
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
)
p

packetzero

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

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?
p

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.
z

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.
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 ๐Ÿ˜„
2 Views