I haven't seen any anyone else mention this yet. Another consideration when working with large blobs is to make those columns that last ones in your schema. To quote Dr. Hipp:
"make the BLOB columns the last column in your tables. Or even store the BLOBs in a separate table which only has two columns: an integer primary key and the blob itself, and then access the BLOB content using a join if you need to. If you put various small integer fields after the BLOB, then SQLite has to scan through the entire BLOB content (following the linked list of disk pages) to get to the integer fields at the end, and that definitely can slow you down." Found here: http://sqlite.1065341.n5.nabble.com/Effect-of-blobs-on-performance-td19559.html#a19560 Your blobs are small so this probably doesn't apply to your application, but something to keep in mind for future projects. -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Eric Grange > Sent: Thursday, March 03, 2016 3:16 AM > To: SQLite mailing list > Subject: Re: [sqlite] Are BLOB stored inline with the rest of the record > fields? > > > All BLOBs are stored inline. The only complication comes when the > > total > row length is longer than a page. > > Ok thanks! > > That is besides my original question but what is the complication in that > case? > Is the blob partially stored in the row, or is some indirection to other pages > used, with only some pointer stored in the row? > > > Surely you mean big-endian? > > Yes, my mistake :) > > > Storing Blobs has a few conversions added if you try to use the SQL to > store it, but it's quite efficient when using the API. > > By "use SQL", you mean as a literal blob embedded in the SQL string? > > My main use case would be through the API, the actual numbers are fixed > precision, and so scattered over the range they cannot be displayed to end > users without using exponents, so some formatting will have to happen. > Besides size, using a blob rather than base32/base64 would simplify the > encoding/decoding, and for debugging, blobs usually display in an > hexadecimal form here, so a big-endian blob would be directly "readable". > > > > On Wed, Mar 2, 2016 at 11:07 PM, Doug Currie <doug.currie at gmail.com> > wrote: > > > On Wed, Mar 2, 2016 at 4:42 PM, R Smith <rsmith at rsweb.co.za> wrote: > > > > > > > > > Personally, unless your space is constrained, I would simply save > > > the numbers as strings, perhaps Hex or BCD with leading chars and > > > convert as needed. This would sort correctly without tricks and not > > > do much worse > > for > > > space. (Base64 would be even better space-wise but won't sort correct). > > > > > > > There is an encoding defined in RFC 4648 "Base-N Encodings" that does > > preserve sort order; it is called Base 32 Encoding with Extended Hex > > Alphabet. I would think the Base64 alphabet could be rearranged to > > have the same property. > > > > e > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
