Get size of blob

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Get size of blob

Olivier Mascia
Dear,

Let:

        create table DATA (
   DKEY blob primary key not null,
          DBYTES blob not null );

DKEY always stores 32 bytes, inserted as blob.
DBYTES stores anything, inserted as blob. Size varies widely, from some about 100 bytes to some hundreds Kilo-bytes.
Page size is default (4K).
These rows are never updated, only inserted (or selected) and very rarely deleted.

Will:

        select length(DBYTES) from DATA where DKEY=x'...';

be relatively light to retrieve the length(DBYTES) in some rare cases I'd need it (without needing the actual content)? Or will this have the unwanted side-effect to force sqlite to read all the content of DBYTES?
I know the encoding of the blob in the row has its size upfront, so I'd guess I'd be right to expect it won't do unneeded I/O to return the length(). But I'm not sure of this and wonder if I'd better cache the size in a separate column as in:

        create table DATA (
   DKEY blob primary key not null,
          DSIZE integer not null,
          DBYTES blob not null );

So if someone can confirm/infirm this assertion (about length(DBYTES) not involving needless I/O), I'd love to read you.

Thanks!

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Get size of blob

Richard Hipp-3
On 4/1/17, Olivier Mascia <[hidden email]> wrote:
>
> So if someone can confirm/infirm this assertion (about length(DBYTES) not
> involving needless I/O), I'd love to read you.
>

Confirmed.  Content is not read from disk for the length() or typeof()
functions.  https://www.sqlite.org/src/artifact/f1acf5744ce?ln=2675-2680


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Get size of blob

Dominique Devienne
On Sat, Apr 1, 2017 at 2:01 PM, Richard Hipp <[hidden email]> wrote:

> On 4/1/17, Olivier Mascia <[hidden email]> wrote:
>
> > So if someone can confirm/infirm this assertion (about length(DBYTES) not
> > involving needless I/O), I'd love to read you.
>
> Confirmed.  Content is not read from disk for the length() or typeof()
> functions.  https://www.sqlite.org/src/artifact/f1acf5744ce?ln=2675-2680


  2432  /* Opcode: Column P1 P2 P3 P4 P5
  2433  ** Synopsis: r[P3]=PX
  2434  **
  2435  ** Interpret the data that cursor P1 points to as a structure
built using
  2436  ** the MakeRecord instruction.  (See the MakeRecord opcode for
additional
  2437  ** information about the format of the data.)  Extract the P2-th column
  2438  ** from this record.  If there are less that (P2+1)
  2439  ** values in the record, extract a NULL.
  2440  **
  2441  ** The value extracted is stored in register P3.
  2442  **
  2443  ** If the column contains fewer than P2 fields, then extract a
NULL.  Or,

[DD] Don't you mean "record" (or "row") instead of "column" above ?

[DD] Isn't line 2441 a duplicate of lines 2438-2439 ?

[DD] If neither is true above, that implies a column contains several fields,
[DD] which would seem "denormalized"; Doesn't mean it's not valid, just that
[DD] I can't quite groke it as written.

  2444  ** if the P4 argument is a P4_MEM use the value of the P4 argument as
  2445  ** the result.
  2446  **
  2447  ** If the OPFLAG_CLEARCACHE bit is set on P5 and P1 is a
pseudo-table cursor,
  2448  ** then the cache of the cursor is reset prior to extracting the column.
  2449  ** The first OP_Column against a pseudo-table after the value
of the content
  2450  ** register has changed should have this bit set.
  2451  **
  2452  ** If the OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG bits are set on P5 when

[DD] on P5 *then* the result is ..., no?

  2453  ** the result is guaranteed to only be used as the argument of
a length()
  2454  ** or typeof() function, respectively.  The loading of large
blobs can be
  2455  ** skipped for length() and all content loading can be skipped
for typeof().
  2456  */

  2457 case OP_Column: {
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...