Re: [sqlite] How much a table takes (will this query always work)

[Max Vlasov]

On Fri, Jul 16, 2010 at 5:14 PM, Jim Wilcoxson  wrote:

> >>
> > You mean, like this?
> >
> > pragma page_count
> > pragma page_size
> > size = page_count*page_size
> >
> >
> Doh.  Nevermind.  I see you said table, not database. :)
>
>
If I'm asked, the syntax PRAGMA page_count(table_name/index_name) would be
great if such function could be included in future versions. And the old
syntax without names would work as before

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much a table takes (will this query always work)

[Max Vlasov]

On Fri, Jul 16, 2010 at 4:52 PM, Jay A. Kreibich  wrote:

>
>  Check out sqlite3_analyzer.  This is available in binary format only
>  on the SQLite website.   http://sqlite.org/download.html
>
>
Thanks, the tool is really great, but just wondering can the code used there
be part of another project in c language.  As long as I see from the search
result the source code for this functionality is spaceanal.tcl. I tried to
find the method used and it seems that sqllite tcl extension has a special
way of accessing btrees directly. So in this case I probably should find the
corresponding functions in the c source and make them public, right?

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much a table takes (will this query always work)

[Jim Wilcoxson]

On Fri, Jul 16, 2010 at 9:13 AM, Jim Wilcoxson  wrote:

> On Fri, Jul 16, 2010 at 6:01 AM, Max Vlasov  wrote:
>
>> Hi,
>>
>> always wanted to have a possibility to calculate how much a table
>> occupies.
>> As long as I see from the archive, there's no out-of-the-box solution
>> (CMIIW)
>>
>>
> You mean, like this?
>
> pragma page_count
> pragma page_size
> size = page_count*page_size
>
>
Doh.  Nevermind.  I see you said table, not database. :)

JIm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much a table takes (will this query always work)

[Jim Wilcoxson]

On Fri, Jul 16, 2010 at 6:01 AM, Max Vlasov  wrote:

> Hi,
>
> always wanted to have a possibility to calculate how much a table occupies.
> As long as I see from the archive, there's no out-of-the-box solution
> (CMIIW)
>
>
You mean, like this?

pragma page_count
pragma page_size
size = page_count*page_size

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much a table takes (will this query always work)

[Jay A. Kreibich]

On Fri, Jul 16, 2010 at 02:01:27PM +0400, Max Vlasov scratched on the wall:
> Hi,
> 
> always wanted to have a possibility to calculate how much a table occupies.
> As long as I see from the archive, there's no out-of-the-box solution
> (CMIIW)

  Check out sqlite3_analyzer.  This is available in binary format only
  on the SQLite website.   http://sqlite.org/download.html

  In addition to a large number of database file stats, it provides the
  following information for every table and index:

   name clob,-- Name of a table or index in the database file
   tblname clob, -- Name of associated table
   is_index boolean, -- TRUE if it is an index, false for a table
   nentry int,   -- Number of entries in the BTree
   leaf_entries int, -- Number of leaf entries
   payload int,  -- Total amount of data stored in this table or index
   ovfl_payload int, -- Total amount of data stored on overflow pages
   ovfl_cnt int, -- Number of entries that use overflow
   mx_payload int,   -- Maximum payload size
   int_pages int,-- Number of interior pages used
   leaf_pages int,   -- Number of leaf pages used
   ovfl_pages int,   -- Number of overflow pages used
   int_unused int,   -- Number of unused bytes on interior pages
   leaf_unused int,  -- Number of unused bytes on primary pages
   ovfl_unused int,  -- Number of unused bytes on overflow pages
   gap_cnt int   -- Number of gaps in the page layout



> Recently I made a query that probably solves this, but it has some
> limitations and there are still issues unsolved.

> - Are there cases when a wrong result possible with ideal conditions (no
> temp database before)?

  The reported size is likely smaller than the table, as it sits in the
  original database.  This technique defragements and re-packs the
  pages, not unlike the VACUUM command.  It should give you pretty good
  numbers for the VACUUMed size, but not for the current size.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How much a table takes (will this query always work)

[Max Vlasov]

Hi,

always wanted to have a possibility to calculate how much a table occupies.
As long as I see from the archive, there's no out-of-the-box solution
(CMIIW)

Recently I made a query that probably solves this, but it has some
limitations and there are still issues unsolved.

So, let's TableToCheck is table we want to know about.

-

CREATE TEMP TABLE [tblsizetemptable1] AS SELECT * FROM TableToCheck;
CREATE TEMP TABLE [tblsizetemptable2] ([Id] INTEGER PRIMARY KEY);
SELECT (SELECT rootpage FROM sqlite_temp_master  WHERE
tbl_name='tblsizetemptable2')-
(SELECT rootpage FROM sqlite_temp_master  WHERE
tbl_name='tblsizetemptable1');

The result can be used as a page count size or be multiplied with the result
of PRAGMA page_size;

After that we should drop the tables
DROP TABLE tblsizetemptable2;
DROP TABLE tblsizetemptable1;

I hope these queries are self-explanatory
-

So the questions and problems:
- Is it possible to create a similar query for indexes. I mean the one, in
what I only have to change the name of the index. As long as I understand
there's no syntax looking like CREATE INDEX ... AS
- If there are temp tables exist, the temp database file might be
fragmented, so this method can give bad results. Is there something like
VACUUM for the temp database or a similar actions leading to the compacted
temp database?
- Are there cases when a wrong result possible with ideal conditions (no
temp database before)?
- Such method is not good for large databases since all the data should be
re-saved just to get the value. If someone suggest an algorithm solving
this, this will be great.

Thanks,

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users