Re: [sqlite] Direct access of table data
...As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. Nathan, maybe you already knew but just in case... if your select relies on an index and also queries fields not presented in that index, consider appending these extra fields to the index. It doesn't make sense in terms of search speed, (moreover it will increas the db size), but this will save time since no extra lookup will be taking place. So if you have table CREATE TABLE a, b, c, and index CREATE INDEX ON a, b and use query similar to SELECT a, b, c ... WHERE a= and b = sqlite will do extra lookup to get c from the table, but if you change the index to CREATE INDEX ON a, b, c the same query will get all the data from the index itself saving time and the amount of data flow. I did a quick test and it showed not only a noticable difference in time, but also a significant difference in amount of the data read. Max, maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access of table data
Max, thanks for the information. That will be very useful for other table queries, but not for this one. For my table in questions there are 18 fields. I think an index with 18 fields would be a little crazy. On 4/23/2010 3:06 AM, Max Vlasov wrote: ...As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. Nathan, maybe you already knew but just in case... if your select relies on an index and also queries fields not presented in that index, consider appending these extra fields to the index. It doesn't make sense in terms of search speed, (moreover it will increas the db size), but this will save time since no extra lookup will be taking place. So if you have table CREATE TABLE a, b, c, and index CREATE INDEX ON a, b and use query similar to SELECT a, b, c ... WHERE a= and b = sqlite will do extra lookup to get c from the table, but if you change the index to CREATE INDEX ON a, b, c the same query will get all the data from the index itself saving time and the amount of data flow. I did a quick test and it showed not only a noticable difference in time, but also a significant difference in amount of the data read. Max, maxerist.net ___ 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] Direct access of table data
If insert speed is important are you doing batch inserts? If so, you want to do a BEGIN/COMMIT to speed up your inserts a LOT. Default action is to defer which mean no database locks occur during your inserts. http://www.sqlite.org/lang_transaction.html Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 7:50 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Max, thanks for the information. That will be very useful for other table queries, but not for this one. For my table in questions there are 18 fields. I think an index with 18 fields would be a little crazy. On 4/23/2010 3:06 AM, Max Vlasov wrote: ...As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. Nathan, maybe you already knew but just in case... if your select relies on an index and also queries fields not presented in that index, consider appending these extra fields to the index. It doesn't make sense in terms of search speed, (moreover it will increas the db size), but this will save time since no extra lookup will be taking place. So if you have table CREATE TABLE a, b, c, and index CREATE INDEX ON a, b and use query similar to SELECT a, b, c ... WHERE a= and b = sqlite will do extra lookup to get c from the table, but if you change the index to CREATE INDEX ON a, b, c the same query will get all the data from the index itself saving time and the amount of data flow. I did a quick test and it showed not only a noticable difference in time, but also a significant difference in amount of the data read. Max, maxerist.net ___ 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] Direct access of table data
Yes, I do use batch inserts. On 4/23/2010 8:56 AM, Black, Michael (IS) wrote: If insert speed is important are you doing batch inserts? If so, you want to do a BEGIN/COMMIT to speed up your inserts a LOT. Default action is to defer which mean no database locks occur during your inserts. http://www.sqlite.org/lang_transaction.html Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 7:50 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Max, thanks for the information. That will be very useful for other table queries, but not for this one. For my table in questions there are 18 fields. I think an index with 18 fields would be a little crazy. On 4/23/2010 3:06 AM, Max Vlasov wrote: ...As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. Nathan, maybe you already knew but just in case... if your select relies on an index and also queries fields not presented in that index, consider appending these extra fields to the index. It doesn't make sense in terms of search speed, (moreover it will increas the db size), but this will save time since no extra lookup will be taking place. So if you have table CREATE TABLE a, b, c, and index CREATE INDEX ON a, b and use query similar to SELECT a, b, c ... WHERE a= and b = sqlite will do extra lookup to get c from the table, but if you change the index to CREATE INDEX ON a, b, c the same query will get all the data from the index itself saving time and the amount of data flow. I did a quick test and it showed not only a noticable difference in time, but also a significant difference in amount of the data read. Max, maxerist.net ___ 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] Direct access of table data
So try creating your additional index, use the BEGIN/COMMIT, and let us know how it performs for you in comparison. I'll bet its faster even with the new index. You didn't say if you were already doing the BEGIN/COMMIT. Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 8:05 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Yes, I do use batch inserts. On 4/23/2010 8:56 AM, Black, Michael (IS) wrote: If insert speed is important are you doing batch inserts? If so, you want to do a BEGIN/COMMIT to speed up your inserts a LOT. Default action is to defer which mean no database locks occur during your inserts. http://www.sqlite.org/lang_transaction.html Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 7:50 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Max, thanks for the information. That will be very useful for other table queries, but not for this one. For my table in questions there are 18 fields. I think an index with 18 fields would be a little crazy. On 4/23/2010 3:06 AM, Max Vlasov wrote: ...As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. Nathan, maybe you already knew but just in case... if your select relies on an index and also queries fields not presented in that index, consider appending these extra fields to the index. It doesn't make sense in terms of search speed, (moreover it will increas the db size), but this will save time since no extra lookup will be taking place. So if you have table CREATE TABLE a, b, c, and index CREATE INDEX ON a, b and use query similar to SELECT a, b, c ... WHERE a= and b = sqlite will do extra lookup to get c from the table, but if you change the index to CREATE INDEX ON a, b, c the same query will get all the data from the index itself saving time and the amount of data flow. I did a quick test and it showed not only a noticable difference in time, but also a significant difference in amount of the data read. Max, maxerist.net ___ 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] Direct access of table data
How long would it take to test using an index with 18 fields? Might not be crazy. Gerry On 4/23/10, Nathan Biggs nbi...@mycfs.com wrote: Max, thanks for the information. That will be very useful for other table queries, but not for this one. For my table in questions there are 18 fields. I think an index with 18 fields would be a little crazy. On 4/23/2010 3:06 AM, Max Vlasov wrote: ...As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. Nathan, maybe you already knew but just in case... if your select relies on an index and also queries fields not presented in that index, consider appending these extra fields to the index. It doesn't make sense in terms of search speed, (moreover it will increas the db size), but this will save time since no extra lookup will be taking place. So if you have table CREATE TABLE a, b, c, and index CREATE INDEX ON a, b and use query similar to SELECT a, b, c ... WHERE a= and b = sqlite will do extra lookup to get c from the table, but if you change the index to CREATE INDEX ON a, b, c the same query will get all the data from the index itself saving time and the amount of data flow. I did a quick test and it showed not only a noticable difference in time, but also a significant difference in amount of the data read. Max, maxerist.net ___ 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 -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access of table data
I am using the Begin and Commit. On 4/23/2010 9:04 AM, Black, Michael (IS) wrote: So try creating your additional index, use the BEGIN/COMMIT, and let us know how it performs for you in comparison. I'll bet its faster even with the new index. You didn't say if you were already doing the BEGIN/COMMIT. Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 8:05 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Yes, I do use batch inserts. On 4/23/2010 8:56 AM, Black, Michael (IS) wrote: If insert speed is important are you doing batch inserts? If so, you want to do a BEGIN/COMMIT to speed up your inserts a LOT. Default action is to defer which mean no database locks occur during your inserts. http://www.sqlite.org/lang_transaction.html Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 7:50 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Max, thanks for the information. That will be very useful for other table queries, but not for this one. For my table in questions there are 18 fields. I think an index with 18 fields would be a little crazy. On 4/23/2010 3:06 AM, Max Vlasov wrote: ...As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. Nathan, maybe you already knew but just in case... if your select relies on an index and also queries fields not presented in that index, consider appending these extra fields to the index. It doesn't make sense in terms of search speed, (moreover it will increas the db size), but this will save time since no extra lookup will be taking place. So if you have table CREATE TABLE a, b, c, and index CREATE INDEX ON a, b and use query similar to SELECT a, b, c ... WHERE a= and b = sqlite will do extra lookup to get c from the table, but if you change the index to CREATE INDEX ON a, b, c the same query will get all the data from the index itself saving time and the amount of data flow. I did a quick test and it showed not only a noticable difference in time, but also a significant difference in amount of the data read. Max, maxerist.net ___ 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] Direct access of table data
Is there a way to read the values of a table directly without building and executing a query. I have a function that has predefined memory (counters) and increments them if the data in the record matches a hard coded value. Since this is hard-coded, I thought it might perform much without all of the memory allocations/de-allocations associated with the query engine. Thanks for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access of table data
Hello Biggs, this is Griggs, Re: Is there a way to read the values of a table directly without building and executing a query. I have a function that has predefined memory... No easy way. The sophistication of database abstraction is the reason you presumably chose to use sqlite. Re: I thought it might perform much [better] without all ... If you've measured this and the query truly takes too long, you might try (in order): 1) trying to optimize the query (do you have ideal indexing, for instance?) 2) restructure your data/program 3) use a simple file rather than a database Regards, Donald Griggs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access of table data
Since this is hard-coded, I thought it might perform much without all of the memory allocations/de-allocations associated with the query engine. Don't use query engine then and store the information in separate file with your own hard-coded format. Pavel On Wed, Apr 21, 2010 at 2:19 PM, Nathan Biggs nbi...@mycfs.com wrote: Is there a way to read the values of a table directly without building and executing a query. I have a function that has predefined memory (counters) and increments them if the data in the record matches a hard coded value. Since this is hard-coded, I thought it might perform much without all of the memory allocations/de-allocations associated with the query engine. Thanks for your help. ___ 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] Direct access of table data
On Wed, Apr 21, 2010 at 1:19 PM, Nathan Biggs nbi...@mycfs.com wrote: Is there a way to read the values of a table directly without building and executing a query. I have a function that has predefined memory (counters) and increments them if the data in the record matches a hard coded value. Since this is hard-coded, I thought it might perform much without all of the memory allocations/de-allocations associated with the query engine. Others have already responded, but in questions like this, my standard response is... measure. If you think it might, it is time to measure, do a test. Only you can do a test that is meaningful to you. Of course, you can and should ask for ways to make your queries more efficient, but in the end, a test on your hardware, in your environment, is the only thing that matters. That said, why choose a sql database, and then want to bypass its sql engine to access the data directly? Even if you could, why would you? Why not just begin with a more appropriate data store in the first place? If you don't need sql, just go ahead and choose a key-value db... Berkeley DB is free for the taking... try Tokyo Cabinet. I am sure there are others. Thanks for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access of table data
Thanks for the input. Like everything else in life there are trade-offs. I guess that the real question originates because the time it takes to query the data seems a lot faster when I use a simplier select statement. As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. There is only one instance where this functionality would be handy anyway. In 48 other instances the ability to query the database is imperative. Having said that SQLite is light-years faster than other databases I have used for this project. Good job to everyone who helps to support it. Keep up the good work! On 4/21/2010 3:28 PM, P Kishor wrote: On Wed, Apr 21, 2010 at 1:19 PM, Nathan Biggsnbi...@mycfs.com wrote: Is there a way to read the values of a table directly without building and executing a query. I have a function that has predefined memory (counters) and increments them if the data in the record matches a hard coded value. Since this is hard-coded, I thought it might perform much without all of the memory allocations/de-allocations associated with the query engine. Others have already responded, but in questions like this, my standard response is... measure. If you think it might, it is time to measure, do a test. Only you can do a test that is meaningful to you. Of course, you can and should ask for ways to make your queries more efficient, but in the end, a test on your hardware, in your environment, is the only thing that matters. That said, why choose a sql database, and then want to bypass its sql engine to access the data directly? Even if you could, why would you? Why not just begin with a more appropriate data store in the first place? If you don't need sql, just go ahead and choose a key-value db... Berkeley DB is free for the taking... try Tokyo Cabinet. I am sure there are others. Thanks for your help. ___ 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
