/ Check-in [dc37750d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a bug in sqlite_analyzer causing it report (slightly) incorrect values for the number of entries in indexes or WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dc37750d4e87d0c529785adceeebd838b8f8591d
User & Date: dan 2016-05-06 15:16:02
Context
2016-05-06
16:30
Fix an error in main.mk causing testfixture to use individual fts5 source code files instead of fts5.c. check-in: 933a1c81 user: dan tags: trunk
15:16
Fix a bug in sqlite_analyzer causing it report (slightly) incorrect values for the number of entries in indexes or WITHOUT ROWID tables. check-in: dc37750d user: dan tags: trunk
2016-05-05
11:53
Renumber internal constants in the printf() implemention for a small performance improvement. check-in: 69d11447 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to tool/spaceanal.tcl.

   148    148   # queries the in-memory db to produce the space-analysis report.
   149    149   #
   150    150   sqlite3 mem :memory:
   151    151   set tabledef {CREATE TABLE space_used(
   152    152      name clob,        -- Name of a table or index in the database file
   153    153      tblname clob,     -- Name of associated table
   154    154      is_index boolean, -- TRUE if it is an index, false for a table
          155  +   is_without_rowid boolean, -- TRUE if WITHOUT ROWID table  
   155    156      nentry int,       -- Number of entries in the BTree
   156    157      leaf_entries int, -- Number of leaf entries
   157    158      depth int,        -- Depth of the b-tree
   158    159      payload int,      -- Total amount of data stored in this table or index
   159    160      ovfl_payload int, -- Total amount of data stored on overflow pages
   160    161      ovfl_cnt int,     -- Number of entries that use overflow
   161    162      mx_payload int,   -- Maximum payload size
................................................................................
   180    181   set isCompressed 0
   181    182   set compressOverhead 0
   182    183   set depth 0
   183    184   set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 }
   184    185   foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] {
   185    186   
   186    187     set is_index [expr {$name!=$tblname}]
   187         -  set idx_btree [expr {$is_index || [is_without_rowid $name]}]
          188  +  set is_without_rowid [is_without_rowid $name]
   188    189     db eval {
   189    190       SELECT 
   190    191         sum(ncell) AS nentry,
   191    192         sum((pagetype=='leaf')*ncell) AS leaf_entries,
   192    193         sum(payload) AS payload,
   193    194         sum((pagetype=='overflow') * payload) AS ovfl_payload,
   194    195         sum(path LIKE '%+000000') AS ovfl_cnt,
................................................................................
   231    232       set prev $pageno
   232    233     }
   233    234     mem eval {
   234    235       INSERT INTO space_used VALUES(
   235    236         $name,
   236    237         $tblname,
   237    238         $is_index,
          239  +      $is_without_rowid,
   238    240         $nentry,
   239    241         $leaf_entries,
   240    242         $depth,
   241    243         $payload,     
   242    244         $ovfl_payload,
   243    245         $ovfl_cnt,   
   244    246         $mx_payload,
................................................................................
   326    328   
   327    329     # Query the in-memory database for the sum of various statistics 
   328    330     # for the subset of tables/indices identified by the WHERE clause in
   329    331     # $where. Note that even if the WHERE clause matches no rows, the
   330    332     # following query returns exactly one row (because it is an aggregate).
   331    333     #
   332    334     # The results of the query are stored directly by SQLite into local 
   333         -  # variables (i.e. $nentry, $nleaf etc.).
          335  +  # variables (i.e. $nentry, $payload etc.).
   334    336     #
   335    337     mem eval "
   336    338       SELECT
   337         -      int(sum(nentry)) AS nentry,
   338         -      int(sum(leaf_entries)) AS nleaf,
          339  +      int(sum(
          340  +        CASE WHEN (is_without_rowid OR is_index) THEN nentry 
          341  +             ELSE leaf_entries 
          342  +        END
          343  +      )) AS nentry,
   339    344         int(sum(payload)) AS payload,
   340    345         int(sum(ovfl_payload)) AS ovfl_payload,
   341    346         max(mx_payload) AS mx_payload,
   342    347         int(sum(ovfl_cnt)) as ovfl_cnt,
   343    348         int(sum(leaf_pages)) AS leaf_pages,
   344    349         int(sum(int_pages)) AS int_pages,
   345    350         int(sum(ovfl_pages)) AS ovfl_pages,
................................................................................
   371    376     # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
   372    377     #
   373    378     set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
   374    379     set total_pages_percent [percent $total_pages $file_pgcnt]
   375    380     set storage [expr {$total_pages*$pageSize}]
   376    381     set payload_percent [percent $payload $storage {of storage consumed}]
   377    382     set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}]
   378         -  set avg_payload [divide $payload $nleaf]
   379         -  set avg_unused [divide $total_unused $nleaf]
          383  +  set avg_payload [divide $payload $nentry]
          384  +  set avg_unused [divide $total_unused $nentry]
   380    385     if {$int_pages>0} {
   381    386       # TODO: Is this formula correct?
   382    387       set nTab [mem eval "
   383    388         SELECT count(*) FROM (
   384    389             SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
   385    390         )
   386    391       "]
   387    392       set avg_fanout [mem eval "
   388    393         SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used
   389    394             WHERE $where
   390    395       "]
   391    396       set avg_fanout [format %.2f $avg_fanout]
   392    397     }
   393         -  set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}]
          398  +  set ovfl_cnt_percent [percent $ovfl_cnt $nentry {of all entries}]
   394    399   
   395    400     # Print out the sub-report statistics.
   396    401     #
   397    402     statline {Percentage of total database} $total_pages_percent
   398         -  statline {Number of entries} $nleaf
          403  +  statline {Number of entries} $nentry
   399    404     statline {Bytes of storage consumed} $storage
   400    405     if {$compressed_size!=$storage} {
   401    406       set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}]
   402    407       set pct [expr {$compressed_size*100.0/$storage}]
   403    408       set pct [format {%5.1f%%} $pct]
   404    409       statline {Bytes used after compression} $compressed_size $pct
   405    410     }