Re: [sqlite] configure syntax error on HP
On Jun 21, 2008, at 1:27 AM, Matt Sergeant wrote: > On Thu, 19 Jun 2008 12:05:56 -0400, D. Richard Hipp wrote: >> >> On Jun 19, 2008, at 11:49 AM, Matt Sergeant wrote: >>> >>> Note that there are some C++ style comments crept back into the code >>> (I >>> noticed in the amalgamation, so I can't give you a direct pointer to >>> them). This causes compile failures on stricter C compilers. >> >> >> Already been fixed. http://www.sqlite.org/cvstrac/chngview?cn=5207 >> and http://www.sqlite.org/cvstrac/tktview?tn=3172 > > Good. You might want to consider adding a test for this - my > DBD::SQLite does one, but I'd rather you catch things upstream. How do you test this? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] printf-8.2 test failure on Solaris on SPARC 64-bit
> printf-8.2...
> Expected: [2147483647 2147483648 4294967295]
> Got: [2147483647 18446744071562067968 18446744073709551615]
>
> The code looks like:
>
>
> ...
> do_test printf-8.2 {
> sqlite3_mprintf_int {%lu %lu %lu} 0x7fff 0x8000 0x
> } {2147483647 2147483648 4294967295}
> ...
>
> where sqlite3_mprintf_int() is a Tcl function written in C that passes
> signed ints to a printf-like function with a format string that uses
> %lu. I think here we have sign extension going on. To me it seems
> clear that there's a bug in sqlite3_mprintf_int() -- why use %lu?
I agree that you are on the right track-- the format doesn't portably
match the values. However, I think the %lu part is correct-- "long" is
the only C type guaranteed to be at least 32 bits. Instead, I think the
issue is that the hex constants are not explicitly specified as longs,
so the compiler is treating them as normal int's, causing the mismatch.
Rather than a sign extension problem, I believe the compiler is reading
8 bytes of parameter data from the stack for each %lu, versus the 4
bytes supplied. As confirmation of this, note that 18446744071562067968
= 8000 hex-- the 2nd and 3rd parameters combined.
I think it's a simple matter of adding the 'L' suffix to the constants.
I.e., 0x7fffL, 0x8000L, etc. This should work portably across
32/64 bit platforms.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MonoDevelop Data Provider
I am trying to make my first MonoDevelop program using SQLite as the database. I have been unable to find a data provider. Does anyone know of a SQLite data provider for MonoDevelop 1.0? If there is one, an example of how to incorporate it into a MonoDevelop program would be appreciated. I am new to both MonoDevelop and SQLite so have very little to go on. Thanks. Dick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
Try to do an explain on the query you are running. Make sure its the same on windows and the hp system. (it should be using the same index). On HP, the default sector size is 1k. You might try rebuilding your database to use a block size of 1k or greater. If you are using veritas filesystem or other filesystem then the block size can be 8k. Tune that accordingly. Try increasing the cache_size as well. HTH. Andrea Connell <[EMAIL PROTECTED]> wrote: I understand that the machines will perform differently, but that doesn't explain the differences I am seeing. On Windows SQLite can do 1,000 rows of input in less than a second and our in house system can do it in just over a second. They are fairly comparable at this point. When I increase the rows of input to 10,000 SQLite can do it in 5 seconds and our in house system takes 12. 100,000 rows are done in 54 seconds and 121 seconds, respectively. So from these results you can see that SQLite is about twice as fast as our system on Windows. However, on HP SQLite takes 30 seconds to do 1,000 rows and our system can do them in just 4 seconds. Clearly it is not the machine itself that is 30 times slower. If our system took 60 seconds to perform the queries, it would make sense but that is not at all the case. That is why I am wondering if there is something else in SQLite that might be causing the drastic slow down on HP. Sorry if that wasn't totally clear from my first message. Any thoughts? Andrea -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Friday, June 20, 2008 12:08 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance on HP You are measuring the speed of the respective machines. Benchmark each one to get relative performance. Andrea Connell wrote: > > Sorry somehow I sent that before I was quite finished. I'm just > wondering if there is anything else I should try. About 30,000 rows > are found in the end, and it can do this on Windows in less than a second. > I'm convinced it shouldn't take 30 seconds on HP. I know the OS's > caching method will make a difference, but should it be this dramatic? > > Thanks, > Andrea > > -Original Message- > From: Andrea Connell > Sent: Friday, June 20, 2008 11:23 AM > To: sqlite-users@sqlite.org > Subject: Performance on HP > > > Now that I have SQLite compiled on HP, I am starting to test > performance. So far it's pretty disappointing though. > > I am comparing performance of SQLite versus an in-house directory > access system. I have the same table structure and data for each of > them. The code reads some data from an input file and searches the > database for that row, then finds all rows from other tables > associated with the first row. > > On Windows, with a cold cache SQLite is a bit slower than the inhouse > system, but after an initial select it runs twice as fast. > On HP our inhouse system can run through 1000 rows in input in 4 > seconds on average. SQLite is taking about 30 seconds with the same > input to find the same results. > > CREATE TABLE LEVEL1 ( ADDRESS_TYPE text, COUNTRY_ID text, DATA_SOURCE > text, DIR_SEARCH_AREA1 integer, ... PHONETIC_KEY text, ... , > RECORDKEY integer); > > CREATE TABLE LEVEL2 ( ... PRIM_NBR_HIGH text, PRIM_NBR_LOW text, ... > RECORDKEY integer, PARENT_KEY integer ); > > CREATE TABLE LEVEL3 ( ... RECORDKEY integer, ... PARENT_KEY integer > ); > > CREATE INDEX L1_INDEX ON LEVEL1 (COUNTRY_ID, DIR_SEARCH_AREA1, > ADDRESS_TYPE, PHONETIC_KEY); CREATE INDEX L2_RANGE_INDEX ON LEVEL2 > (PARENT_KEY, PRIM_NBR_HIGH, PRIM_NBR_LOW); CREATE INDEX L3_INDEX ON > LEVEL3 (PARENT_KEY); > > I also reordered on these indexes to create the clustered index. > > On Windows I had set the page_size PRAGMA to 4096 before creating the > tables, but I think on HP they are 1024 so that wouldn't help? > > My program uses the following queries to find the info and binds the > appropriate data from the input file. > > char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND > DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY >= ? AND > PHONETIC_KEY < ? ;"; char * qry2 = "SELECT * FROM LEVEL2 WHERE > PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; > char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;"; > > ___ > 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
Re: [sqlite] SQLite with client/server support
В сообщении от Friday 20 June 2008 03:00:21 Stephen Woodbridge написал(а): > This looks interesting: > http://sqlitedbms.sourceforge.net/index.htm > > Alexey would this work for your multi-master replication? Maybe you > modify this to work for the specific task you have in mind. May be I can solve my problem by this schema: Host A: attach read/write A.db (master A) and read-only B.db (slave B) Host B: attach read/write B.db (master B) and read-only A.db (slave A) Master-slave replication is more simple than master-master and was discussed often here. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
Andrea Connell wrote: > I was originally using LIKE but since that can't make use of indexing I > found this as an alternative to attempt to speed up the query. > > So if I wanted to search PHONETIC_KEY LIKE 'ABCD%' I replace it with > PHONETIC_KEY >= 'ABCD' AND PHONETIC_KEY < 'ABCE' > The optimizer in SQLite will use an index for a like clause like your example. See section 4 of http://www.sqlite.org/optoverview.html for details. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
I understand that the machines will perform differently, but that doesn't explain the differences I am seeing. On Windows SQLite can do 1,000 rows of input in less than a second and our in house system can do it in just over a second. They are fairly comparable at this point. When I increase the rows of input to 10,000 SQLite can do it in 5 seconds and our in house system takes 12. 100,000 rows are done in 54 seconds and 121 seconds, respectively. So from these results you can see that SQLite is about twice as fast as our system on Windows. However, on HP SQLite takes 30 seconds to do 1,000 rows and our system can do them in just 4 seconds. Clearly it is not the machine itself that is 30 times slower. If our system took 60 seconds to perform the queries, it would make sense but that is not at all the case. That is why I am wondering if there is something else in SQLite that might be causing the drastic slow down on HP. Sorry if that wasn't totally clear from my first message. Any thoughts? Andrea -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Friday, June 20, 2008 12:08 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance on HP You are measuring the speed of the respective machines. Benchmark each one to get relative performance. Andrea Connell wrote: > > Sorry somehow I sent that before I was quite finished. I'm just > wondering if there is anything else I should try. About 30,000 rows > are found in the end, and it can do this on Windows in less than a second. > I'm convinced it shouldn't take 30 seconds on HP. I know the OS's > caching method will make a difference, but should it be this dramatic? > > Thanks, > Andrea > > -Original Message- > From: Andrea Connell > Sent: Friday, June 20, 2008 11:23 AM > To: sqlite-users@sqlite.org > Subject: Performance on HP > > > Now that I have SQLite compiled on HP, I am starting to test > performance. So far it's pretty disappointing though. > > I am comparing performance of SQLite versus an in-house directory > access system. I have the same table structure and data for each of > them. The code reads some data from an input file and searches the > database for that row, then finds all rows from other tables > associated with the first row. > > On Windows, with a cold cache SQLite is a bit slower than the inhouse > system, but after an initial select it runs twice as fast. > On HP our inhouse system can run through 1000 rows in input in 4 > seconds on average. SQLite is taking about 30 seconds with the same > input to find the same results. > > CREATE TABLE LEVEL1 ( ADDRESS_TYPE text, COUNTRY_ID text, DATA_SOURCE > text, DIR_SEARCH_AREA1 integer, ... PHONETIC_KEY text, ... , > RECORDKEY integer); > > CREATE TABLE LEVEL2 ( ... PRIM_NBR_HIGH text, PRIM_NBR_LOW text, ... > RECORDKEY integer, PARENT_KEY integer ); > > CREATE TABLE LEVEL3 ( ... RECORDKEY integer, ... PARENT_KEY integer > ); > > CREATE INDEX L1_INDEX ON LEVEL1 (COUNTRY_ID, DIR_SEARCH_AREA1, > ADDRESS_TYPE, PHONETIC_KEY); CREATE INDEX L2_RANGE_INDEX ON LEVEL2 > (PARENT_KEY, PRIM_NBR_HIGH, PRIM_NBR_LOW); CREATE INDEX L3_INDEX ON > LEVEL3 (PARENT_KEY); > > I also reordered on these indexes to create the clustered index. > > On Windows I had set the page_size PRAGMA to 4096 before creating the > tables, but I think on HP they are 1024 so that wouldn't help? > > My program uses the following queries to find the info and binds the > appropriate data from the input file. > > char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND > DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY >= ? AND > PHONETIC_KEY < ? ;"; char * qry2 = "SELECT * FROM LEVEL2 WHERE > PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; > char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;"; > > ___ > 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] configure syntax error on HP
On Thu, 19 Jun 2008 12:05:56 -0400, D. Richard Hipp wrote: > > On Jun 19, 2008, at 11:49 AM, Matt Sergeant wrote: >> >> Note that there are some C++ style comments crept back into the code >> (I >> noticed in the amalgamation, so I can't give you a direct pointer to >> them). This causes compile failures on stricter C compilers. > > > Already been fixed. http://www.sqlite.org/cvstrac/chngview?cn=5207 > and http://www.sqlite.org/cvstrac/tktview?tn=3172 Good. You might want to consider adding a test for this - my DBD::SQLite does one, but I'd rather you catch things upstream. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
I was originally using LIKE but since that can't make use of indexing I found this as an alternative to attempt to speed up the query. So if I wanted to search PHONETIC_KEY LIKE 'ABCD%' I replace it with PHONETIC_KEY >= 'ABCD' AND PHONETIC_KEY < 'ABCE' -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, June 20, 2008 12:28 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance on HP Hi Andrea, I'm interested in your query: Andrea Connell wrote: > char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND > DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY >= ? AND > PHONETIC_KEY < ? ;"; > > char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ? AND > PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; > char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;"; > Are there >= and < on purpose to do lexicographic string ordering? Or even some kind of Soundex code? Once they caused table scanning, SQLite page loading may increase? Best Regards, /Mike/ ___ 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] Performance on HP
Hi Andrea, I'm interested in your query: Andrea Connell wrote: > char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND > DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY >= ? AND > PHONETIC_KEY < ? ;"; > > char * qry2 = "SELECT * FROM LEVEL2 WHERE > PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; > char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;"; > Are there >= and < on purpose to do lexicographic string ordering? Or even some kind of Soundex code? Once they caused table scanning, SQLite page loading may increase? Best Regards, /Mike/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
You are measuring the speed of the respective machines. Benchmark each one to get relative performance. Andrea Connell wrote: > > Sorry somehow I sent that before I was quite finished. I'm just > wondering if there is anything else I should try. About 30,000 rows are > found in the end, and it can do this on Windows in less than a second. > I'm convinced it shouldn't take 30 seconds on HP. I know the OS's > caching method will make a difference, but should it be this dramatic? > > Thanks, > Andrea > > -Original Message- > From: Andrea Connell > Sent: Friday, June 20, 2008 11:23 AM > To: sqlite-users@sqlite.org > Subject: Performance on HP > > > Now that I have SQLite compiled on HP, I am starting to test > performance. So far it's pretty disappointing though. > > I am comparing performance of SQLite versus an in-house directory access > system. I have the same table structure and data for each of them. The > code reads some data from an input file and searches the database for > that row, then finds all rows from other tables associated with the > first row. > > On Windows, with a cold cache SQLite is a bit slower than the inhouse > system, but after an initial select it runs twice as fast. > On HP our inhouse system can run through 1000 rows in input in 4 seconds > on average. SQLite is taking about 30 seconds with the same input to > find the same results. > > CREATE TABLE LEVEL1 ( ADDRESS_TYPE text, COUNTRY_ID text, DATA_SOURCE > text, DIR_SEARCH_AREA1 integer, ... PHONETIC_KEY text, ... , RECORDKEY > integer); > > CREATE TABLE LEVEL2 ( ... PRIM_NBR_HIGH text, PRIM_NBR_LOW text, ... > RECORDKEY integer, PARENT_KEY integer ); > > CREATE TABLE LEVEL3 ( ... RECORDKEY integer, ... PARENT_KEY integer ); > > CREATE INDEX L1_INDEX ON LEVEL1 (COUNTRY_ID, DIR_SEARCH_AREA1, > ADDRESS_TYPE, PHONETIC_KEY); CREATE INDEX L2_RANGE_INDEX ON LEVEL2 > (PARENT_KEY, PRIM_NBR_HIGH, PRIM_NBR_LOW); CREATE INDEX L3_INDEX ON > LEVEL3 (PARENT_KEY); > > I also reordered on these indexes to create the clustered index. > > On Windows I had set the page_size PRAGMA to 4096 before creating the > tables, but I think on HP they are 1024 so that wouldn't help? > > My program uses the following queries to find the info and binds the > appropriate data from the input file. > > char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND > DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY >= ? AND > PHONETIC_KEY < ? ;"; char * qry2 = "SELECT * FROM LEVEL2 WHERE > PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; > char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;"; > > ___ > 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] printf-8.2 test failure on Solaris on SPARC 64-bit
I ran into this with SQLite3 3.5.4:
printf-8.2...
Expected: [2147483647 2147483648 4294967295]
Got: [2147483647 18446744071562067968 18446744073709551615]
The code looks like:
...
do_test printf-8.2 {
sqlite3_mprintf_int {%lu %lu %lu} 0x7fff 0x8000 0x
} {2147483647 2147483648 4294967295}
...
where sqlite3_mprintf_int() is a Tcl function written in C that passes
signed ints to a printf-like function with a format string that uses
%lu. I think here we have sign extension going on. To me it seems
clear that there's a bug in sqlite3_mprintf_int() -- why use %lu?
Interenstingly I don't get the same failure on Solaris on x86/64
64-bit. Odd.
I should point out, I suppose, that Solaris is ILP32 and LP64 on all
architectures.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
Sorry somehow I sent that before I was quite finished. I'm just wondering if there is anything else I should try. About 30,000 rows are found in the end, and it can do this on Windows in less than a second. I'm convinced it shouldn't take 30 seconds on HP. I know the OS's caching method will make a difference, but should it be this dramatic? Thanks, Andrea -Original Message- From: Andrea Connell Sent: Friday, June 20, 2008 11:23 AM To: sqlite-users@sqlite.org Subject: Performance on HP Now that I have SQLite compiled on HP, I am starting to test performance. So far it's pretty disappointing though. I am comparing performance of SQLite versus an in-house directory access system. I have the same table structure and data for each of them. The code reads some data from an input file and searches the database for that row, then finds all rows from other tables associated with the first row. On Windows, with a cold cache SQLite is a bit slower than the inhouse system, but after an initial select it runs twice as fast. On HP our inhouse system can run through 1000 rows in input in 4 seconds on average. SQLite is taking about 30 seconds with the same input to find the same results. CREATE TABLE LEVEL1 ( ADDRESS_TYPE text, COUNTRY_ID text, DATA_SOURCE text, DIR_SEARCH_AREA1 integer, ... PHONETIC_KEY text, ... , RECORDKEY integer); CREATE TABLE LEVEL2 ( ... PRIM_NBR_HIGH text, PRIM_NBR_LOW text, ... RECORDKEY integer, PARENT_KEY integer ); CREATE TABLE LEVEL3 ( ... RECORDKEY integer, ... PARENT_KEY integer ); CREATE INDEX L1_INDEX ON LEVEL1 (COUNTRY_ID, DIR_SEARCH_AREA1, ADDRESS_TYPE, PHONETIC_KEY); CREATE INDEX L2_RANGE_INDEX ON LEVEL2 (PARENT_KEY, PRIM_NBR_HIGH, PRIM_NBR_LOW); CREATE INDEX L3_INDEX ON LEVEL3 (PARENT_KEY); I also reordered on these indexes to create the clustered index. On Windows I had set the page_size PRAGMA to 4096 before creating the tables, but I think on HP they are 1024 so that wouldn't help? My program uses the following queries to find the info and binds the appropriate data from the input file. char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY >= ? AND PHONETIC_KEY < ? ;"; char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;"; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance on HP
Now that I have SQLite compiled on HP, I am starting to test performance. So far it's pretty disappointing though. I am comparing performance of SQLite versus an in-house directory access system. I have the same table structure and data for each of them. The code reads some data from an input file and searches the database for that row, then finds all rows from other tables associated with the first row. On Windows, with a cold cache SQLite is a bit slower than the inhouse system, but after an initial select it runs twice as fast. On HP our inhouse system can run through 1000 rows in input in 4 seconds on average. SQLite is taking about 30 seconds with the same input to find the same results. CREATE TABLE LEVEL1 ( ADDRESS_TYPE text, COUNTRY_ID text, DATA_SOURCE text, DIR_SEARCH_AREA1 integer, ... PHONETIC_KEY text, ... , RECORDKEY integer); CREATE TABLE LEVEL2 ( ... PRIM_NBR_HIGH text, PRIM_NBR_LOW text, ... RECORDKEY integer, PARENT_KEY integer ); CREATE TABLE LEVEL3 ( ... RECORDKEY integer, ... PARENT_KEY integer ); CREATE INDEX L1_INDEX ON LEVEL1 (COUNTRY_ID, DIR_SEARCH_AREA1, ADDRESS_TYPE, PHONETIC_KEY); CREATE INDEX L2_RANGE_INDEX ON LEVEL2 (PARENT_KEY, PRIM_NBR_HIGH, PRIM_NBR_LOW); CREATE INDEX L3_INDEX ON LEVEL3 (PARENT_KEY); I also reordered on these indexes to create the clustered index. On Windows I had set the page_size PRAGMA to 4096 before creating the tables, but I think on HP they are 1024 so that wouldn't help? My program uses the following queries to find the info and binds the appropriate data from the input file. char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY >= ? AND PHONETIC_KEY < ? ;"; char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;"; char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;"; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Case-insensitive Glob?
Hi Gilles, 'like' is case-insensitive. so: select * from customers where city like "%ville%"; would match "villes" or "VilleS". Rgds, Simon 2008/6/20 Gilles Ganault <[EMAIL PROTECTED]>: > Hello > > Is there a way for glob to be case-insensitive? I'd like to retrieve > all rows where the city name can be a mix of upper- and lower-case > letters, eg. > > select * from customers where city glob "*ville*"; > > would match "ville" or "Ville". > > Thank you. > > ___ > 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] Getting a table size without counting
В сообщении от Thursday 19 June 2008 17:40:14 Dennis Cote написал(а): > Stephen Woodbridge wrote: > > Well if the index requires fewer page reads then it should be > > proportionally faster. For example if you can only get 5 rows on a page > > but 25 index entries, you have 1/5 the number of pages to read. > > Yes, that is true, but this effect is offset by the fact that these > index pages compete with the table pages for the finite space in the > page cache. The speed of counting is usually only an issue with large > databases where the table doesn't fit in the cache. When loading index > pages to speed the count() you are also ejecting table pages that might > be used for the next data query, and hence slowing that data query down > because it now has to reload the data pages. > > Adding such an index also slows down all insert, update, and delete > operations because they must modify the index as well as the table. I'm testing database size 100 Gb+ on server with 2GB RAM. I need use big databases. For small databases this optimization is not necessary. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Case-insensitive Glob?
Hello Is there a way for glob to be case-insensitive? I'd like to retrieve all rows where the city name can be a mix of upper- and lower-case letters, eg. select * from customers where city glob "*ville*"; would match "ville" or "Ville". Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Client/Srever SQLite
> I use mutexes set up as read or write locks around Sqlite to synchronize > access. In Sqlite 3.5.9 function "db timeout" work fine, I was test it. So internal mutex is enough now, I think. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
