Re: [sqlite] How rebuild with larger page size from command line?
On 2008 Dec, 02, at 21:19, Thomas Briggs wrote:
> Try removing the semi-colon at the end of the .read statement. The
> semi-colon is the query terminator, but because dot-commands aren't
> queries they don't require the semi. As such the .read command in
> twoLiner.sh is either seeing a third (and invalid) argument or an
> invalid file name ("placesDump.txt ;").
Ah that works. Although I haven't tried Kishor's idea, that should
obviously work too.
Thanks all,
Jerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How rebuild with larger page size from command line?
Try removing the semi-colon at the end of the .read statement. The
semi-colon is the query terminator, but because dot-commands aren't
queries they don't require the semi. As such the .read command in
twoLiner.sh is either seeing a third (and invalid) argument or an
invalid file name ("placesDump.txt ;").
-T
On Tue, Dec 2, 2008 at 11:56 PM, Jerry Krinock <[EMAIL PROTECTED]> wrote:
>
> On 2008 Dec, 02, at 19:44, Thomas Briggs wrote:
>
>> Put both commands (the pragma and the read) into a file (e.g.
>> foo.txt) and then do:
>>
>> sqlite3 newDatabase.sqlite '.read foo.txt'
>
> Looked like a great idea, Thomas but it doesn't work for me:
>
> jk$ echo 'PRAGMA page_size=4096 ;' > twoLiner.sh
> jk$ echo '.read placesDump.txt ;' >> twoLiner.sh
> jk$ sqlite3 places.sqlite '.read twoLiner.sh'
> unknown command or invalid arguments: "read".
>
> The file twoLiner.sh does have the expected contents:
>
> PRAGMA page_size=4096 ;
> .read placesDump.txt ;
>
> I get the same error if I delete the PRAGMA line and just have
> the .read in the file.
>
> In the man page for sqlite3, .read will "Execute SQL in [a file]".
> The problem is probably that .read itself is a meta-command, not
> "SQL"; hence .read cannot be nested.
>
> Any other ideas?
>
> Thanks,
>
> Jerry
>
>
> ___
> 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] How rebuild with larger page size from command line?
On 12/2/08, Jerry Krinock <[EMAIL PROTECTED]> wrote: > > On 2008 Dec, 02, at 19:44, Thomas Briggs wrote: > > > Put both commands (the pragma and the read) into a file (e.g. > > foo.txt) and then do: > > > > sqlite3 newDatabase.sqlite '.read foo.txt' > > > Looked like a great idea, Thomas but it doesn't work for me: > > jk$ echo 'PRAGMA page_size=4096 ;' > twoLiner.sh > jk$ echo '.read placesDump.txt ;' >> twoLiner.sh > jk$ sqlite3 places.sqlite '.read twoLiner.sh' > unknown command or invalid arguments: "read". > > The file twoLiner.sh does have the expected contents: > > PRAGMA page_size=4096 ; > .read placesDump.txt ; > > I get the same error if I delete the PRAGMA line and just have > the .read in the file. > > In the man page for sqlite3, .read will "Execute SQL in [a file]". > The problem is probably that .read itself is a meta-command, not > "SQL"; hence .read cannot be nested. > > Any other ideas? Add the PRAGMA line to the start of your dump file so it looks like so... PRAGMA page_size=4096; Then run your $ sqlite3 dbname '.read dumpfile_with_pragma_added.sh' > > Thanks, > > > Jerry > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How rebuild with larger page size from command line?
On 2008 Dec, 02, at 19:44, Thomas Briggs wrote: > Put both commands (the pragma and the read) into a file (e.g. > foo.txt) and then do: > > sqlite3 newDatabase.sqlite '.read foo.txt' Looked like a great idea, Thomas but it doesn't work for me: jk$ echo 'PRAGMA page_size=4096 ;' > twoLiner.sh jk$ echo '.read placesDump.txt ;' >> twoLiner.sh jk$ sqlite3 places.sqlite '.read twoLiner.sh' unknown command or invalid arguments: "read". The file twoLiner.sh does have the expected contents: PRAGMA page_size=4096 ; .read placesDump.txt ; I get the same error if I delete the PRAGMA line and just have the .read in the file. In the man page for sqlite3, .read will "Execute SQL in [a file]". The problem is probably that .read itself is a meta-command, not "SQL"; hence .read cannot be nested. Any other ideas? Thanks, Jerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
Thank you for the explanation! I now have primary keys and indices added to my tables, my exporter was not previously exporting primary keys correctly to SQLite and I just added index exporting. However I am not seeing any performance gain!! Using EXPLAIN QUERY PLAN is mostly coming back as using PRIMARY KEY or sometimes an index which is good but I would have expected some sort of performance gain. Every table should have at least one primary key and one or more indices now. The file size has increased by about 25-30% too which is unavoidable I guess, I've tried vacuuming but that does not seem to effect the file size much. The sqlite3_analyzer gives the following report summary: Page size in bytes 1024 Pages in the whole file (measured) 2628 Pages in the whole file (calculated).. 2628 Pages that store data. 2628 100.0% Pages on the freelist (per header) 00.0% Pages on the freelist (calculated) 00.0% Pages of auto-vacuum overhead. 00.0% Number of tables in the database.. 77 Number of indices. 152 Number of named indices... 77 Automatically generated indices... 75 Size of the file in bytes. 2691072 Bytes of user payload stored.. 1501970 55.8% -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Tuesday, December 02, 2008 5:06 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe To efficiently execute the SQL "SELECT * FROM mytab WHERE myid = '1234'" you must have an index on the "myid" colunm. Each row has an index which uses a rowid as a key, and that is how the row is accessed. A "primary key" is a column which is indexed and which has a unique value, duplicates are not encouraged. You may have many indices on a table, and an index my have as its key values from more than one column. Brown, Daniel wrote: > I maybe confused but indices sound similar to what I understand primary > keys do, I already have primary keys on each table. Unless I'm mistaken > as to what primary keys are? From your explanation I guess I'm slightly > confused about the difference in primary keys and indices and that I > need to implement indices to speed up my queries. Are there any general > guidelines for creating indices? Is it as simple as creating an indice > per primary key in a table? > > How do I interpret the output from EXPLAIN QUERY PLAN? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton > Sent: Tuesday, December 02, 2008 2:20 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite performance woe > > Databases work by using indices. A search for a row in a table of 1 > million rows goes from having to do as many as a million row reads to a > handful of index node accesses, from minutes to milliseconds. Note that > > Sqlite is "lite" and only uses one index at a time so thoughtful schema > design and query layout is necessary for optimal results. > > Brown, Daniel wrote: > >>Hello Donald & Others, >> >>I have primary keys set for each of the table but no indicies (that I > > am > >>aware of) as I simply converted the data from our existing database >>system which does not support indicies. As my current system only >>implements primary keys I have no real experience dealing with > > indicies, > >>are they like some sort of extra key column? Are there any guides to >>optimising SQLite performance with indicies? >> >>I tried EXPLAIN QUERY PLAN for the following: >>"SELECT * FROM test_item INNER JOIN test_container ON >>test_item.container_code = test_container.container_code" >> >>The output was: >>0|0|TABLE test_item >>1|1|TABLE test_container >> >>Is there a guide I can check for understanding this output? >> >>Daniel >> >>-Original Message- >>From: [EMAIL PROTECTED] >>[mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald >>Sent: Tuesday, December 02, 2008 9:52 AM >>To: General Discussion of SQLite Database >>Subject: Re: [sqlite] SQLite performance woe >> >>Hi Daniel, >> >>Regarding: >> "What I'd like to know is if there is anything we can do with >>our queries, SQLite set-up or library configuration to improve the >>speed? " >> >>Unless indicies would be inappropriate, did you mention whether you've >>defined any indicies and does EXPLAIN QUERY PLAN show that the proper >>index is being used? >> >>___ >>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] How rebuild with larger page size from command line?
Put both commands (the pragma and the read) into a file (e.g. foo.txt) and then do: sqlite3 newDatabase.sqlite '.read foo.txt' -T On Tue, Dec 2, 2008 at 8:48 PM, Jerry Krinock <[EMAIL PROTECTED]> wrote: > I need a command-line script running on Mac OS 10.5 to rebuild sqlite > 3 database files with a page_size of 4096 bytes. > > The first line of my script dumps the database to a text file, then > next line should read it create a new one. Since the default page > size is 1024 bytes, documentation says that I need to change it with a > PRAGMA before creating the database. So I do this: > >sqlite3 newDatabase.sqlite 'PRAGMA page_size=4096; .read dump.txt' > > Result: > >SQL error: near ".": syntax error > > If I eliminate either the PRAGMA or the .read statement, there is no > error. But I need them both. What can I do? > > Thank you, > > Jerry Krinock > ___ > 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] How rebuild with larger page size from command line?
I need a command-line script running on Mac OS 10.5 to rebuild sqlite 3 database files with a page_size of 4096 bytes. The first line of my script dumps the database to a text file, then next line should read it create a new one. Since the default page size is 1024 bytes, documentation says that I need to change it with a PRAGMA before creating the database. So I do this: sqlite3 newDatabase.sqlite 'PRAGMA page_size=4096; .read dump.txt' Result: SQL error: near ".": syntax error If I eliminate either the PRAGMA or the .read statement, there is no error. But I need them both. What can I do? Thank you, Jerry Krinock ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
To efficiently execute the SQL "SELECT * FROM mytab WHERE myid = '1234'" you must have an index on the "myid" colunm. Each row has an index which uses a rowid as a key, and that is how the row is accessed. A "primary key" is a column which is indexed and which has a unique value, duplicates are not encouraged. You may have many indices on a table, and an index my have as its key values from more than one column. Brown, Daniel wrote: > I maybe confused but indices sound similar to what I understand primary > keys do, I already have primary keys on each table. Unless I'm mistaken > as to what primary keys are? From your explanation I guess I'm slightly > confused about the difference in primary keys and indices and that I > need to implement indices to speed up my queries. Are there any general > guidelines for creating indices? Is it as simple as creating an indice > per primary key in a table? > > How do I interpret the output from EXPLAIN QUERY PLAN? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton > Sent: Tuesday, December 02, 2008 2:20 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite performance woe > > Databases work by using indices. A search for a row in a table of 1 > million rows goes from having to do as many as a million row reads to a > handful of index node accesses, from minutes to milliseconds. Note that > > Sqlite is "lite" and only uses one index at a time so thoughtful schema > design and query layout is necessary for optimal results. > > Brown, Daniel wrote: > >>Hello Donald & Others, >> >>I have primary keys set for each of the table but no indicies (that I > > am > >>aware of) as I simply converted the data from our existing database >>system which does not support indicies. As my current system only >>implements primary keys I have no real experience dealing with > > indicies, > >>are they like some sort of extra key column? Are there any guides to >>optimising SQLite performance with indicies? >> >>I tried EXPLAIN QUERY PLAN for the following: >>"SELECT * FROM test_item INNER JOIN test_container ON >>test_item.container_code = test_container.container_code" >> >>The output was: >>0|0|TABLE test_item >>1|1|TABLE test_container >> >>Is there a guide I can check for understanding this output? >> >>Daniel >> >>-Original Message- >>From: [EMAIL PROTECTED] >>[mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald >>Sent: Tuesday, December 02, 2008 9:52 AM >>To: General Discussion of SQLite Database >>Subject: Re: [sqlite] SQLite performance woe >> >>Hi Daniel, >> >>Regarding: >> "What I'd like to know is if there is anything we can do with >>our queries, SQLite set-up or library configuration to improve the >>speed? " >> >>Unless indicies would be inappropriate, did you mention whether you've >>defined any indicies and does EXPLAIN QUERY PLAN show that the proper >>index is being used? >> >>___ >>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 > ___ > 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] SQL example using date()
Greg Robertson <[EMAIL PROTECTED]> wrote: > I would like to do a date search to find records that are between two > dates. The dates are stored in the table in the format DD-MM- but > I can change that to another format but I prefer something readable > rather than an int or double. Just do -MM-DD. Then a simple string comparison also happens to be correct for dates. For more complex date and time manipulation, see http://sqlite.org/lang_datefunc.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL example using date()
On 12/2/08, Greg Robertson <[EMAIL PROTECTED]> wrote: > I would like to do a date search to find records that are between two > dates. The dates are stored in the table in the format DD-MM- but > I can change that to another format but I prefer something readable > rather than an int or double. > > Could someone point me to some docs that could help me write the SQL > and figure out the best format I should store my dates in? > Well, generally the format considered the "best" by many is the Julianday REAL format, but yes, it is unreadable by humans. You can always store the data as you want, but to do any calculations, you can use the datetime functions to convert to an appropriate format to do the math. See the wiki for datetime functions. > Thanks > > Greg -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
All: For comparison I tried several combinations of query orders and indices. I found both indices and the join clause sequence make significant differences in execution time. Using SQLiteSpy with SQLite v3.6.1 on Windows XP. I have two tables: GPFB with 34830 rows, 10 columns with a 4-column primary key SET_1 with 320 rows, 10 columns with a 2-column primary key Indices added on two columns (GID,EID) common to both tables: create index idx_gpfb_gid_eid on GPFB(GID,EID); create index idx_set1_gid_eid on SET_1(GID,EID) (The combination of GID and EID are not unique in either of the tables.) My basic query: select SETID,SID,CUT,X,sum(t1*Kx) as Px,sum(t2*Ky) as Py,sum(t3*Kz) as Pz,sum(R2*Ky+T1*Kx*Z) as My from GPFB join SET_1 where GPFB.GID=SET_1.GID and GPFB.EID=SET_1.EID group by SETID,SID,CUT order by SETID,SID,CUT; I also executed the query reversing the join clause to "from SET_1 join GPFB". 800 rows were returned: "from GPFB join Set_1" with no indices: 3.3 seconds "from GPFB join Set_1" with indices: 109.7 ms "from SET_1 join GPFB" with no indices: 5.5 seconds "from SET_1 join GPFB" with indices: 55.9 ms In this example, EXPLAIN QUERY PLAN seems to indicate only the joined table index is used. There was no significant time delta in either query if the from table index was dropped. Russ Royal -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Tuesday, December 02, 2008 5:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Hello Donald & Others, I have primary keys set for each of the table but no indicies (that I am aware of) as I simply converted the data from our existing database system which does not support indicies. As my current system only implements primary keys I have no real experience dealing with indicies, are they like some sort of extra key column? Are there any guides to optimising SQLite performance with indicies? I tried EXPLAIN QUERY PLAN for the following: "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code" The output was: 0|0|TABLE test_item 1|1|TABLE test_container Is there a guide I can check for understanding this output? Daniel - Daniel, I don't know the sizes of your tables nor the cardinality of your joined items (i.e., how much variation in the values), but you might try creating creating an index or two, especially on the larger table, e.g.: CREATE INDEX idx_ti_ccode ON test_item(container_code); CREATE INDEX idx_ti_ccode ON test_container(container_code); Then run the EXPLAIN QUERY PLAN again and see if one of the indices is mentioned. It might even help a little to VACUUM the database afterwards. If the rows are rather large (i.e. if your avg row is measure in Kbytes rather than bytes) then be sure "container_code" is one of the first columns in each table. Note that when benchmarking, your first run may be markedly slower than repeated runs due to caching. Is this running on a workstation/laptop/pc type of machine, or some embedded gizmo with limited resources? Let us know the results. Donald ___ 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] SQL example using date()
I would like to do a date search to find records that are between two dates. The dates are stored in the table in the format DD-MM- but I can change that to another format but I prefer something readable rather than an int or double. Could someone point me to some docs that could help me write the SQL and figure out the best format I should store my dates in? Thanks Greg ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
-Original Message- Subject: Re: [sqlite] SQLite performance woe I maybe confused but indices sound similar to what I understand primary keys do, I already have primary keys on each table. Unless I'm mistaken as to what primary keys are? From your explanation I guess I'm slightly confused about the difference in primary keys and indices and that I need to implement indices to speed up my queries. Are there any general guidelines for creating indices? Is it as simple as creating an indice per primary key in a table? How do I interpret the output from EXPLAIN QUERY PLAN? - Sorry, emails crossed. Regarding "explaining EXPLAIN": I'm an anti-expert here, and the author writes: "The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their behavior is undocumented, unspecified, and variable." (above is from: http://www.sqlite.org/lang_explain.html ) Nonetheless -- If I run EXPLAIN QUERY PLAN on a query and get an output such as the following, then I know that my query is using the index named "myIndex" in accessing table "client_logs." 0 1 TABLE clients 1 0 TABLE client_logs AS cl WITH INDEX myIndex 0 0 TABLE lastContact You may want to post here the commands you use to create your tables (schema). Since sqlite shows "benign neglect" to most data type specifications, I wondered if it's possible that your definitions of primary keys are not being honored? You may also be interested in: http://www.sqlite.org/lang_createindex.html http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning http://www.sqlite.org/lang_createtable.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Tuesday, December 02, 2008 5:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Hello Donald & Others, I have primary keys set for each of the table but no indicies (that I am aware of) as I simply converted the data from our existing database system which does not support indicies. As my current system only implements primary keys I have no real experience dealing with indicies, are they like some sort of extra key column? Are there any guides to optimising SQLite performance with indicies? I tried EXPLAIN QUERY PLAN for the following: "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code" The output was: 0|0|TABLE test_item 1|1|TABLE test_container Is there a guide I can check for understanding this output? Daniel - Daniel, I don't know the sizes of your tables nor the cardinality of your joined items (i.e., how much variation in the values), but you might try creating creating an index or two, especially on the larger table, e.g.: CREATE INDEX idx_ti_ccode ON test_item(container_code); CREATE INDEX idx_ti_ccode ON test_container(container_code); Then run the EXPLAIN QUERY PLAN again and see if one of the indices is mentioned. It might even help a little to VACUUM the database afterwards. If the rows are rather large (i.e. if your avg row is measure in Kbytes rather than bytes) then be sure "container_code" is one of the first columns in each table. Note that when benchmarking, your first run may be markedly slower than repeated runs due to caching. Is this running on a workstation/laptop/pc type of machine, or some embedded gizmo with limited resources? Let us know the results. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
I maybe confused but indices sound similar to what I understand primary keys do, I already have primary keys on each table. Unless I'm mistaken as to what primary keys are? From your explanation I guess I'm slightly confused about the difference in primary keys and indices and that I need to implement indices to speed up my queries. Are there any general guidelines for creating indices? Is it as simple as creating an indice per primary key in a table? How do I interpret the output from EXPLAIN QUERY PLAN? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Tuesday, December 02, 2008 2:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Databases work by using indices. A search for a row in a table of 1 million rows goes from having to do as many as a million row reads to a handful of index node accesses, from minutes to milliseconds. Note that Sqlite is "lite" and only uses one index at a time so thoughtful schema design and query layout is necessary for optimal results. Brown, Daniel wrote: > Hello Donald & Others, > > I have primary keys set for each of the table but no indicies (that I am > aware of) as I simply converted the data from our existing database > system which does not support indicies. As my current system only > implements primary keys I have no real experience dealing with indicies, > are they like some sort of extra key column? Are there any guides to > optimising SQLite performance with indicies? > > I tried EXPLAIN QUERY PLAN for the following: > "SELECT * FROM test_item INNER JOIN test_container ON > test_item.container_code = test_container.container_code" > > The output was: > 0|0|TABLE test_item > 1|1|TABLE test_container > > Is there a guide I can check for understanding this output? > > Daniel > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald > Sent: Tuesday, December 02, 2008 9:52 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite performance woe > > Hi Daniel, > > Regarding: >"What I'd like to know is if there is anything we can do with > our queries, SQLite set-up or library configuration to improve the > speed? " > > Unless indicies would be inappropriate, did you mention whether you've > defined any indicies and does EXPLAIN QUERY PLAN show that the proper > index is being used? > > ___ > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
Databases work by using indices. A search for a row in a table of 1 million rows goes from having to do as many as a million row reads to a handful of index node accesses, from minutes to milliseconds. Note that Sqlite is "lite" and only uses one index at a time so thoughtful schema design and query layout is necessary for optimal results. Brown, Daniel wrote: > Hello Donald & Others, > > I have primary keys set for each of the table but no indicies (that I am > aware of) as I simply converted the data from our existing database > system which does not support indicies. As my current system only > implements primary keys I have no real experience dealing with indicies, > are they like some sort of extra key column? Are there any guides to > optimising SQLite performance with indicies? > > I tried EXPLAIN QUERY PLAN for the following: > "SELECT * FROM test_item INNER JOIN test_container ON > test_item.container_code = test_container.container_code" > > The output was: > 0|0|TABLE test_item > 1|1|TABLE test_container > > Is there a guide I can check for understanding this output? > > Daniel > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald > Sent: Tuesday, December 02, 2008 9:52 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite performance woe > > Hi Daniel, > > Regarding: >"What I'd like to know is if there is anything we can do with > our queries, SQLite set-up or library configuration to improve the > speed? " > > Unless indicies would be inappropriate, did you mention whether you've > defined any indicies and does EXPLAIN QUERY PLAN show that the proper > index is being used? > > ___ > 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] SQLite performance woe
Hello Donald & Others, I have primary keys set for each of the table but no indicies (that I am aware of) as I simply converted the data from our existing database system which does not support indicies. As my current system only implements primary keys I have no real experience dealing with indicies, are they like some sort of extra key column? Are there any guides to optimising SQLite performance with indicies? I tried EXPLAIN QUERY PLAN for the following: "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code" The output was: 0|0|TABLE test_item 1|1|TABLE test_container Is there a guide I can check for understanding this output? Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: Tuesday, December 02, 2008 9:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Hi Daniel, Regarding: "What I'd like to know is if there is anything we can do with our queries, SQLite set-up or library configuration to improve the speed? " Unless indicies would be inappropriate, did you mention whether you've defined any indicies and does EXPLAIN QUERY PLAN show that the proper index is being used? ___ 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] Journal files
Okay I updated to version 3.6.6.2 and now I am only seeing "PRAGMA main.journal_mode = OFF;" not work with my custom defined OS ( when I switch to windows it works ) I should note that I changed sqlite3.c to sqlite3.cpp and got the amalgamation to compile in C++ I also defined SQLITE_OS_OTHER and wrote my own functions for sqlite3_io_methods and sqlite3_vfs Is there anything else I would need to do that's OS specific to get the "PRAGMA main.journal_mode = OFF;" to work if I define my own OS like I have? >I originally thought "maybe we introduced a bug and broke >journal_mode". But I tried it myself and everything works correctly. >And the regression tests for journal_mode=OFF are running. So I do >not have any idea what you are doing wrong. Everything is working >great here. > > >D. Richard Hipp >[EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
Hi Daniel, Regarding: "What I'd like to know is if there is anything we can do with our queries, SQLite set-up or library configuration to improve the speed? " Unless indicies would be inappropriate, did you mention whether you've defined any indicies and does EXPLAIN QUERY PLAN show that the proper index is being used? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Significance of Sqlite version?
On Dec 2, 2008, at 11:55 AM, Brandon, Nicholas (UK) wrote: > > I note recently that the SQLite version has gone from a 3 point number > (i.e. 3.6.2) to a 4 point number (i.e. 3.6.6.2). 3.6.6.1 and 3.6.6.2 were branch releases to address emergency issues. The next version will be 3.6.7. > > > Should I read any significance into this change? Is there going to be > two strands to development/release of SQLite or will the current > practice of the 'latest is the best' still remain true? > > Regards > Nick > > > > This email and any attachments are confidential to the intended > recipient and may also be privileged. If you are not the intended > recipient please delete it from your system and notify the sender. > You should not copy it or use it for any purpose nor disclose or > distribute its contents to any other person. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Significance of Sqlite version?
I note recently that the SQLite version has gone from a 3 point number (i.e. 3.6.2) to a 4 point number (i.e. 3.6.6.2). Should I read any significance into this change? Is there going to be two strands to development/release of SQLite or will the current practice of the 'latest is the best' still remain true? Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
> > I am not using the amalgamation version of the source as I > have our my > VFS implementations for two of the platforms I work with > based on the > original win_os.c VFS and the amalgamation does not provide > the > necessary header files (os_common.h and sqliteInt.h) to > make VFS > integration possible. Other than by inserting the source > for my VFS > implementations directly into the amalgamation source, > which I'd rather > not do as it would make upgrading to new SQLite versions > much more > complex. > Try using the full source sqlite and amalgamation. Use the source sqlite to create your VFS implementation and libraries. Then use the amalgamated version to create the sqlite libs. Probably will only be a 10% gain max though. Can you tell us what/How your "custom" database works? I'm going to guess that it does not do parsing and is static based upon your types and structures? So thats kind of like comparing apples to oranges. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implicit Indices on Subqueries used as "lookups" or joins ....???
I still consider it a work around for adhoc queries. Programatically I can of course use it easily, but when analysing data one runs many adhoc queires which you change minute on minute. Having to create temp tables for each change and give it a new name for each change is a real pain. Further given platforms like Oracle dont appear to suffer from this problem, I assume (as dangerous as that is) that they actually do create implicit indices. It is a nice to have I agree, but its big win nice to have! The ability to build indices on temp tables already exists in Sqlite, surely it cant be too hard to apply this when building temp tables from subqueries as you must have parsed the join criteria to be able join the tables. Thanks for the reply, S On Tue, Dec 2, 2008 at 3:57 PM, P Kishor <[EMAIL PROTECTED]> wrote: > On 12/2/08, Da Martian <[EMAIL PROTECTED]> wrote: > > Hi > > > > I have continious issues with subquery performance when subqueries are > used > > for joins. It crops up all the time my daily work. > > > > If you create a derived table using a subquery and use it in a join > SQLite > > performance is abysmal. However if you make a temp table from said > subquery > > and index this temp table on the join keys, it goes at incredible speed. > > > > Examples include a query which takes over 2 hours and doesnt complete as > I > > killed it, to running in under 10 seconds if use the temp table pattern. > > > > This pattern of the temp table has to be repeated for almost any data > > analysis I do as SQLite subquery performance with joins is so bad. > > > > To recreate the problem simple create two subqueries which produce say > 100 > > 000 records each with composite integer keys and join them. > > > > e.g > > > > Table1 (Key1, Key2, Key3, Value) > > Table2 (Key1, Key2, Key3, Value) > > > > select * > > from > > (select Key1, Key2, sum(Value) as Value) from Table1 group by Key1, > > Key2) t1 join > > (select Key1, Key2, sum(Value) as Value) from Table2 group by Key1, > > Key2) t2 on > > (t1.Key1 = t2.Key1 and > >t2.Key2 = t2.Key2) > > > > Make sure T1 and Most esp T2 have large volumes of records to highlight > the > > problem, eg. 100 000 each does the job. >2 hours versus 10 seconds on > my > > hardware. > > > > > > Can SQLite be altered to automatically create an index on subqueries > used as > > joins or lookups for the key fields used in the join or lookup. This > would, > > in my experience and opinion make SQLite so much more effective. The > cost in > > time of creating said indices is usually less 1 second on my hardware > and > > examples and saves hours! > > > > > > I have experienced the same, and my solution is exactly as noted > above... programmatically create temp tables with appropriate indexes, > and then query with those temp tables. No need to even drop the temp > tables as they go away when the connection is dropped. > > Works like a charm, so there has been really no need to want to have > core SQLite do the same for me, but I guess it might be nice. > > > -- > Puneet Kishor > ___ > 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] Implicit Indices on Subqueries used as "lookups" or joins ....???
On 12/2/08, Da Martian <[EMAIL PROTECTED]> wrote: > Hi > > I have continious issues with subquery performance when subqueries are used > for joins. It crops up all the time my daily work. > > If you create a derived table using a subquery and use it in a join SQLite > performance is abysmal. However if you make a temp table from said subquery > and index this temp table on the join keys, it goes at incredible speed. > > Examples include a query which takes over 2 hours and doesnt complete as I > killed it, to running in under 10 seconds if use the temp table pattern. > > This pattern of the temp table has to be repeated for almost any data > analysis I do as SQLite subquery performance with joins is so bad. > > To recreate the problem simple create two subqueries which produce say 100 > 000 records each with composite integer keys and join them. > > e.g > > Table1 (Key1, Key2, Key3, Value) > Table2 (Key1, Key2, Key3, Value) > > select * > from > (select Key1, Key2, sum(Value) as Value) from Table1 group by Key1, > Key2) t1 join > (select Key1, Key2, sum(Value) as Value) from Table2 group by Key1, > Key2) t2 on > (t1.Key1 = t2.Key1 and >t2.Key2 = t2.Key2) > > Make sure T1 and Most esp T2 have large volumes of records to highlight the > problem, eg. 100 000 each does the job. >2 hours versus 10 seconds on my > hardware. > > > Can SQLite be altered to automatically create an index on subqueries used as > joins or lookups for the key fields used in the join or lookup. This would, > in my experience and opinion make SQLite so much more effective. The cost in > time of creating said indices is usually less 1 second on my hardware and > examples and saves hours! > > I have experienced the same, and my solution is exactly as noted above... programmatically create temp tables with appropriate indexes, and then query with those temp tables. No need to even drop the temp tables as they go away when the connection is dropped. Works like a charm, so there has been really no need to want to have core SQLite do the same for me, but I guess it might be nice. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Implicit Indices on Subqueries used as "lookups" or joins ....???
Hi I have continious issues with subquery performance when subqueries are used for joins. It crops up all the time my daily work. If you create a derived table using a subquery and use it in a join SQLite performance is abysmal. However if you make a temp table from said subquery and index this temp table on the join keys, it goes at incredible speed. Examples include a query which takes over 2 hours and doesnt complete as I killed it, to running in under 10 seconds if use the temp table pattern. This pattern of the temp table has to be repeated for almost any data analysis I do as SQLite subquery performance with joins is so bad. To recreate the problem simple create two subqueries which produce say 100 000 records each with composite integer keys and join them. e.g Table1 (Key1, Key2, Key3, Value) Table2 (Key1, Key2, Key3, Value) select * from (select Key1, Key2, sum(Value) as Value) from Table1 group by Key1, Key2) t1 join (select Key1, Key2, sum(Value) as Value) from Table2 group by Key1, Key2) t2 on (t1.Key1 = t2.Key1 and t2.Key2 = t2.Key2) Make sure T1 and Most esp T2 have large volumes of records to highlight the problem, eg. 100 000 each does the job. >2 hours versus 10 seconds on my hardware. Can SQLite be altered to automatically create an index on subqueries used as joins or lookups for the key fields used in the join or lookup. This would, in my experience and opinion make SQLite so much more effective. The cost in time of creating said indices is usually less 1 second on my hardware and examples and saves hours! Thanks, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C Function call in a Trigger
"Nadeem Iftikhar"
<[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> I am trying to call a C function in a sqlite 2 trigger.
>
> Here is the code
[snipped]
> trigger|example|contacts|0|CREATE TRIGGER example
> AFTER INSERT ON contacts
> BEGIN
> SELECT altcaps('this is a test');
> END
>
> sqlite>insert into contacts values(2);
> SQL error: no such function: altcaps
You seem to think that the code for the function would be embedded into
the database somehow. This is not the case. The database you've created
is only usable by a program that, right after opening a connection,
registers a custom function named altcaps. sqlite command line shell
doesn't do that, naturally.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization for inner table join
"Jos van den Oever" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > There's two tables with the same problem. One has an undetermined > number of values: 'm' points to user-definable tag. > In the other table I have about 110 values. This could be spread over > two integer columns. I'm a bit hesitant to use integer values as > bitmasks. How is the signedness handled in the binding? Should I > simply use a uint64_t and not worry? I believe it would just work. But, if you think that would be a problem, you can use only 63 bits. Two columns will still cover 126 possible values. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C Function call in a Trigger
I am trying to call a C function in a sqlite 2 trigger.
Here is the code
'''
#include
#include
#include
void capitalize_alternate(sqlite_func *context, int argc, const char **argv)
{
int i;
static char str[80];
for (i=0; i select * from sqlite_master;
table|contacts|contacts|3|create table contacts (int a)
table|requests|requests|4|create table requests(a int)
trigger|example|contacts|0|CREATE TRIGGER example
AFTER INSERT ON contacts
BEGIN
SELECT altcaps('this is a test');
END
sqlite>insert into contacts values(2);
SQL error: no such function: altcaps
sqlite>
Here it says no such function
'
Could anyone help me with that please.
Regards,
Nick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the sqlite3_bind* result
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 schleg wrote: > Anyone know if there's a way to get the actual query that results from > calling sqlite3_bind*? The bindings don't change the query (ie there is no printf equivalent happening behind the scenes). If you want to know what bindings you used, your code will have to remember that. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkk1EDIACgkQmOOfHg372QSlbwCgneoWiJL0EvASdX4ECHVwadXD JJIAnjXHE8DXzT5GfryTL2nvumOV1w2m =vrgD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting the sqlite3_bind* result
Anyone know if there's a way to get the actual query that results from calling sqlite3_bind*? I just want to be able to log it after the parameters have been evaluated. Thanks! -- View this message in context: http://www.nabble.com/Getting-the-sqlite3_bind*-result-tp20788369p20788369.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization for inner table join
2008/12/2 Igor Tandetnik <[EMAIL PROTECTED]>: > You could also try something more straightforward: > > select distinct n from map m1 where >exists (select 1 from map m2 where m1.n=m2.n and m2.m=3) and >exists (select 1 from map m2 where m1.n=m2.n and m2.m=5) and >not exists (select 1 from map m2 where m1.n=m2.n and m2.m=7); > > -- or > > select distinct n from map where >n in (select n from map where m=3) and >n in (select n from map where m=5) and >n not in (select n from map where m=7); This would have a worse worst case scenario, but by cleverly ordering the inclusive statements from infrequent to frequent and the exclusive ones from frequent to infrequent this could be improved. I'd have to do a select m, count(m) from map group by m; to get the info I need for that. > If you need to run this kind of query often, and values of m are small > (preferably less than 64), you might want to store a map from n to a > bitmask where each bit corresponds to one value of m. Then the query > becomes simply > > select n from map > where (n & 168) = 40; > > This is going to be linear, but in the number of distinct values of n, > not in the number of all pairs. There's two tables with the same problem. One has an undetermined number of values: 'm' points to user-definable tag. In the other table I have about 110 values. This could be spread over two integer columns. I'm a bit hesitant to use integer values as bitmasks. How is the signedness handled in the binding? Should I simply use a uint64_t and not worry? Cheers, Jos ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
