Re: [sqlite] How rebuild with larger page size from command line?

2008-12-02 Thread Jerry Krinock

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?

2008-12-02 Thread Thomas Briggs
   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?

2008-12-02 Thread P Kishor
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?

2008-12-02 Thread Jerry Krinock

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

2008-12-02 Thread Brown, Daniel
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?

2008-12-02 Thread Thomas Briggs
   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?

2008-12-02 Thread Jerry Krinock
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

2008-12-02 Thread John Stanton
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()

2008-12-02 Thread Igor Tandetnik
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()

2008-12-02 Thread P Kishor
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

2008-12-02 Thread sqlite
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()

2008-12-02 Thread Greg Robertson
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

2008-12-02 Thread Griggs, Donald
 

-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

2008-12-02 Thread Griggs, Donald
 

-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

2008-12-02 Thread Brown, Daniel
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

2008-12-02 Thread John Stanton
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

2008-12-02 Thread Brown, Daniel
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

2008-12-02 Thread Stephen Abbamonte
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

2008-12-02 Thread Griggs, Donald
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?

2008-12-02 Thread D. Richard Hipp

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?

2008-12-02 Thread Brandon, Nicholas (UK)

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

2008-12-02 Thread Ken



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

2008-12-02 Thread Da Martian
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 ....???

2008-12-02 Thread P Kishor
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 ....???

2008-12-02 Thread Da Martian
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

2008-12-02 Thread Igor Tandetnik
"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

2008-12-02 Thread Igor Tandetnik
"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

2008-12-02 Thread Nadeem Iftikhar
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

2008-12-02 Thread Roger Binns
-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

2008-12-02 Thread schleg

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-02 Thread Jos van den Oever
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