table or record size without reading the entire table/record?

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

table or record size without reading the entire table/record?

Kervin L. Pierre
Hello,

I am trying to figure out a way to return
the actual or approximate table size in a
database.

Basically, in my application a table relates
to an object and I need to calculate the
approximate size of an object.

There is the sqlite3_column_bytes(), but I
understand that that function returns the
value into memory before counting the bytes.

Is there another way of estimating a table's
size on disk?

Thanks,
Kervin
Reply | Threaded
Open this post in threaded view
|

Re: table or record size without reading the entire table/record?

D. Richard Hipp
On Sun, 2005-08-21 at 13:43 -0400, Kervin L. Pierre wrote:

> Hello,
>
> I am trying to figure out a way to return
> the actual or approximate table size in a
> database.
>
> Basically, in my application a table relates
> to an object and I need to calculate the
> approximate size of an object.
>
> There is the sqlite3_column_bytes(), but I
> understand that that function returns the
> value into memory before counting the bytes.
>
> Is there another way of estimating a table's
> size on disk?
>

Are you trying to estimate the size of a table,
or a single row in that table?  Your words say
the table but the context suggest you really
want the size of a row.

No, there is no way to determine the size of
a row in SQLite without loading the whole row
into memory.  But if you really need to know
the row size before loading it, you can store
that value in a separate column in the table.

  CREATE TABLE object(
    size INT,
    data BLOB
  );

Then when you need to know the size, just do
a query for the size.

Note:  This will only work if the "size" column
comes before the "data" column in the table
definition.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: table or record size without reading the entire table/record?

Kervin L. Pierre
D. Richard Hipp wrote:
>
> Are you trying to estimate the size of a table,
> or a single row in that table?  Your words say
> the table but the context suggest you really
> want the size of a row.
>

I need the table.  But I thought if I could
have the row, calculating the total for the
table wouldn't be difficult.

>
> Then when you need to know the size, just do
> a query for the size.
>

Thanks.  That was my backup plan if there was
no other way.

> Note:  This will only work if the "size" column
> comes before the "data" column in the table
> definition.

Thanks for the heads up.  Out of curiosity,
why does the size column have to come before
the data column?

Regards,
Kervin



Reply | Threaded
Open this post in threaded view
|

Re: table or record size without reading the entire table/record?

D. Richard Hipp
On Sun, 2005-08-21 at 14:26 -0400, Kervin L. Pierre wrote:
> > Note:  This will only work if the "size" column
> > comes before the "data" column in the table
> > definition.
>
> Thanks for the heads up.  Out of curiosity,
> why does the size column have to come before
> the data column?
>

When reading information from the disk, SQLite starts
at the beginning of a row and reads till it finds the
information it needs.  If the size comes first, it will
therefore stop after reading the size and ignore the
data.  If the data comes first, it will have to read
all of the data in order to determine the size, which
defeats the purpose of putting the size column in the
table in the first place.
--
D. Richard Hipp <[hidden email]>