On 2015-03-01 08:40 PM, David Barrett wrote: > Hi! If I define a column as BLOB type, will length() run strlen() on the > data, or will it consult some kind of internal value to determine the > length? The documentation says: > > "For a string value X, the length(X) function returns the number of > characters (not bytes) in X prior to the first NUL character. ... For a > blob value X, length(X) returns the number of bytes in the blob." > > This suggests to me it uses some internal length value (eg, not strlen), > but given that sqlite doesn't use strong typing, I'm wondering if I insert > a string into that blob if it'll return the blob length or the string > length.
I believe it is StrLen() that is the more complex because it needs to consider multi-byte character sets (Unicode etc.) as opposed to just straight byte-length. SQLite knows the byte-size of any stored value, this is imperative to the way it stores data and querying it is rather quick (though I do not know of any benchmarks having tested it specifically, but you are welcome to devise a test bed for it). If it is really performance-sensitive, or some tests reveal the length function being a tad sluggish, you might opt to simply add another column to store the byte/string/whatever lengths you wish and use the normal column value in your select to fetch it (as opposed to a function) - that will most certainly be very fast when fetching values, but it might add a few cycles to the inserts/updates. Also, conventional wisdom dictates storing strings as strings, I am very sure it is at least as fast as storing blobs. Internally, SQLite writes bytes, whether they are from strings or blobs, not much difference. Significant time was spent by the SQLite devs to make sure strings go in and out of the DB very efficiently. You will score a few cycles if you can live without UTF8, but not much. Please do devise some speed tests though, I believe you won't find a significant performance difference but it's worth checking if your application needs every drop of speed - just to be sure (Plus we'd be interested in the results if you do find significant differences).
