Re: [sqlite] Explanation
On Feb 9, 2012, at 4:17 PM, Marco Bambini wrote: > sqlite 3.7.8 on MacOS X > > This query works fine: > SELECT rowid, * FROM 'customer' WHERE rowid < 100; > > While this query does not return anything: > SELECT rowid, * FROM 'customer' WHERE 'rowid' < 100; > > Do you have an explanation? perhaps because in the second query 'rowid' is being treated as a string and not a column. You might want to try "rowid" instead (double quotes). -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Explanation
sqlite 3.7.8 on MacOS X This query works fine: SELECT rowid, * FROM 'customer' WHERE rowid < 100; While this query does not return anything: SELECT rowid, * FROM 'customer' WHERE 'rowid' < 100; Do you have an explanation? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explanation
Yes, you are right (as always). Time with SQLITE_THREADSAFE=0 is about 4.33 seconds now. Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 28, 2009, at 5:23 PM, D. Richard Hipp wrote: > > On Aug 28, 2009, at 11:17 AM, Mike Eggleston wrote: > >> On Fri, 28 Aug 2009, Marco Bambini might have said: >>> >>> Version 3.4.2 takes about 5.06 seconds (average value) while version >>> 3.6.17 takes about 7.28 seconds (average value). >>> Could be a slowdown in the library for the complexity added over the >>> years or does someone have another possible explanation? > > Our measurements show a substantial performance improvement over > 3.4.2. > > Perhaps you are running in the default configuration, which has been > augmented with many new mutexes since version 3.4.2 in order to make > SQLite proof against over-zealous users of threads. If you recompile > with -DSQLITE_THREADSAFE=0, perhaps you will get your old performance > back. > > >>> >>> Thanks. >> >> Did you just relink your app or did you also migrate the data to a >> new >> sqlite3 database? I think the migration command is: >> >> echo '.dump' | sqlite3 $db | sqlite3 $dbnew >> >> Maybe the internal database structure has changed? > > The file-format is unchanged. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explanation
On Aug 28, 2009, at 11:17 AM, Mike Eggleston wrote: > On Fri, 28 Aug 2009, Marco Bambini might have said: >> >> Version 3.4.2 takes about 5.06 seconds (average value) while version >> 3.6.17 takes about 7.28 seconds (average value). >> Could be a slowdown in the library for the complexity added over the >> years or does someone have another possible explanation? Our measurements show a substantial performance improvement over 3.4.2. Perhaps you are running in the default configuration, which has been augmented with many new mutexes since version 3.4.2 in order to make SQLite proof against over-zealous users of threads. If you recompile with -DSQLITE_THREADSAFE=0, perhaps you will get your old performance back. >> >> Thanks. > > Did you just relink your app or did you also migrate the data to a new > sqlite3 database? I think the migration command is: > > echo '.dump' | sqlite3 $db | sqlite3 $dbnew > > Maybe the internal database structure has changed? The file-format is unchanged. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explanation
Library is statically linked into the final app and the db is newly created... -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 28, 2009, at 5:17 PM, Mike Eggleston wrote: > On Fri, 28 Aug 2009, Marco Bambini might have said: > >> Hello, >> >> today I made some test on a project I wrote some years ago. >> I upgraded sqlite library from version 3.4.2 to version 3.6.17. >> What I am really unable to understand is the time difference required >> to perform the same query using the exact same algorithm by the two >> libraries. >> >> SELECT * FROM table1 >> where table1 has 1 million rows and 10 columns (its an 80MB db). >> >> Version 3.4.2 takes about 5.06 seconds (average value) while version >> 3.6.17 takes about 7.28 seconds (average value). >> Could be a slowdown in the library for the complexity added over the >> years or does someone have another possible explanation? >> >> Thanks. > > Did you just relink your app or did you also migrate the data to a new > sqlite3 database? I think the migration command is: > > echo '.dump' | sqlite3 $db | sqlite3 $dbnew > > Maybe the internal database structure has changed? > > Mike > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explanation
On Fri, 28 Aug 2009, Marco Bambini might have said: > Hello, > > today I made some test on a project I wrote some years ago. > I upgraded sqlite library from version 3.4.2 to version 3.6.17. > What I am really unable to understand is the time difference required > to perform the same query using the exact same algorithm by the two > libraries. > > SELECT * FROM table1 > where table1 has 1 million rows and 10 columns (its an 80MB db). > > Version 3.4.2 takes about 5.06 seconds (average value) while version > 3.6.17 takes about 7.28 seconds (average value). > Could be a slowdown in the library for the complexity added over the > years or does someone have another possible explanation? > > Thanks. Did you just relink your app or did you also migrate the data to a new sqlite3 database? I think the migration command is: echo '.dump' | sqlite3 $db | sqlite3 $dbnew Maybe the internal database structure has changed? Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Explanation
Hello, today I made some test on a project I wrote some years ago. I upgraded sqlite library from version 3.4.2 to version 3.6.17. What I am really unable to understand is the time difference required to perform the same query using the exact same algorithm by the two libraries. SELECT * FROM table1 where table1 has 1 million rows and 10 columns (its an 80MB db). Version 3.4.2 takes about 5.06 seconds (average value) while version 3.6.17 takes about 7.28 seconds (average value). Could be a slowdown in the library for the complexity added over the years or does someone have another possible explanation? Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Explanation about ROWID requested
Hello, A few days ago I posted a question and I haven't seen any comments so far. I'm really curious about ROWID's volatility. How can I make sure that ROWIDs do not get re-initialized? I'm posting the message once again hoping that someone will explain how I should properly use ROWIDs. Thanks again. Best regards, -- Tito ** Would this explanation about ROWID make sense?: Referencing ROWID: If you make references to ROWID but then export your database (using, for example, the ".dump" command of the sqlite shell) and reimport it, all of your ROWIDs will change and your references won't be right any more. If you use an INTEGER PRIMARY KEY, the ".dump" command will preserve the values and your references will not be broken by the export. *** IMPORTANT ***: Please note that ROWID columns are always created by SQLite, even if you don't specify it when creating the table via CREATE TABLE. If this is the case and you do something like this: SELECT * FROM the data returned will not include the ROWID columns, since it really isn't part of the schema. It's always safer to include the ROWID column in the CREATE TABLE statement, since you'll always have a chance to display ROWID's contents. I have copied part of the text from an old posting written by Dr. Hipp. My questions are: - if I declare ROWID as INTEGER PRIMARY KEY in the CREATE TABLE statement, would that be enough to guarantee that the ROWID is safely dumped and reimported? - Or is ROWID *always* rearranged when reimporting? - If this is the case I guess I would have to create my own unique column (i.e. MyUniqueUID and type INTEGER PRIMARY KEY, right?) - The part that confuses me about Dr. Hipp's statement is this: 'If you use an INTEGER PRIMARY KEY, the ".dump" command will preserve the values and your references will not be broken by the export.': this is the reason I posted my first question above... Any comments? Thanks! -- Tito - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Explanation about ROWID requested
Hello, Would this explanation about ROWID make sense?: Referencing ROWID: If you make references to ROWID but then export your database (using, for example, the ".dump" command of the sqlite shell) and reimport it, all of your ROWIDs will change and your references won't be right any more. If you use an INTEGER PRIMARY KEY, the ".dump" command will preserve the values and your references will not be broken by the export. *** IMPORTANT ***: Please note that ROWID columns are always created by SQLite, even if you don't specify it when creating the table via CREATE TABLE. If this is the case and you do something like this: SELECT * FROM the data returned will not include the ROWID columns, since it really isn't part of the schema. It's always safer to include the ROWID column in the CREATE TABLE statement, since you'll always have a chance to display ROWID's contents. I have copied part of the text from an old posting written by Dr. Hipp. My questions are: - if I declare ROWID as INTEGER PRIMARY KEY in the CREATE TABLE statement, would that be enough to guarantee that the ROWID is safely dumped and reimported? - Or is ROWID *always* rearranged when reimporting? - If this is the case I guess I would have to create my own unique column (i.e. MyUniqueUID and type INTEGER PRIMARY KEY, right? - The part that confuses me about Dr. Hipp's statement is this: 'If you use an INTEGER PRIMARY KEY, the ".dump" command will preserve the values and your references will not be broken by the export.': this is the reason I posted my first question above... Any comments? Thanks! -- Tito
