Row length in SQLITE

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Row length in SQLITE

Deon Brewis
Is there any way to get the length of rows in a table / index in sqlite?

DBSTAT/sqlite3_analyzer --stats almost gives me the information I want, but it's an aggregate sum & max per page - I need the data per row (cell).

- Deon

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

Re: Row length in SQLITE

Simon Slavin-3
On 22 Jan 2020, at 11:44pm, Deon Brewis <[hidden email]> wrote:

> Is there any way to get the length of rows in a table / index in sqlite?

Do you mean the count of rows in a table / index ?

   SELECT count(*) FROM MyTable

There's no easy fast way to do this because SQLite doesn't keep that number handy anywhere.  It stores the entries in a tree and it would have to manually count the leaves of the tree.

----

Or do you mean the count of columns in a table / index ?

    SELECT * FROM MyTable LIMIT 1

and count the number of columns returned.  Or in C

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

Re: [EXTERNAL] Row length in SQLITE

Hick Gunter
In reply to this post by Deon Brewis
SQLite uses a compressed format to store records (be it rows of a table or entries in an index), so the length of a specific record depends on its contents.

See https://sqlite.org/fileformat.html

Storing a row of (NULL, NULL, NULL, NULL) takes just 5 bytes, whereas (1024, 1.234, 'some string', X'0123456789ABCDEF') requires 34 bytes.

The best you can determine is the average length of a record for the dataset you have loaded.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Deon Brewis
Gesendet: Donnerstag, 23. Jänner 2020 00:45
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Row length in SQLITE

Is there any way to get the length of rows in a table / index in sqlite?

DBSTAT/sqlite3_analyzer --stats almost gives me the information I want, but it's an aggregate sum & max per page - I need the data per row (cell).

- Deon

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row length in SQLITE

Deon Brewis
In reply to this post by Simon Slavin-3
No I mean e.g.

row 1 = 500 bytes,
row 2 = 600 bytes
row 3 = 80 bytes
row 4 = 300 bytes

etc.

Like the info that DBSTAT gives, but per row, not per page. This doesn't need to be performant - it's for usage analysis during development time.

- Deon

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Simon Slavin
Sent: Wednesday, January 22, 2020 5:24 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Row length in SQLITE

On 22 Jan 2020, at 11:44pm, Deon Brewis <[hidden email]> wrote:

> Is there any way to get the length of rows in a table / index in sqlite?

Do you mean the count of rows in a table / index ?

   SELECT count(*) FROM MyTable

There's no easy fast way to do this because SQLite doesn't keep that number handy anywhere.  It stores the entries in a tree and it would have to manually count the leaves of the tree.

----

Or do you mean the count of columns in a table / index ?

    SELECT * FROM MyTable LIMIT 1

and count the number of columns returned.  Or in C

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

Re: [EXTERNAL] Re: Row length in SQLITE

Hick Gunter
As previously mentioned, SQLite uses a compressed format to store rows. You would have to reverse engineer at least the calculation

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Deon Brewis
Gesendet: Samstag, 25. Januar 2020 05:14
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Row length in SQLITE

No I mean e.g.

row 1 = 500 bytes,
row 2 = 600 bytes
row 3 = 80 bytes
row 4 = 300 bytes

etc.

Like the info that DBSTAT gives, but per row, not per page. This doesn't need to be performant - it's for usage analysis during development time.

- Deon

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Simon Slavin
Sent: Wednesday, January 22, 2020 5:24 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Row length in SQLITE

On 22 Jan 2020, at 11:44pm, Deon Brewis <[hidden email]> wrote:

> Is there any way to get the length of rows in a table / index in sqlite?

Do you mean the count of rows in a table / index ?

   SELECT count(*) FROM MyTable

There's no easy fast way to do this because SQLite doesn't keep that number handy anywhere.  It stores the entries in a tree and it would have to manually count the leaves of the tree.

----

Or do you mean the count of columns in a table / index ?

    SELECT * FROM MyTable LIMIT 1

and count the number of columns returned.  Or in C

    sqlite3_column_count()
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row length in SQLITE

Keith Medcalf
In reply to this post by Deon Brewis

You can certainly get the max and average cell size per page of rows from dbstat which is the most granular data available I think, as well as the average and max for all the rows taken together.  Assuming that the table is a "rowid" table, then that is the data for the "leaf" pages only.  As in:

   select Schema,
          Name,
          PageNo,
          ncell as nrows,
          payload / ncell as avglen,
          mx_payload as maxlen,
          payload as payload,
          unused as unused,
          pgsize as total
     from dbStat
    where PageType == 'leaf'
      and Schema == 'main'
      and Name == 'Details'
union all
   select Schema,
          Name,
          'All',
          sum(ncell) as nrows,
          sum(payload) / sum(ncell) as avglen,
          max(mx_payload) as maxlen,
          sum(payload) as payload,
          sum(unused) as unused,
          sum(pgsize) as total
     from dbStat
    where PageType == 'leaf'
      and Schema == 'main'
      and Name == 'Details'
;

The row size will not vary by alot unless the table contains 'text' or 'blob' column data.  The schema and summary for the above table looks like this:

CREATE TABLE Details
(
    ApplianceID     integer not null references Appliances(ApplianceID) on delete cascade,
    SrcIntID        integer not null references Interfaces(InterfaceID) on delete cascade,
    DstIntID        integer not null references Interfaces(InterfaceID) on delete cascade,
    Transport       text not null collate nocase,
    SrcHostID       integer not null references Hosts(HostID) on delete cascade,
    SrcPort         integer not null,
    DstHostID       integer not null references Hosts(HostID) on delete cascade,
    DstPort         integer not null,
    Action          integer not null,
    Count           integer not null,
    FileID          integer not null references Files(FileID) on delete cascade
);

schema      name        'All'       nrows       avglen      maxlen      payload     unused      total
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
main        Details     All         11608351    27          31          321835482   1851729     404230144

which indicates that all the rows are about the same size and the average is 27 bytes, which is somewhat less than the 95 bytes max I would expect per row if the integers were all stored as 8 byte integers.

Are you trying to find rows that have "wacky large" data (as in blob/text) stored in them?

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Deon Brewis
>Sent: Friday, 24 January, 2020 21:14
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Row length in SQLITE
>
>No I mean e.g.
>
>row 1 = 500 bytes,
>row 2 = 600 bytes
>row 3 = 80 bytes
>row 4 = 300 bytes
>
>etc.
>
>Like the info that DBSTAT gives, but per row, not per page. This doesn't
>need to be performant - it's for usage analysis during development time.
>
>- Deon
>
>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Simon Slavin
>Sent: Wednesday, January 22, 2020 5:24 PM
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Row length in SQLITE
>
>On 22 Jan 2020, at 11:44pm, Deon Brewis <[hidden email]> wrote:
>
>> Is there any way to get the length of rows in a table / index in
>sqlite?
>
>Do you mean the count of rows in a table / index ?
>
>   SELECT count(*) FROM MyTable
>
>There's no easy fast way to do this because SQLite doesn't keep that
>number handy anywhere.  It stores the entries in a tree and it would have
>to manually count the leaves of the tree.
>
>----
>
>Or do you mean the count of columns in a table / index ?
>
>    SELECT * FROM MyTable LIMIT 1
>
>and count the number of columns returned.  Or in C
>
>    sqlite3_column_count()
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Row length in SQLITE

Keith Medcalf
In reply to this post by Deon Brewis

Here is a wee bit of C code that you can compile as a plugin that will give you the row size (well, it may be bigger than the actual record size by a few bytes but it is pretty close) ...

works properly for utf-16 encoded databases as well.

-----//----- sqlsize.c -----//-----
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

#ifndef SQLITE_PRIVATE
    #define SQLITE_PRIVATE static
#endif

static inline sqlite_int64 _varIntSize_(sqlite_int64 v)
{
    sqlite_int64 uu;

    if (v<0)
        uu = ~v;
    else
        uu = v;
    if (uu <= 127 )
        return 1;
    else if (uu <= 32767)
        return 2;
    else if (uu <= 8388607)
        return 3;
    else if (uu <= 2147483647)
        return 4;
    else if (uu <= 140737488355327LL)
        return 6;
    else return 8;
}

SQLITE_PRIVATE void _recSizeFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    sqlite_int64 maxsize = 0;
    sqlite_int64 sz;
    int i;

    for (i=0; i<argc; i++)
    {
        switch (sqlite3_value_type(argv[i]))
        {
            case SQLITE_NULL:
                maxsize += 1;
                break;
            case SQLITE_INTEGER:
                maxsize += _varIntSize_(sqlite3_value_int64(argv[i])) + 1;
                break;
            case SQLITE_FLOAT:
                maxsize += 9;
                break;
            case SQLITE_TEXT:
                sqlite3_value_blob(argv[i]);
                sz = sqlite3_value_bytes(argv[i]);
                maxsize += sz + _varIntSize_(sz*2+12);
                break;
            case SQLITE_BLOB:
                sz = sqlite3_value_bytes(argv[i]);
                maxsize += sz + _varIntSize_(sz*2+13);
                break;
        }
    }
    sqlite3_result_int64(context, maxsize);
}

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_sqlsize_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    SQLITE_EXTENSION_INIT2(pApi);

    return sqlite3_create_function(db, "recsize", -1, SQLITE_ANY|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS,  0, _recSizeFunc, 0, 0);
}
-----//-----

It is a bit of a PITA to call, but thats how the cookie crumbles ...

>sqlite3 \data\apps\splunk\splunk.db
SQLite version 3.31.0 2020-01-27 11:17:22
Enter ".help" for usage hints.
sqlite> .schema details
CREATE TABLE Details
(
    ApplianceID     integer not null references Appliances(ApplianceID) on delete cascade,
    SrcIntID        integer not null references Interfaces(InterfaceID) on delete cascade,
    DstIntID        integer not null references Interfaces(InterfaceID) on delete cascade,
    Transport       text not null collate nocase,
    SrcHostID       integer not null references Hosts(HostID) on delete cascade,
    SrcPort         integer not null,
    DstHostID       integer not null references Hosts(HostID) on delete cascade,
    DstPort         integer not null,
    Action          integer not null,
    Count           integer not null,
    FileID          integer not null references Files(FileID) on delete cascade
);
sqlite> select _rowid_, recsize(ApplianceID, SrcIntID, DstIntID, Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID) from details limit 10;
1|27
2|27
3|27
4|27
5|27
6|28
7|27
8|27
9|28
10|27
sqlite> select max(recsize(ApplianceID, SrcIntID, DstIntID, Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
31
sqlite> select avg(recsize(ApplianceID, SrcIntID, DstIntID, Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
29.4473800800817
sqlite> select min(recsize(ApplianceID, SrcIntID, DstIntID, Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
26
sqlite> select skew(recsize(ApplianceID, SrcIntID, DstIntID, Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
-0.378384651017371
sqlite> select kurt(recsize(ApplianceID, SrcIntID, DstIntID, Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
0.130516904446944

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Deon Brewis
>Sent: Friday, 24 January, 2020 21:14
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Row length in SQLITE
>
>No I mean e.g.
>
>row 1 = 500 bytes,
>row 2 = 600 bytes
>row 3 = 80 bytes
>row 4 = 300 bytes
>
>etc.
>
>Like the info that DBSTAT gives, but per row, not per page. This doesn't
>need to be performant - it's for usage analysis during development time.
>
>- Deon
>
>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Simon Slavin
>Sent: Wednesday, January 22, 2020 5:24 PM
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Row length in SQLITE
>
>On 22 Jan 2020, at 11:44pm, Deon Brewis <[hidden email]> wrote:
>
>> Is there any way to get the length of rows in a table / index in
>sqlite?
>
>Do you mean the count of rows in a table / index ?
>
>   SELECT count(*) FROM MyTable
>
>There's no easy fast way to do this because SQLite doesn't keep that
>number handy anywhere.  It stores the entries in a tree and it would have
>to manually count the leaves of the tree.
>
>----
>
>Or do you mean the count of columns in a table / index ?
>
>    SELECT * FROM MyTable LIMIT 1
>
>and count the number of columns returned.  Or in C
>
>    sqlite3_column_count()
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [EXTERNAL] Re: Row length in SQLITE

Hick Gunter
You are missing

maxsize += _varIntSize_(maxsize)

fort he size varint at the begin oft he header just before the return

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Keith Medcalf
Gesendet: Montag, 27. Januar 2020 12:43
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Row length in SQLITE


Here is a wee bit of C code that you can compile as a plugin that will give you the row size (well, it may be bigger than the actual record size by a few bytes but it is pretty close) ...

works properly for utf-16 encoded databases as well.

-----//----- sqlsize.c -----//-----
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

#ifndef SQLITE_PRIVATE
    #define SQLITE_PRIVATE static
#endif

static inline sqlite_int64 _varIntSize_(sqlite_int64 v) {
    sqlite_int64 uu;

    if (v<0)
        uu = ~v;
    else
        uu = v;
    if (uu <= 127 )
        return 1;
    else if (uu <= 32767)
        return 2;
    else if (uu <= 8388607)
        return 3;
    else if (uu <= 2147483647)
        return 4;
    else if (uu <= 140737488355327LL)
        return 6;
    else return 8;
}

SQLITE_PRIVATE void _recSizeFunc(sqlite3_context *context, int argc, sqlite3_value **argv) {
    sqlite_int64 maxsize = 0;
    sqlite_int64 sz;
    int i;

    for (i=0; i<argc; i++)
    {
        switch (sqlite3_value_type(argv[i]))
        {
            case SQLITE_NULL:
                maxsize += 1;
                break;
            case SQLITE_INTEGER:
                maxsize += _varIntSize_(sqlite3_value_int64(argv[i])) + 1;
                break;
            case SQLITE_FLOAT:
                maxsize += 9;
                break;
            case SQLITE_TEXT:
                sqlite3_value_blob(argv[i]);
                sz = sqlite3_value_bytes(argv[i]);
                maxsize += sz + _varIntSize_(sz*2+12);
                break;
            case SQLITE_BLOB:
                sz = sqlite3_value_bytes(argv[i]);
                maxsize += sz + _varIntSize_(sz*2+13);
                break;
        }
    }
    sqlite3_result_int64(context, maxsize); }

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_sqlsize_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
    SQLITE_EXTENSION_INIT2(pApi);

    return sqlite3_create_function(db, "recsize", -1, SQLITE_ANY|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS,  0, _recSizeFunc, 0, 0); }
-----//-----

It is a bit of a PITA to call, but thats how the cookie crumbles ...

>sqlite3 \data\apps\splunk\splunk.db
SQLite version 3.31.0 2020-01-27 11:17:22 Enter ".help" for usage hints.
sqlite> .schema details
CREATE TABLE Details
(
    ApplianceID     integer not null references Appliances(ApplianceID) on delete cascade,
    SrcIntID        integer not null references Interfaces(InterfaceID) on delete cascade,
    DstIntID        integer not null references Interfaces(InterfaceID) on delete cascade,
    Transport       text not null collate nocase,
    SrcHostID       integer not null references Hosts(HostID) on delete cascade,
    SrcPort         integer not null,
    DstHostID       integer not null references Hosts(HostID) on delete cascade,
    DstPort         integer not null,
    Action          integer not null,
    Count           integer not null,
    FileID          integer not null references Files(FileID) on delete cascade
);
sqlite> select _rowid_, recsize(ApplianceID, SrcIntID, DstIntID,
sqlite> Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action,
sqlite> Count, FileID) from details limit 10;
1|27
2|27
3|27
4|27
5|27
6|28
7|27
8|27
9|28
10|27
sqlite> select max(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
31
sqlite> select avg(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
29.4473800800817
sqlite> select min(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
26
sqlite> select skew(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
-0.378384651017371
sqlite> select kurt(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
sqlite> from details;
0.130516904446944

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Deon Brewis
>Sent: Friday, 24 January, 2020 21:14
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Row length in SQLITE
>
>No I mean e.g.
>
>row 1 = 500 bytes,
>row 2 = 600 bytes
>row 3 = 80 bytes
>row 4 = 300 bytes
>
>etc.
>
>Like the info that DBSTAT gives, but per row, not per page. This
>doesn't need to be performant - it's for usage analysis during development time.
>
>- Deon
>
>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Simon Slavin
>Sent: Wednesday, January 22, 2020 5:24 PM
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Row length in SQLITE
>
>On 22 Jan 2020, at 11:44pm, Deon Brewis <[hidden email]> wrote:
>
>> Is there any way to get the length of rows in a table / index in
>sqlite?
>
>Do you mean the count of rows in a table / index ?
>
>   SELECT count(*) FROM MyTable
>
>There's no easy fast way to do this because SQLite doesn't keep that
>number handy anywhere.  It stores the entries in a tree and it would
>have to manually count the leaves of the tree.
>
>----
>
>Or do you mean the count of columns in a table / index ?
>
>    SELECT * FROM MyTable LIMIT 1
>
>and count the number of columns returned.  Or in C
>
>    sqlite3_column_count()
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Row length in SQLITE

Keith Medcalf
In reply to this post by Deon Brewis

Except that should be for the header bytes only.  It is somewhat inaccurate because IEEE doubles may be stored as varints and values 0 and 1 may be stored as just the header code 8 or 9 without storing the actual varint (if the schema version is 4 or more, which cannot be read in an extension, though I suppose the context points to the connection which points to the schema which somewhere along the way will have the schema version, though those pointers are supposed to be opaque).  Plus of course that size is the size of the header + the size of the varint storing the size of the header all as a varint ...

However, assuming schema type 4 then the following is more accurate.  Though if the column has no affinity then not all the optimizations are applied.

SQLITE_PRIVATE void _recSizeFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    sqlite_int64 hdrsize = 0;
    sqlite_int64 datsize = 0;
    sqlite_int64 sz;
    double v;
    int i;

    for (i=0; i<argc; i++)
    {
        switch (sqlite3_value_type(argv[i]))
        {
            case SQLITE_NULL:
                hdrsize += 1;
                break;
            case SQLITE_INTEGER:
                hdrsize += 1;
                sz = sqlite3_value_int64(argv[i]);
                if ((sz < 0) || (sz > 1))
                    datsize += _varIntSize_(sqlite3_value_int64(argv[i]));
                break;
            case SQLITE_FLOAT:
                hdrsize += 1;
                v = sqlite3_value_double(argv[i]);
                if ((fabs(v) <= 140737488355327.0) && (trunc(v) == v))
                {
                    if ((v < 0) || (v > 1))
                        datsize += _varIntSize_((sqlite_int64)v);
                }
                else
                    datsize += 8;
                break;
            case SQLITE_TEXT:
                sqlite3_value_blob(argv[i]);
                sz = sqlite3_value_bytes(argv[i]);
                hdrsize += _varIntSize_(sz*2+12);
                datsize += sz;
                break;
            case SQLITE_BLOB:
                sz = sqlite3_value_bytes(argv[i]);
                hdrsize += _varIntSize_(sz*2+13);
                datsize += sz;
                break;
        }
    }
    sqlite3_result_int64(context, _varIntSize_(hdrsize +_varIntSize_(hdrsize)) + hdrsize + datsize);
}


--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Hick Gunter
>Sent: Monday, 27 January, 2020 05:32
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] [EXTERNAL] Re: Row length in SQLITE
>
>You are missing
>
>maxsize += _varIntSize_(maxsize)
>
>fort he size varint at the begin oft he header just before the return
>
>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [mailto:[hidden email]]
>Im Auftrag von Keith Medcalf
>Gesendet: Montag, 27. Januar 2020 12:43
>An: SQLite mailing list <[hidden email]>
>Betreff: [EXTERNAL] Re: [sqlite] Row length in SQLITE
>
>
>Here is a wee bit of C code that you can compile as a plugin that will
>give you the row size (well, it may be bigger than the actual record size
>by a few bytes but it is pretty close) ...
>
>works properly for utf-16 encoded databases as well.
>
>-----//----- sqlsize.c -----//-----
>#include "sqlite3ext.h"
>SQLITE_EXTENSION_INIT1
>
>#ifndef SQLITE_PRIVATE
>    #define SQLITE_PRIVATE static
>#endif
>
>static inline sqlite_int64 _varIntSize_(sqlite_int64 v) {
>    sqlite_int64 uu;
>
>    if (v<0)
>        uu = ~v;
>    else
>        uu = v;
>    if (uu <= 127 )
>        return 1;
>    else if (uu <= 32767)
>        return 2;
>    else if (uu <= 8388607)
>        return 3;
>    else if (uu <= 2147483647)
>        return 4;
>    else if (uu <= 140737488355327LL)
>        return 6;
>    else return 8;
>}
>
>SQLITE_PRIVATE void _recSizeFunc(sqlite3_context *context, int argc,
>sqlite3_value **argv) {
>    sqlite_int64 maxsize = 0;
>    sqlite_int64 sz;
>    int i;
>
>    for (i=0; i<argc; i++)
>    {
>        switch (sqlite3_value_type(argv[i]))
>        {
>            case SQLITE_NULL:
>                maxsize += 1;
>                break;
>            case SQLITE_INTEGER:
>                maxsize += _varIntSize_(sqlite3_value_int64(argv[i])) +
>1;
>                break;
>            case SQLITE_FLOAT:
>                maxsize += 9;
>                break;
>            case SQLITE_TEXT:
>                sqlite3_value_blob(argv[i]);
>                sz = sqlite3_value_bytes(argv[i]);
>                maxsize += sz + _varIntSize_(sz*2+12);
>                break;
>            case SQLITE_BLOB:
>                sz = sqlite3_value_bytes(argv[i]);
>                maxsize += sz + _varIntSize_(sz*2+13);
>                break;
>        }
>    }
>    sqlite3_result_int64(context, maxsize); }
>
>#ifdef _WIN32
>#ifndef SQLITE_CORE
>__declspec(dllexport)
>#endif
>#endif
>int sqlite3_sqlsize_init(sqlite3 *db, char **pzErrMsg, const
>sqlite3_api_routines *pApi) {
>    SQLITE_EXTENSION_INIT2(pApi);
>
>    return sqlite3_create_function(db, "recsize", -1,
>SQLITE_ANY|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS,  0, _recSizeFunc, 0,
>0); }
>-----//-----
>
>It is a bit of a PITA to call, but thats how the cookie crumbles ...
>
>>sqlite3 \data\apps\splunk\splunk.db
>SQLite version 3.31.0 2020-01-27 11:17:22 Enter ".help" for usage hints.
>sqlite> .schema details
>CREATE TABLE Details
>(
>    ApplianceID     integer not null references Appliances(ApplianceID)
>on delete cascade,
>    SrcIntID        integer not null references Interfaces(InterfaceID)
>on delete cascade,
>    DstIntID        integer not null references Interfaces(InterfaceID)
>on delete cascade,
>    Transport       text not null collate nocase,
>    SrcHostID       integer not null references Hosts(HostID) on delete
>cascade,
>    SrcPort         integer not null,
>    DstHostID       integer not null references Hosts(HostID) on delete
>cascade,
>    DstPort         integer not null,
>    Action          integer not null,
>    Count           integer not null,
>    FileID          integer not null references Files(FileID) on delete
>cascade
>);
>sqlite> select _rowid_, recsize(ApplianceID, SrcIntID, DstIntID,
>sqlite> Transport, SrcHostID, SrcPort, DstHostID, DstPort, Action,
>sqlite> Count, FileID) from details limit 10;
>1|27
>2|27
>3|27
>4|27
>5|27
>6|28
>7|27
>8|27
>9|28
>10|27
>sqlite> select max(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
>sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
>sqlite> from details;
>31
>sqlite> select avg(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
>sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
>sqlite> from details;
>29.4473800800817
>sqlite> select min(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
>sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
>sqlite> from details;
>26
>sqlite> select skew(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
>sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
>sqlite> from details;
>-0.378384651017371
>sqlite> select kurt(recsize(ApplianceID, SrcIntID, DstIntID, Transport,
>sqlite> SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID))
>sqlite> from details;
>0.130516904446944
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-----Original Message-----
>>From: sqlite-users <[hidden email]> On
>>Behalf Of Deon Brewis
>>Sent: Friday, 24 January, 2020 21:14
>>To: SQLite mailing list <[hidden email]>
>>Subject: Re: [sqlite] Row length in SQLITE
>>
>>No I mean e.g.
>>
>>row 1 = 500 bytes,
>>row 2 = 600 bytes
>>row 3 = 80 bytes
>>row 4 = 300 bytes
>>
>>etc.
>>
>>Like the info that DBSTAT gives, but per row, not per page. This
>>doesn't need to be performant - it's for usage analysis during
>development time.
>>
>>- Deon
>>
>>-----Original Message-----
>>From: sqlite-users <[hidden email]> On
>>Behalf Of Simon Slavin
>>Sent: Wednesday, January 22, 2020 5:24 PM
>>To: SQLite mailing list <[hidden email]>
>>Subject: Re: [sqlite] Row length in SQLITE
>>
>>On 22 Jan 2020, at 11:44pm, Deon Brewis <[hidden email]> wrote:
>>
>>> Is there any way to get the length of rows in a table / index in
>>sqlite?
>>
>>Do you mean the count of rows in a table / index ?
>>
>>   SELECT count(*) FROM MyTable
>>
>>There's no easy fast way to do this because SQLite doesn't keep that
>>number handy anywhere.  It stores the entries in a tree and it would
>>have to manually count the leaves of the tree.
>>
>>----
>>
>>Or do you mean the count of columns in a table / index ?
>>
>>    SELECT * FROM MyTable LIMIT 1
>>
>>and count the number of columns returned.  Or in C
>>
>>    sqlite3_column_count()
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>___________________________________________
> Gunter Hick | Software Engineer | Scientific Games International GmbH |
>Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 |
>(O) +43 1 80100 - 0
>
>May be privileged. May be confidential. Please delete if not the
>addressee.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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