Thanks for all the details! I was already storing the really large blobs in their own tables.
However I will have to review the situations for smallish blobs (about as large as the rest of the fields in a record), as moving them to another table would halve the number of pages involved for queries that do not need them... of course that would have to be balanced with more complex sql, and more page reads in the cases where those blobs are needed... why can't we both have our cake and eat it? :) On Thu, Mar 3, 2016 at 5:40 PM, Drago, William @ CSG - NARDA-MITEQ < William.Drago at l-3com.com> wrote: > 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. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
