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] > > ----------------------------------------------------------------------------- > >