Reply to thread

Message

No, there was one more thought
Running Analyze after the import. This creates a permanent table in the database with technical information, which is supposed to help the query optimizer of SQLite to better optimize the queries. Results as follows:

locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran "ANALYZE;" before
ReadTest 1: 11984ms, 11863ms (23847ms)
ReadTest 2: 193ms, 197ms (390ms)
ReadTest 3: 5.3ms, 5.0ms (10.3ms)
ReadTest 4: 49.7ms, 45.5ms (95.2ms)

This indeed seems to speed up ReadTest 2 and 3 by more than 10% - but ReadTest 4 is more than 10% slower. No idea why.
Repeated the test:

locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran ANALYZE; before
ReadTest 1: 12153ms, 11835ms (23988ms)
ReadTest 2: 210ms, 205ms (405ms)
ReadTest 3: 5.2ms, 9.9ms (15.1ms)
ReadTest 4: 44.9ms, 41.5ms (86.4ms)

Now as you see, ReadTest 2 is slower, albeit not as slow as without analyze, but ReadTest 3 is now even slower than without Analyze.
ReadTest 4 is faster than before, but still slower than the result without Analyze.

So the only conclusion I can make for now is that none of the ideas above really brought a breakthrough. Sometimes it was about 10% faster, but sometimes 10 or 20% slower - all of this more or less driven by coincidence. Seems like we have reached a point at which it is very hard to get additional performance benefits by just tuning some parameters. I will therefore stop doing further optimization tests for now and just wait for system.data.sqlite v 1.0.89.0. This will be the last thing to do before we try to get v0.08 of SQLiteDatabase Plugin ready

<blockquote data-quote="MJGraf" data-source="post: 1036211" data-attributes="member: 17886">No, there was one more thought <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite8" alt=":D" title="Big Grin&nbsp; &nbsp; :D" loading="lazy" data-shortname=":D" />Running Analyze after the import. This creates a permanent table in the database with technical information, which is supposed to help the query optimizer of SQLite to better optimize the queries. Results as follows:locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result /&nbsp; Ran &quot;ANALYZE;&quot; beforeReadTest 1: 11984ms, 11863ms (23847ms)ReadTest 2: 193ms, 197ms (390ms)ReadTest 3: 5.3ms, 5.0ms (10.3ms)ReadTest 4: 49.7ms, 45.5ms (95.2ms)This indeed seems to speed up ReadTest 2 and 3 by more than 10% - but ReadTest 4 is more than 10% slower. No idea why.Repeated the test:locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result /&nbsp; Ran ANALYZE; beforeReadTest 1: 12153ms, 11835ms (23988ms)ReadTest 2: 210ms, 205ms (405ms)ReadTest 3: 5.2ms, 9.9ms (15.1ms)ReadTest 4: 44.9ms, 41.5ms (86.4ms)Now as you see, ReadTest 2 is slower, albeit not as slow as without analyze, but ReadTest 3 is now even slower than without Analyze.ReadTest 4 is faster than before, but still slower than the result without Analyze.So the only conclusion I can make for now is that none of the ideas above really brought a breakthrough. Sometimes it was about 10% faster, but sometimes 10 or 20% slower - all of this more or less driven by coincidence. Seems like we have reached a point at which it is very hard to get additional performance benefits by just tuning some parameters. I will therefore stop doing further optimization tests for now and just wait for system.data.sqlite v 1.0.89.0. This will be the last thing to do before we try to get v0.08 of SQLiteDatabase Plugin ready <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite8" alt=":D" title="Big Grin&nbsp; &nbsp; :D" loading="lazy" data-shortname=":D" /></blockquote>

[QUOTE="MJGraf, post: 1036211, member: 17886"] No, there was one more thought :D Running Analyze after the import. This creates a permanent table in the database with technical information, which is supposed to help the query optimizer of SQLite to better optimize the queries. Results as follows: [B]locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran "ANALYZE;" before[/B] ReadTest 1: 11984ms, 11863ms ([B]23847ms[/B]) ReadTest 2: 193ms, 197ms ([B]390ms[/B]) ReadTest 3: 5.3ms, 5.0ms ([B]10.3ms[/B]) ReadTest 4: 49.7ms, 45.5ms ([B]95.2ms[/B]) This indeed seems to speed up ReadTest 2 and 3 by more than 10% - but ReadTest 4 is more than 10% slower. No idea why. Repeated the test: [B]locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran ANALYZE; before[/B] ReadTest 1: 12153ms, 11835ms ([B]23988ms[/B]) ReadTest 2: 210ms, 205ms ([B]405ms[/B]) ReadTest 3: 5.2ms, 9.9ms ([B]15.1ms[/B]) ReadTest 4: 44.9ms, 41.5ms ([B]86.4ms[/B]) Now as you see, ReadTest 2 is slower, albeit not as slow as without analyze, but ReadTest 3 is now even slower than without Analyze. ReadTest 4 is faster than before, but still slower than the result without Analyze. So the only conclusion I can make for now is that none of the ideas above really brought a breakthrough. Sometimes it was about 10% faster, but sometimes 10 or 20% slower - all of this more or less driven by coincidence. Seems like we have reached a point at which it is very hard to get additional performance benefits by just tuning some parameters. I will therefore stop doing further optimization tests for now and just wait for system.data.sqlite v 1.0.89.0. This will be the last thing to do before we try to get v0.08 of SQLiteDatabase Plugin ready :D [/QUOTE]