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
Re: [sqlite] Explanation about ROWID requested
Hello Dr. Hipp, On 1 may 2004, at 8:38, D. Richard Hipp wrote: When you do a "SELECT *", the results contain only columns that are explicitly declared in the CREATE TABLE statement. If you have declared an INTEGER PRIMARY KEY column, then the rowid will appear under that column name. If there is no INTEGER PRIMARY KEY, then the rowid will not be a part of the result. The ".dump" command works by doing a "SELECT *". So if you do not have an INTEGER PRIMARY KEY in your table declaration, the rowid will not be part of the saved data and will be lost when the table is reconstructed. This is the explanation I was hoping for. Excellent! BTW... I think that this information should be mentioned in the documentation, FAQ, or Wiki (why not all 3! ;-) If nobody is against it, I can take care of the Wiki page. Is that OK with you? I would like to thank everyone else that answered my post. I truly appreciate it. Best regards, -- Tito - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Explanation about ROWID requested
Every row of every table has a ROWID. The ROWID can be called "ROWID", "_ROWID_", and/or "OID". All three names refer to the same value and can be used interchangably. But if you declare a column with any of those names, the name refers to your declared column, not the actual ROWID. This is similar to how an local automatic variable will hide a global variable by the same name in C/C++. If you declare a column to be an INTEGER PRIMARY KEY, then that column becomes another alias for the ROWID. Examples: Given a table of the following form: CREATE TABLE t1(x INTEGER PRIMARY KEY, y); All of the following statements are equivalent: SELECT rowid, y FROM t1; SELECT _rowid_, y FROM t1; SELECT oid, y FROM t1; SELECT x, y FROM t1; As far as SQLite is concerned, "rowid", "_rowid_", "oid", and "x" are just different names for the same value. These statements are also all equivalent: INSERT INTO t1(rowid,y) VALUES(1,2); INSERT INTO t1(_rowid_,y) VALUES(1,2); INSERT INTO t1(oid,y) VALUES(1,2); INSERT INTO t1(x,y) VALUES(1,2); Any place you can use the column "x" associated with table "t1", you can substitute "rowid", "_rowid_", or "oid" and get the same result. When you do a "SELECT *", the results contain only columns that are explicitly declared in the CREATE TABLE statement. If you have declared an INTEGER PRIMARY KEY column, then the rowid will appear under that column name. If there is no INTEGER PRIMARY KEY, then the rowid will not be a part of the result. The ".dump" command works by doing a "SELECT *". So if you do not have an INTEGER PRIMARY KEY in your table declaration, the rowid will not be part of the saved data and will be lost when the table is reconstructed. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Explanation about ROWID requested
Tito Ciuro <[EMAIL PROTECTED]> writes: > 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. If you declare an INTEGER PRIMARY KEY then it will be used as the ROWID: sqlite> CREATE TABLE test ...> ( ...> id INTEGER PRIMARY KEY, ...> t TEXT ...> ); sqlite> INSERT INTO test VALUES (23, 'hello'); sqlite> INSERT INTO test VALUES (42, 'world'); sqlite> SELECT id, t FROM test; id = 23 t = hello id = 42 t = world sqlite> SELECT ROWID, t FROM test; ROWID = 23 t = hello ROWID = 42 t = world sqlite> .dump BEGIN TRANSACTION; CREATE TABLE test ( id INTEGER PRIMARY KEY, t TEXT ); INSERT INTO test VALUES(23,'hello'); INSERT INTO test VALUES(42,'world'); COMMIT; sqlite> Note that whether I select 'id' or 'ROWID', I get the same value. Also note that the .dump command generates a script that will recreate exactly the same data as I did initially, so ROWID will have the same value after a re-create as it does initially. Derrell - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Explanation about ROWID requested
Tito Ciuro wrote: 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 Hi Tito, AFAIK the ROWID is used internally as a means to organize the individual rows of a table in storage. Basically it is an integer, which is different between different rows. However, if a table features an INTEGER PRIMARY KEY column (which obviously is different between different rows too), the database engine might use that value instead if an internally only handled value, thereby saving one stage of indirection. Of course, when writing such a table into an external format and rereading it, these values shall not change - as it is with all values saved via ".dump" and reread (if they would, nobody would ".dump" call a backup utility, but "obfuscator" instead :) I even think referencing the ROWID, even when possible, is IMHO a sign of bad table layout; why would you even want it when you could easily reference an "official" SQL column, without any tradebacks. (I even think that exposing the ROWID of a column via SQL is a bad thing(tm), but then I don't have to use it.) - 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? > [ ... ] > - 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?) If I understand you right: you define a column named "ROWID"? Why would you do that? Just call it ID, and you are better off (and even save 3 keystrokes) - 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... When importing a table from the .dump output, the database engine has no chance to guess what ROWID has been used prior to ".dump". Therefore, it cannot guarantee accessing the same RowID to the same row again. Consider this example: create table a (i1, i2); insert into a values (1, 2); insert into a values (1, 3); insert into a values (1, 4); delete from a where i2 = 3; .dump This creates the following output: BEGIN TRANSACTION; create table a (i1, i2); INSERT INTO a VALUES(1,2); INSERT INTO a VALUES(1,4); COMMIT; If this is all information the DB engine gets when reconstruction the table, it *cannot* know which ROWIDs have been assigned to each row. If you use an INTEGER PRIMARY KEY the situatoin is different: the DB engine does use the appr. value of this column as the ROWID, knowing that it will be unique and therefore meets all requirements for an ROWID. It doesn't have to assign ROWIDs on its own. But anyway: As far as I am concerned, I would want to see this ROWID stuff in the tips, tricks, and optimizations section of the documentation (and ROWID not accessible via the ROWID/OID/_ROWID_ columns.) Any comments? Thanks! -- Tito Hope it helped, /eno - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[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
