Well, rough calculations indicate that the situation that sqlite preallocates about an additional 3 rows every time a new ROWID is assigned (on the fairly common use case that ROWID is always monotonically increasing). Assuming that this corner case is not accounted for, it could be that when assigning randomly or in decreasing order, the additional rows are kept around but unassigned, resulting in unused space.

Without actually looking into the internals or profiling the code, this is just a guess and I would recommend that someone better familiar with the internals comment on this topic.

Scott Hess wrote:
Could it be that you're seeing the btree optimization around in-order
insertion? From btree.c:

#ifndef SQLITE_OMIT_QUICKBALANCE
  /*
  ** A special case:  If a new entry has just been inserted into a
  ** table (that is, a btree with integer keys and all data at the leaves)
  ** and the new entry is the right-most entry in the tree (it has the
  ** largest key) then use the special balance_quick() routine for
  ** balancing.  balance_quick() is much faster and results in a tighter
  ** packing of data in the common case.
  */

-scott


On 10/16/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
Dear all,

I'll reframe the question again, If ROWID(hidden column/b-tree key/internal
to all table) is changed manually, means I'll insert some unique values in
random order / in descending order (say from 10000 to 1), the memory
occupied increases more. why? I observed entire table is getting sorted
based on ROWID.

My results were, for 10000 records if it is normally inserted (ie., not
modifying ROWID) it takes 500KB. The same thing if I modify the ROWID it
consumes 1.5MB. why?

Is that all the columns are indexed or only ROWID is indexed?

Memory measurement is done by sqlite3GenericMalloc using
SQLITE_ENABLE_MEMORY_MANAGEMENT macro enabled. Calculating each allocation
size gives total memory allocation.

Thanks.


On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote:
My question is how you're measuring the memory useage?  Are you
accounting for the space overhead of the various bookkeeping sqlite
needs (i.e. master table)?  The way you're creating you're table implies
you're not using autoincrement for the integer field - are you
accounting for the extra internal row id column?

Joe Wilson wrote:
It could be malloc fragmentation.

Which sqlite version, operating system, and malloc implementation are
you using?
--- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote:

Say I have 3 columns in one Table, with one INTEGER, two TEXT columns,
If
ROWID is manually inserted and made descending for 10000 records from
10000
to 1, (or even if random number for ROWID - both these cases), the
memory
occupied is more. Why is this so?




____________________________________________________________________________________
Be a better Globetrotter. Get better travel answers from someone who
knows. Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&sid=396545469


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]

-----------------------------------------------------------------------------



-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------



-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to