sorry I forgot to mention the sqlite version that I'm using, its SQLite
3.3.8.
Below is the sample code that I tried,
static char *database_name = ":memory:";
static sqlite3* db_handle;
#define PRINT_TIME \
{ \
unsigned long millisec = clock(); \
printf("milliseconds = %ld\n", millisec); \
}
static const char *TestSqlStats[] = {
"CREATE TABLE testTbl (t_id INTEGER, t_name TEXT);",
"INSERT INTO testTbl (ROWID,t_id,t_name) VALUES (:A,:B,:C);",
};
int main(int argc, char *argv[])
{
char *zErrMsg = NULL;
int status, i = 0;
/* Open database */
status = sqlite3_open(database_name, &db_handle);
/* Create Table */
status = sqlite3_exec(db_handle, "BEGIN;", NULL, NULL, NULL);
status = sqlite3_exec(db_handle, TestSqlStats[0], NULL, NULL, NULL);
PRINT_TIME
{
static sqlite3_stmt *MyInsertStmt = NULL;
status = sqlite3_prepare(db_handle, TestSqlStats[1], -1,
&MyInsertStmt, (void*)0);
for (i = 10000; i > 0; i--)
{
status = sqlite3_bind_int(MyInsertStmt, 1, i);
status = sqlite3_bind_int(MyInsertStmt, 2, i);
status = sqlite3_bind_text(MyInsertStmt, 3, "It's a bad
statement", -1, SQLITE_TRANSIENT);
status = sqlite3_step(MyInsertStmt);
status = sqlite3_reset(MyInsertStmt);
}
status = sqlite3_finalize(MyInsertStmt);
}
PRINT_TIME
}
These are the test results on VC++6.0 on Pentium 4 and 3GHz Processor with
1GB RAM.
with descending order
milliseconds = 0
milliseconds = 672
Total Memory Consumed = 626040
1 It's a bad statement
2 It's a bad statement
3 It's a bad statement
4 It's a bad statement
5 It's a bad statement
Press any key to continue
with ascending order
milliseconds = 0
milliseconds = 234
Total Memory Consumed = 328960
0 It's a bad statement
1 It's a bad statement
2 It's a bad statement
3 It's a bad statement
4 It's a bad statement
Press any key to continue
>From the above results, the time & space complexity has been almost doubled
when ROWID is modified with another unique value or if inserted in
descending order.
Is there any way in which I can overcome this? or is it corrected in higher
version, before trying higher version if anybody already tried please reply.
Scott -- thanks for the info, I tried enabling the macro
SQLITE_OMIT_QUICKBALANCE, it didn't effect anything.
There are some TODO's comment which may not be implemented. Please correct
me if I'm wrong.
Thanks,
-Lokee
On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote:
>
> 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]
>
> -----------------------------------------------------------------------------
>
>
