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; isqlite3 \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,
SrcIntIDinteger not null references Interfaces(InterfaceID) on
delete cascade,
DstIntIDinteger 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 On
>Behalf Of Deon Brewis
>Sent: Friday, 24 January, 2020 21:14
>To: SQLite mailing list
>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 On
>Behalf Of Simon Slavin
>Sent: Wednesday, January 22, 2020 5:24 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Row length in SQLITE
>
>On 22 Jan 2020, at 11:44pm, Deon Brewis 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Row length in SQLITE
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, SrcIntIDinteger not null references Interfaces(InterfaceID) on delete cascade, DstIntIDinteger 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 -- -- -- -- -- -- -- -- -- mainDetails All 1160835127 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 On >Behalf Of Deon Brewis >Sent: Friday, 24 January, 2020 21:14 >To: SQLite mailing list >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 On >Behalf Of Simon Slavin >Sent: Wednesday, January 22, 2020 5:24 PM >To: SQLite mailing list >Subject: Re: [sqlite] Row length in SQLITE > >On 22 Jan 2020, at 11:44pm, Deon Brewis 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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 On Behalf Of Simon Slavin Sent: Wednesday, January 22, 2020 5:24 PM To: SQLite mailing list Subject: Re: [sqlite] Row length in SQLITE On 22 Jan 2020, at 11:44pm, Deon Brewis 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Row length in SQLITE
On 22 Jan 2020, at 11:44pm, Deon Brewis 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
