Reply to thread

Message

<blockquote data-quote="MJGraf" data-source="post: 1036234" data-attributes="member: 17886">I knew there was one more thing I wanted to try <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" />I read somewhere that using a BLOB as primary key is a very bad idea. MP2 uses Guids as primary keys and I told SQLite to store Guids as BLOBs. New idea therefore, let's store Guids as text (unfortunately Guids are 128 Bit. I also read that you could get a real speed improvement by using INT (64 bit) as primary key, because SQlite creates its own (hidden) RowNumber cloumn for every table unless you use an INT as primary key. In the latter case, the primary key is at the same time used as RowNumber, which saves time. But this is not possible, because we don't get our 128 bit Guid into a 64 bit INT...)So here the results for Guids stored as string:locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result /&nbsp; Store Guids as text (instead of binary)ImportTest: 20:53 minReadTest 1: 12770ms, 12732ms (25502ms)ReadTest 2: 234ms, 237ms (471ms)ReadTest 3: 12.7ms, 10.4ms (23.1ms)ReadTest 4: 51.9ms, 32.9ms (84.8ms)Result is: writes are slower (expected, because the database file is now a bit bigger and therefore more data to write). ReadTests 2 and 4 are a bit slower, ReadTest 3 is materially slower. So back to storing Guids as binaries (and don't always believe what you read in the internet <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: 1036234, member: 17886"] I knew there was one more thing I wanted to try :D I read somewhere that using a BLOB as primary key is a very bad idea. MP2 uses Guids as primary keys and I told SQLite to store Guids as BLOBs. New idea therefore, let's store Guids as text (unfortunately Guids are 128 Bit. I also read that you could get a real speed improvement by using INT (64 bit) as primary key, because SQlite creates its own (hidden) RowNumber cloumn for every table unless you use an INT as primary key. In the latter case, the primary key is at the same time used as RowNumber, which saves time. But this is not possible, because we don't get our 128 bit Guid into a 64 bit INT...) So here the results for Guids stored as string: [B]locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Store Guids as text (instead of binary)[/B] ImportTest: [B]20:53 min[/B] ReadTest 1: 12770ms, 12732ms ([B]25502ms[/B]) ReadTest 2: 234ms, 237ms ([B]471ms[/B]) ReadTest 3: 12.7ms, 10.4ms ([B]23.1ms[/B]) ReadTest 4: 51.9ms, 32.9ms ([B]84.8ms[/B]) Result is: writes are slower (expected, because the database file is now a bit bigger and therefore more data to write). ReadTests 2 and 4 are a bit slower, ReadTest 3 is materially slower. So back to storing Guids as binaries (and don't always believe what you read in the internet :D ) [/QUOTE]

Verification