Re: [sqlite] Direct access of table data

[Nathan Biggs]

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

Re: [sqlite] Direct access of table data

[Gerry Snyder]

How long would it take to test using an index with 18 fields? Might
not be crazy.

Gerry

On 4/23/10, Nathan Biggs  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

[Black, Michael (IS)]

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

[Nathan Biggs]

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

[Black, Michael (IS)]

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

[Nathan Biggs]

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

[Max Vlasov]

> ...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

[Nathan Biggs]

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 Biggs  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

Re: [sqlite] Direct access of table data

[P Kishor]

On Wed, Apr 21, 2010 at 1:19 PM, Nathan Biggs  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

[Pavel Ivanov]

> 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  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

[Griggs, Donald]

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

[sqlite] Direct access of table data

[Nathan Biggs]

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