Re: [sqlite] New
On Fri, 28 Feb 2014 18:54:34 -0600, Ashleigh wrote: > I'm trying to view files from my iphone backup I'm not > sure which program it is it says sqlite it is a black box > like the windows command That would be the sqlite command line tool, sqlite3.exe . If you start a MS Windows command window (CMD.EXE), then type sqlite3 , sqlite will open that file (if it really is a sqlite database). Then type .h for help. If you prefer a graphical user interface, I can recommend the sqlite manager plugin in the Firefox web browser. > If any one knows a better way to read and understand the files I would > greatly appreciate it >I think the file ext. is a plist. >Live, love & laugh. > -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New
On 1 Mar 2014, at 12:54am, Ashleigh wrote: > I'm trying to view files from my iphone backup I'm not sure which program it > is it says sqlite it is a black box like the windows command Sorry, your question is about the program you're using and not about SQLite. Please ask somewhere else. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transactions
On 2/28/2014 5:48 PM, L. Wood wrote: Is this legal? 1) Create "INSERT" prepared statement with parameters. 2) Create a second non-parameter "BEGIN TRANSACTION" prepared statement, execute it with _step(), and finalize it. 3) Attach values to the parameters of the first "INSERT" prepared statement, _step() it, _reset() it. Repeat 3) many times. 4) Create a third non-parameter "END TRANSACTION" prepared statement, execute it with _step(), and finalize it. 5) Finalize the original "INSERT" prepared statement. Looks perfectly fine to me. That's pretty much the standard operating procedure. Could these prepared statements conflict with each other? I don't see why they would. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/02/14 16:54, Ashleigh wrote: > I'm trying to view files from my iphone backup I'm not sure which > program it is it says sqlite it is a black box like the windows command > If any one knows a better way to read and understand the files I would > greatly appreciate it I think the file ext. is a plist. Live, love & > laugh. In addition to those, read this: http://catb.org/~esr/faqs/smart-questions.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlMRNzgACgkQmOOfHg372QTAWACglwFrY79O3Z8U0Hz7xCv3B8VM Xv8Anjm//0wqI5eBrJ08EIFB4/OdixcU =DOm0 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New
Hello All this here, is permeated by The Supreme Soul, whatever there is in this world. Enjoy things by renunciation. Do not covet others' wealth. Best Regards. On Fri, Feb 28, 2014 at 4:54 PM, Ashleigh wrote: > I'm trying to view files from my iphone backup I'm not sure which program > it is it says sqlite it is a black box like the windows command If any one > knows a better way to read and understand the files I would greatly > appreciate it > I think the file ext. is a plist. > Live, love & laugh. > > On Feb 28, 2014, at 10:35 AM, RSmith wrote: > > > On 2014/02/28 17:13, Ashleigh wrote: > >> Nothing will load in SQLite just the command box > > > > Not sure if this is a prophecy, a problem, a proposition or a > premonition, but I am pretty confident that it isn't an SQLite process > problem. > > > > Might you give us some more information please? > > > > What command box opens, when you do what? How did you attempt to "load" > something, and which something did you try to load on which platform using > which SQLite tool/version/library, and when the something didn't load, is > there an error, an indication or just emptiness all around? > > > > Our psychic abilities have not matured, please be explicit. > > > >> Live, love & laugh. > > Maybe one should add: "pay attention" and "worship curiosity" to > those... :) > > > > > > > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New
I'm trying to view files from my iphone backup I'm not sure which program it is it says sqlite it is a black box like the windows command If any one knows a better way to read and understand the files I would greatly appreciate it I think the file ext. is a plist. Live, love & laugh. On Feb 28, 2014, at 10:35 AM, RSmith wrote: > On 2014/02/28 17:13, Ashleigh wrote: >> Nothing will load in SQLite just the command box > > Not sure if this is a prophecy, a problem, a proposition or a premonition, > but I am pretty confident that it isn't an SQLite process problem. > > Might you give us some more information please? > > What command box opens, when you do what? How did you attempt to "load" > something, and which something did you try to load on which platform using > which SQLite tool/version/library, and when the something didn't load, is > there an error, an indication or just emptiness all around? > > Our psychic abilities have not matured, please be explicit. > >> Live, love & laugh. > Maybe one should add: "pay attention" and "worship curiosity" to those... :) > > > > ___ > 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
[sqlite] Transactions
Is this legal? 1) Create "INSERT" prepared statement with parameters. 2) Create a second non-parameter "BEGIN TRANSACTION" prepared statement, execute it with _step(), and finalize it. 3) Attach values to the parameters of the first "INSERT" prepared statement, _step() it, _reset() it. Repeat 3) many times. 4) Create a third non-parameter "END TRANSACTION" prepared statement, execute it with _step(), and finalize it. 5) Finalize the original "INSERT" prepared statement. Could these prepared statements conflict with each other? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why SQLITE_FLOAT instead of SQLITE_REAL?
On Fri, Feb 28, 2014 at 4:36 PM, L. Wood wrote: > SQLite has the REAL data type: > > https://www.sqlite.org/datatype3.html > > Then why do we have SQLITE_FLOAT instead of SQLITE_REAL? All the other > data types (INTEGER, BLOB, TEXT, NULL) match with the SQLITE_ constants. > > Is this just a historical quirk that stuck, or something else? > Historical quirk > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why SQLITE_FLOAT instead of SQLITE_REAL?
SQLite has the REAL data type: https://www.sqlite.org/datatype3.html Then why do we have SQLITE_FLOAT instead of SQLITE_REAL? All the other data types (INTEGER, BLOB, TEXT, NULL) match with the SQLITE_ constants. Is this just a historical quirk that stuck, or something else? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual Table "Functions"
Can someone tell me how the statement below works? > From Eleytherios Stamatogiannakis : > create table newtable as select * from READCOMPRESSEDFILE('ctable.rc'); I'm using virtual tables extensively in my application, to expose runtime C++ objects, and I'm declaring them as shown in http://www.sqlite.org/vtab.html 1.1 Usage, i.e. using create virtual table vt using module(args...) How does one create such Virtual Table *Functions*? It looks like it creates a temporary table, but I don't see how to achieve the above using registered custom SQL functions API, nor the VTable API. Can those functions be used in joins? And if so, can the arguments fed to the VTable "Function" be columns from the preceding "tables" participating in the join? Thanks for any help on this. This is really puzzling to me. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
On 28 Feb 2014, at 5:18pm, L. Wood wrote: > If I do this, would you expect _step() for the "BEGIN TRANSACTION" query and > _step() for each "DELETE" query to be very fast, but the _step() for the "END > TRANSACTION" query to take most (99%) of the time? > > Would you expect a similar speed boost for "INSERT"? Is one by one "INSERT" > in a similar way slow as molasses, and wrapping many inserts in a transaction > recommended? The way SQL works is that theoretically you cannot do any command outside a transaction. No changes are actually made to the database except when a transaction ends. Therefore if you issue "BEGIN TRANSACTION" all the work is actually done when you execute "END TRANSACTION". Until then, SQL is just making a list of the changes you may want to do sometime in the future. Therefore the "END TRANSACTION" is the command that takes all the time. However, SQLite tries to be useful to you. If you forget to do "BEGIN TRANSACTION" before you execute an INSERT or a DELETE, instead of returning an error message it helpfully wraps that single command inside a transaction for you. Therefore that single command makes changes to the database. So it takes a long time. This is why issuing many INSERT or DELETE commands with no transaction declared takes so long. For each separate command SQLite has to do make the change you asked for then do lots of work to make sure that the database file is synchronised with the journal file and the disk is updated to reflect both changes. If you wrap lots of commands inside one transaction SQLite only has to do this synchronization once. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
Yes. On Fri, Feb 28, 2014 at 12:18 PM, L. Wood wrote: > > I expect #2 to work best. Make sure to enclose the whole thing in an > > explicit transaction (or at least, run large batches within explicit > > transactions; one implicit transaction per deleted row will be slow as > > molasses). > > If I do this, would you expect _step() for the "BEGIN TRANSACTION" query > and _step() for each "DELETE" query to be very fast, but the _step() for > the "END TRANSACTION" query to take most (99%) of the time? > > Would you expect a similar speed boost for "INSERT"? Is one by one > "INSERT" in a similar way slow as molasses, and wrapping many inserts in a > transaction recommended? > ___ > 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] Recommended way to delete rows
> I expect #2 to work best. Make sure to enclose the whole thing in an > explicit transaction (or at least, run large batches within explicit > transactions; one implicit transaction per deleted row will be slow as > molasses). If I do this, would you expect _step() for the "BEGIN TRANSACTION" query and _step() for each "DELETE" query to be very fast, but the _step() for the "END TRANSACTION" query to take most (99%) of the time? Would you expect a similar speed boost for "INSERT"? Is one by one "INSERT" in a similar way slow as molasses, and wrapping many inserts in a transaction recommended? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New
On 2014/02/28 17:13, Ashleigh wrote: Nothing will load in SQLite just the command box Not sure if this is a prophecy, a problem, a proposition or a premonition, but I am pretty confident that it isn't an SQLite process problem. Might you give us some more information please? What command box opens, when you do what? How did you attempt to "load" something, and which something did you try to load on which platform using which SQLite tool/version/library, and when the something didn't load, is there an error, an indication or just emptiness all around? Our psychic abilities have not matured, please be explicit. Live, love & laugh. Maybe one should add: "pay attention" and "worship curiosity" to those... :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual table API performance
Hello, First of all, i would like to say that, IMHO, SQLite's overall design and code quality is top notch. Having said that, SQLite's virtual table API, while beautifully designed, it is not very efficient. We have been hitting our heads for the last few years, on the virtual table API, to make it work efficiently. During that time, we were thinking that the problem was on our side, in the code that feeds the virtual table API. It turns out that the virtual table API isn't very efficient. The main cause of inefficiency is that it is extremely "chatty". For an external stream that contains many columns, "xColumn" can be called-back hundreds of millions of times for the stream to be consumed by SQLite. These callbacks have a very big cost. Let me describe a test that we did. For our work, we use compressed streams that are being fed in SQLite through the virtual table API. If we load into SQLite, the external compressed stream (containing 3M rows) through the virtual table API: create table newtable as select * from READCOMPRESSEDFILE('ctable.rc'); it takes: 55 sec If we create an external program that inserts into SQLite, the rows in the compressed stream one by one, using "insert into newtable values " and the SQLite bind API: it takes: 19 sec (~3x faster than using the virtual table API) Another problem with the virtual table API, is that it wrecks havok with VM JIT engines. Especially for tracing JIT engines, the many "small" per column callbacks do not permit them to specialize at all, compared to the specialization that a tracing JIT could achieve with one "big" get_an_entire_row callback. A suggestion for improving the efficiency of the virtual table API naturally arises when we look at all the virtual table functions that we have already created. We have ~15 VT functions dealing with importing "all" from external sources (files, http streams, xml, clipboard, etc), and only one filtering VT function (a multidimensional index) "picking" columns to return. So most of our queries that use VTs look like this: create table cliptab as select * from clipboard(); , these queries most of the time select all columns from an external stream. Based on above, an addition that improves the efficiency of the VT API would be an "xRow" function that the SQLite could call to get an entire row back (like the bind API). Even better, and to reduce even more the callback count, would be a "xNextRow" function that returns the contents of the next row or EOF. Regards, estama. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
On 2/28/2014 9:59 AM, L. Wood wrote: * Prepare query containing single ID with _prepare(). Execute it with _step(). Repeat for each ID. * Call _prepare() with a query with single parameter for the ID. _bind(), _step(), and _reset() - repeat for each ID. * Call _prepare() with a query containing every single one of the IDs. Then _step() on it once. Done. Is this even possible? Since there can be a million IDs, I'm not sure if the query can be so long. I expect #2 to work best. Make sure to enclose the whole thing in an explicit transaction (or at least, run large batches within explicit transactions; one implicit transaction per deleted row will be slow as molasses). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New
Ashleigh wrote: > Nothing will load in SQLite just the command box I guess you clicked on "sqlite3.exe"? What exactly are you trying to do? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite fails to load
Thanks for the reply. Everything seems to be working fine, the only thing is I don't see SQLite in VEE's drop down box for "Available .NET Assemblies." I'm not sure where that list comes from, and I thought that even though SQLite is working, that I might not have installed it properly with gacutil. -Bill -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Friday, February 28, 2014 10:24 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] System.Data.SQLite fails to load Drago, William @ MWG - NARDAEAST wrote: > > I solved this problem by deploying the "System.Data.SQLite.dll" > mixed-mode assembly to > the GAC. I know this is not recommended, but it's the only thing that works. > It should be OK, the mixed-mode assembly is designed for these types of cases, when an application or environment cannot make use of app-local deployment for some reason. > > My question now is, I used gacutil.exe instead of the installer that > comes with SQLite. > Was that a mistake? Should I have used the installer? I hate running installers unless > I know exactly what they're going to do, that's why I'm asking. > Using "gacutil" is fine. That's more-or-less what the setup packages do. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
Stephan Beal wrote: > On Fri, Feb 28, 2014 at 3:59 PM, L. Wood wrote: >> I can think of three ways: >> * Call _prepare() with a query with single parameter for the ID. >> _bind(), _step(), and _reset() - repeat for each ID. This is the obvious method to use. >> Are there other ways? > > i don't know that this would be better, but it's a different way: > > collect the list into a temp table with one field (the to-delete id), then > do a DELETE FROM x WHERE id IN temptablename In this case, SQLite will construct a temporary list of values to hold all the rowids before doing the actual deletions. The overhead of constructing this list might or might not be larger than the overhead of executing already-prepared statements. However, if there are too many IDs, that temporay list will affect the caches, or might even need to be stored on disk. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite fails to load
Drago, William @ MWG - NARDAEAST wrote: > > I solved this problem by deploying the "System.Data.SQLite.dll" mixed-mode assembly to > the GAC. I know this is not recommended, but it's the only thing that works. > It should be OK, the mixed-mode assembly is designed for these types of cases, when an application or environment cannot make use of app-local deployment for some reason. > > My question now is, I used gacutil.exe instead of the installer that comes with SQLite. > Was that a mistake? Should I have used the installer? I hate running installers unless > I know exactly what they're going to do, that's why I'm asking. > Using "gacutil" is fine. That's more-or-less what the setup packages do. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
On Fri, Feb 28, 2014 at 3:59 PM, L. Wood wrote: > I can think of three ways: > Which way do you recommend? Are there other ways? > i don't know that this would be better, but it's a different way: collect the list into a temp table with one field (the to-delete id), then do a DELETE FROM x WHERE id IN temptablename That might solve your list-length problem (can't say how performantly). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] New
Nothing will load in SQLite just the command box Live, love & laugh. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite fails to load
I solved this problem by deploying the "System.Data.SQLite.dll" mixed-mode assembly to the GAC. I know this is not recommended, but it's the only thing that works. My question now is, I used gacutil.exe instead of the installer that comes with SQLite. Was that a mistake? Should I have used the installer? I hate running installers unless I know exactly what they're going to do, that's why I'm asking. Thanks, -Bill -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Incongruous Sent: Wednesday, February 26, 2014 9:15 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] System.Data.SQLite fails to load You've sparked my curiosity, what is this VEE programming language? Is there a web site I can go to read more about it? -Original Message- From: Joe Mistachkin Sent: Tuesday, February 25, 2014 3:44 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] System.Data.SQLite fails to load William Drago wrote: > > I am using System.Data.SQLite with a relatively uncommon > language called VEE. This is an interpreted language that > runs in a 32bit development/runtime environment. > I've never heard of this language before; however, it sounds like it hosts the CLR within its process? > > My application works fine when run on an internal or USB > thumb drive. However, if I try running it from a network > drive SQLite fails to load. > For the 2.0 .NET Framework, loading assemblies from a network share can be complicated by trust issues. Since I do not see any network share paths in your trace output, I'm not sure if that is the case here. One thing that I'm noticing is that the successful load uses the "LoadFrom context" and the failed load uses the default context. I'm not sure how the VEE code loads the System.Data.SQLite assembly (or other assemblies); however, maybe try using the "LoadFrom context" when loading from the network share as well. -- Joe Mistachkin ___ 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 CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Recommended way to delete rows
If I already have a collection of row IDs of rows I wish to delete from a table, what is a recommended/fast way to delete them from the table? The collection is just a set/array of integers, not necessarily contiguous. I can think of three ways: * Prepare query containing single ID with _prepare(). Execute it with _step(). Repeat for each ID. * Call _prepare() with a query with single parameter for the ID. _bind(), _step(), and _reset() - repeat for each ID. * Call _prepare() with a query containing every single one of the IDs. Then _step() on it once. Done. Is this even possible? Since there can be a million IDs, I'm not sure if the query can be so long. Which way do you recommend? Are there other ways? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: datatype mismatch
On Fri, Feb 28, 2014 at 8:00 AM, Dominique Devienne wrote: > Ran into this [datatype mismatch] error, which surprised me since I > thought SQLite's > dynamic typing allowed any value type to be stored in any column. > The exception to that rule is an INTEGER PRIMARY KEY column, which is only allowed to store an integer. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why does type affinity declared on a foreign key column affect join speed?
On Fri, Feb 28, 2014 at 6:38 AM, Bruce Sutherland wrote: > We are tracking manufactured components with an alphanumeric serial number, > which gives us a natural key. Naturally we set type affinity TEXT on the key > column. There are many tables linked through foreign key relationships on > this serial number. I just posted for info on this, in SO: http://stackoverflow.com/questions/22060197 :) My own question is more why is it asymmetrical, i.e. depending on which side of the join one adds a WHERE clause, the plan is indexed on both sides, or not. --DD C:\Users\DDevienne>sqlite3 SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table parent (sn text primary key, data text); sqlite> create table child_int (sn integer references parent(sn), data text, unique(sn)); sqlite> create table child_txt (sn textreferences parent(sn), data text, unique(sn)); sqlite> insert into parent values ('x', '1'), ('y', '2'), ('z', '3'); sqlite> insert into child_int values ('x', 'one'), ('y', 'two'), ('z', 'three'); sqlite> insert into child_txt values ('x', 'one'), ('y', 'two'), ('z', 'three'); sqlite> select p.data, c.data from parent p, child_int c on p.sn = c.sn where p.sn = 'y'; 2|two sqlite> select p.data, c.data from parent p, child_int c on p.sn = c.sn where c.sn = 'y'; 2|two sqlite> select p.data, c.data from parent p, child_txt c on p.sn = c.sn where p.sn = 'y'; 2|two sqlite> select p.data, c.data from parent p, child_txt c on p.sn = c.sn where c.sn = 'y'; 2|two sqlite> explain query plan select p.data, c.data from parent p, child_int c on p.sn = c.sn where p.sn = 'y'; 0|0|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?) 0|1|1|SEARCH TABLE child_int AS c USING INDEX sqlite_autoindex_child_int_1 (sn=?) sqlite> explain query plan select p.data, c.data from parent p, child_int c on p.sn = c.sn where c.sn = 'y'; 0|0|1|SEARCH TABLE child_int AS c USING INDEX sqlite_autoindex_child_int_1 (sn=?) 0|1|0|SCAN TABLE parent AS p sqlite> explain query plan select p.data, c.data from parent p, child_txt c on p.sn = c.sn where p.sn = 'y'; 0|0|1|SEARCH TABLE child_txt AS c USING INDEX sqlite_autoindex_child_txt_1 (sn=?) 0|1|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?) sqlite> explain query plan select p.data, c.data from parent p, child_txt c on p.sn = c.sn where c.sn = 'y'; 0|0|0|SEARCH TABLE parent AS p USING INDEX sqlite_autoindex_parent_1 (sn=?) 0|1|1|SEARCH TABLE child_txt AS c USING INDEX sqlite_autoindex_child_txt_1 (sn=?) sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error: datatype mismatch
Ran into this error, which surprised me since I thought SQLite's dynamic typing allowed any value type to be stored in any column. Either the PK or the FK seem to have some influence (with pragma foreign_keys on or off), which I didn't expect, since the entered FK value does match the parent row's PK. Can anyone shed some light on this error please? --DD C:\Users\DDevienne>sqlite3 SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table parent (sn text primary key, data text); sqlite> create table child_int (sn integer primary key references parent(sn), data text); sqlite> create table child_txt (sn textprimary key references parent(sn), data text); sqlite> insert into parent values ('x', '1'), ('y', '2'), ('z', '3'); sqlite> insert into child_int values ('x', 'one'), ('y', 'two'), ('z', 'three'); Error: datatype mismatch sqlite> insert into child_txt values ('x', 'one'), ('y', 'two'), ('z', 'three'); sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why does type affinity declared on a foreign key column affect join speed?
We are tracking manufactured components with an alphanumeric serial number, which gives us a natural key. Naturally we set type affinity TEXT on the key column. There are many tables linked through foreign key relationships on this serial number. On one table, the type affinity of the serial number FOREIGN KEY column was incorrectly declared as INTEGER. Due to the flexibility of the type affinity system, this never showed up as an issue. We were able to INSERT valid TEXT values into the column with no problem. Everything seemed to be working fine. As the database grew modestly, some queries became very slow. After making sure that all relevant columns were already automatically indexed due to FOREIGN KEY and PRIMARY KEY declarations, I found the culprit. After changing the type affinity on the bad table from INTEGER to TEXT, the slow query which joined on that table sped up by two orders of magnitude. I'm happy enough with the end result, but I'd quite like to know why this happened? Was SQLite building a different type of index under the hood due to the declared type affinity? Did the difference in type affinity of the compared columns prevent the query from using the index? Was there some unnecessary type conversion going on which slowed things down? Here is an cut down example: CREATE TABLE IF NOT EXISTS pcb_units ( serial_no TEXT NOT NULL, PRIMARY KEY (serial_no), -- Constraints enforcing serial number validity. CONSTRAINT family_check CHECK (SUBSTR(serial_no, 6, 1) BETWEEN 'A' AND 'Z' OR SUBSTR(serial_no, 6, 1) BETWEEN '0' AND '9'), CONSTRAINT model_check CHECK (SUBSTR(serial_no, 7, 1) BETWEEN 'A' AND 'Z' OR SUBSTR(serial_no, 7, 1) BETWEEN '0' AND '9'), CONSTRAINT reserved_check CHECK (SUBSTR(serial_no, 8, 1) == '0'), CONSTRAINT unit_number_check CHECK (CAST(SUBSTR(serial_no, 9, 4) AS INTEGER) BETWEEN 0 AND ) ); CREATE TABLE assembly_pcb_units ( pcb_serial_no INTEGER NOT NULL,-- This is the bad column assembly_serial_no TEXT NOT NULL, added_time TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, added_by TEXT NOT NULL, CONSTRAINT pcb_in_one_assembly_only UNIQUE(pcb_serial_no), FOREIGN KEY (assembly_serial_no) REFERENCES assembly_units(assembly_serial_no), FOREIGN KEY (pcb_serial_no) REFERENCES pcb_units(serial_no), FOREIGN KEY (added_by) REFERENCES sti_users(user_id) ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 'Select' queries via pdo_sqlite are slow
On 28 Feb 2014, at 9:48am, pihu...@free.fr wrote: > I did a fourth one with a '+' sign before the "NomJob = 'NSAVBASE'" clause > :[snip] > This one is way quicker (0.055 second instead of 2.235 seconds in my latest > test). That is a good experiment. Unfortunately it depends on you knowing something special about SQLite you shouldn't have to know. > Is there a better way to optimize all my queries instead of checking them one > by one ? The honest answer to this is to learn how indexes work and are used. Theoretically the way to use SQL is to create one ideal index for each SELECT, DELETE and UPDATE command. Of course, it will turn out that many commands have the same 'ideal' index, or one index will make them both fast enough even if it is not the abolute fastest. Or that the best index for them is the one SQLite automatically creates on the TABLE's primary key. So in real life you don't end up with that many indexes. > I heard indexes aren't useful for a database with a single table. What do you > suggest ? Sorry, but what you heard is nonsense. Indexes operate /only/ on a single table. There's no way to put data from two tables in one index (assuming the documentation is correct and you can't create an index on a VIEW). I will try to give an answer useful for many people and I have to simplify this a little to make it fit in an email message. To learn to optimize a command like select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20 guess at many indexes like the following: CREATE INDEX ti1 ON ReportJobs (NomJob, NomChaine, DateMonteeAuPlan) CREATE INDEX ti2 ON ReportJobs (NomChaine, NomJob, DateMonteeAuPlan) ANALYZE EXPLAIN QUERY PLAN select DateMonteeAuPlan, [...] This would give SQLite its best possible chance of having a very good index to use. Then look at the output of the EXPLAIN QUERY PLAN command, see which index SQLite decided to use, and delete the others. (just to explain: ANALYZE analyses how useful each index is, so if you create or delete indexes it can be useful to do ANALYZE again.) Once you have done this for a few projects you will learn how indexes work and you will just naturally be able to guess at a good index for each command, and also decide whether the increase in file size makes it worth creating an index when it would speed up a command by only 20 or 30 milliseconds. Of course, creating an index may speed up a search but it slows down changes made to a table (on each change, each index must be updated). So the correct thing to do depends on the nature of your program: whether you want input and output to be faster. And lastly the standard warning about over optimization: Remember that if you find yourself saying "My program must do everything as fast as possible." you are doing the wrong thing. It is better to say "My program must do everything fast enough.". Hope this helps. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 'Select' queries via pdo_sqlite are slow
Thanks Simon for your reply. An ANALYZE; in the SQLite shell tool did nothing performance-wise. Here are the EXPLAIN QUERY PLAN results : sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' limit 20; 0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 (ANY(NomChaine) AND ANY(DateMonteeAuPlan) AND NomJob=?) --> pdo_sqlite query() : 0 second sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine = 'DCLC25736' order by DateMonteeAuPlan DESC limit 20; 0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 (NomChaine=?) --> pdo_sqlite query() : 0.001 second sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20; 0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 (ANY(NomChaine) AND ANY(DateMonteeAuPlan) AND NomJob=?) 0|0|0|USE TEMP B-TREE FOR ORDER BY --> pdo_sqlite query() : 2.235 seconds I did a fourth one with a '+' sign before the "NomJob = 'NSAVBASE'" clause : sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where +NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20; 0|0|0|SCAN TABLE ReportJobs 0|0|0|USE TEMP B-TREE FOR ORDER BY This one is way quicker (0.055 second instead of 2.235 seconds in my latest test). Is there a better way to optimize all my queries instead of checking them one by one ? I heard indexes aren't useful for a database with a single table. What do you suggest ? Thanks again. - Mail original - De: "Simon Slavin" À: "General Discussion of SQLite Database" Envoyé: Jeudi 27 Février 2014 14:08:24 Objet: Re: [sqlite] 'Select' queries via pdo_sqlite are slow On 26 Feb 2014, at 9:09pm, pihu...@free.fr wrote: > Benchmark (bench.php) on the « $bdd->query(...); » instruction : > Query 2 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where > NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' limit 20; > => 0.0002752075195 seconde(s) > > Query 3 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where > NomJob = 'NSAVBASE' and NomChaine = 'DCLC25736' order by DateMonteeAuPlan > DESC limit 20; > => 0 seconde(s) > > Query 1 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where > NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan > DESC limit 20; > => 1.862160767 seconde(s) > > > Why is there so much differences between two query quasi-identical? My guess is that withthe combination of a LIKE and another condition, the optimizer can't figure out which approach will give you the fastest result. First, do an ANALYZE on that database. You can do it inside your own software or just open the database with the SQLite shell tool. The results of the ANALYZE are saved in the database for later use. If that doesn't improve things try using the command EXPLAIN QUERY PLAN [select command goes here] on each of those SELECT statements. You'll see which indexes the statement is trying to use. Again you can execute this command in your own software or in the shell tool. The results are returned as if you had done a SELECT command. Simon. ___ 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