Re: [sqlite] Using SQLite in embedded environment
On Sat, Jun 28, 2008 at 11:30 AM, dcharno <[EMAIL PROTECTED]> wrote: >> As an option, we also think about Berkeley DB, do you experts has >> experience using Berkeley DB on ARM/Linux with ulibc or glibc? > > Berkeley DB may also be an option. It really depends upon what you are > trying to accomplish, what your data set looks like, etc. > > Ironically yes; I am translating Berkeley DB databases into SQLite for > analysis. :-) sounds like a good method ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а): > I have a large table and a two column index: How much rows are you have? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а): > When I do the following query: > > SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) > AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid > DESC LIMIT 1; > > it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", > it's very fast. As described in http://www.sqlite.org/lang_createindex.html "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] index-name ON table-name ( column-name [, column-name]* ) column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" You can try create additional index as CREATE INDEX ev4_idx ON events (type,eid desc); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL questions
В сообщении от Saturday 28 June 2008 01:20:49 John Stanton написал(а): > I have seen such indices. The key is evaluated at the time the record > is inserted and an index built. The poster could use a trigger with an > update to place the evaluated expression into a colum. Excellent! It's solution of problem. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite in embedded environment
Steven Woody wrote: > On Sat, Jun 28, 2008 at 11:30 AM, dcharno <[EMAIL PROTECTED]> wrote: >>> As an option, we also think about Berkeley DB, do you experts has >>> experience using Berkeley DB on ARM/Linux with ulibc or glibc? >> Berkeley DB may also be an option. It really depends upon what you are >> trying to accomplish, what your data set looks like, etc. >> >> Ironically yes; I am translating Berkeley DB databases into SQLite for >> analysis. > > :-) sounds like a good method Except for legacy issues, I can't think of any reason you'd ever need both. If your options are SQLite and Berkeley DB then a couple of points to consider: With SQLite, you obviously have the full power of SQL to model your system's data and write arbitrarily complex queries to filter and analyze your data. And SQL is standard so it is well documented and easy for other team members to access. Berkeley DB is a persistent hash table. Its good if your data is primarily key/value based and you only need to do key lookup. But, its really just a storage layer. It doesn't provide any type of query capability for filtering or searching through your data -- all of that has to be written as a layer above Berkeley DB by you. Berkeley DB is a bit bigger than SQL Compare the licenses. Be sure to read the license of Berkeley DB to make sure it is compatible with your application. http://en.wikipedia.org/wiki/Berkeley_DB#Licensing SQLite is in the public domain, so you are free to do with it what you please. http://www.sqlite.org/copyright.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite takes too much memory
On Sat, Jun 28, 2008 at 8:43 AM, Mihai Limbasan <[EMAIL PROTECTED]> wrote: > The posted code does not create 100 prepared statements. It creates just > one, fetches the data, finalizes the statement, then loops. Very true, my mistake. /Jonas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
(sorry about my poor english) I think that the problem doesn't correct by creating a DESC index. The problema was at de 'where clausula': (events.eid<=3261976) For some reason SQLITE doesn't optimize the query to use the index to locate the last index item that have type=22 and eid<=3261976. Of course if you have only a few items that have tid=9, SQL must have to read every item starting with de last until he find the item that haves tid=9. Do you try to the prevoius sugest: add a index that have type and tid? You must know that, SQLite must read the items to find verbose<=1 good luck! Emilio Alexey Pechnikov escribio': > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а): >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. > > As described in http://www.sqlite.org/lang_createindex.html > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] > index-name > ON table-name ( column-name [, column-name]* ) > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > You can try create additional index as > CREATE INDEX ev4_idx ON events (type,eid desc); > > ___ > 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] Index and ORDER BY
16 million On Jun 28, 2008, at 4:25 AM, Alexey Pechnikov wrote: > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson > написал(а): >> I have a large table and a two column index: > > How much rows are you have? > ___ > 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] Index and ORDER BY
Sounds promising. I'll give it a try. Thanks! Jeff On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson > написал(а): >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND >> ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. > > As described in http://www.sqlite.org/lang_createindex.html > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- > name .] > index-name > ON table-name ( column-name [, column-name]* ) > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > You can try create additional index as > CREATE INDEX ev4_idx ON events (type,eid desc); > > ___ > 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] Index and ORDER BY
I tried taking the tid and verbose tests out of the WHERE clause, and it made very little difference in the performance. I was thinking that if I can at least speed it up with just eid and type, I could try to extend it to the other columns. Thanks, Jeff On Jun 28, 2008, at 6:25 AM, Emilio Platzer wrote: > (sorry about my poor english) > > I think that the problem doesn't correct by creating a DESC index. The > problema was at de 'where clausula': > > (events.eid<=3261976) > > For some reason SQLITE doesn't optimize the query to use the index to > locate the last index item that have type=22 and eid<=3261976. Of > course > if you have only a few items that have tid=9, SQL must have to read > every item starting with de last until he find the item that haves > tid=9. > > Do you try to the prevoius sugest: add a index that have type and tid? > > You must know that, SQLite must read the items to find verbose<=1 > > good luck! > > Emilio > > Alexey Pechnikov escribio': >> В сообщении от Saturday 28 June 2008 02:28:05 Jeff >> Gibson написал(а): >>> When I do the following query: >>> >>> SELECT events.* FROM events WHERE ( events.type=22) AND >>> ( events.tid=9) >>> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY >>> events.eid >>> DESC LIMIT 1; >>> >>> it's very slow. If I switch the ORDER BY to "ASC" instead of >>> "DESC", >>> it's very fast. >> >> As described in http://www.sqlite.org/lang_createindex.html >> "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- >> name .] >> index-name >> ON table-name ( column-name [, column-name]* ) >> column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" >> >> You can try create additional index as >> CREATE INDEX ev4_idx ON events (type,eid desc); >> >> ___ >> 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] Index and ORDER BY
Very strange. I modified my query to not use verbose or tid, so only the indexed columns are relevant. With: CREATE INDEX ev4_idx ON events(type); The query runs in about 9 seconds. With: CREATE INDEX ev4_idx ON events(type,eid desc) It runs in 11 seconds. I'm not using the most accurate timing in the world (not using database functions for the timing, since I don't know if that would distort the results) - literally, a wall clock. But it is noticeably a little slower. Any ideas? Thanks, Jeff On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson > написал(а): >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND >> ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. > > As described in http://www.sqlite.org/lang_createindex.html > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- > name .] > index-name > ON table-name ( column-name [, column-name]* ) > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > You can try create additional index as > CREATE INDEX ev4_idx ON events (type,eid desc); > > ___ > 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] Index and ORDER BY
Can you send link to you database file? You results are strange. В сообщении от Saturday 28 June 2008 21:44:15 [EMAIL PROTECTED] написал(а): > Very strange. I modified my query to not use verbose or tid, so only > the indexed columns are relevant. > > With: > > CREATE INDEX ev4_idx ON events(type); > > The query runs in about 9 seconds. > > With: > > CREATE INDEX ev4_idx ON events(type,eid desc) > > It runs in 11 seconds. > > I'm not using the most accurate timing in the world (not using > database functions for the timing, since I don't know if that would > distort the results) - literally, a wall clock. But it is noticeably > a little slower. Any ideas? > Thanks, > Jeff > > On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: > > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson > > > > написал(а): > >> When I do the following query: > >> > >> SELECT events.* FROM events WHERE ( events.type=22) AND > >> ( events.tid=9) > >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid > >> DESC LIMIT 1; > >> > >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", > >> it's very fast. > > > > As described in http://www.sqlite.org/lang_createindex.html > > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- > > name .] > > index-name > > ON table-name ( column-name [, column-name]* ) > > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > > > You can try create additional index as > > CREATE INDEX ev4_idx ON events (type,eid desc); > > > > ___ > > 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] Index and ORDER BY
[EMAIL PROTECTED] wrote: > Very strange. I modified my query to not use verbose or tid, so only > the indexed columns are relevant. > > With: > > CREATE INDEX ev4_idx ON events(type); > > The query runs in about 9 seconds. > > With: > > CREATE INDEX ev4_idx ON events(type,eid desc) > > It runs in 11 seconds. > > I'm not using the most accurate timing in the world (not using > database functions for the timing, since I don't know if that would > distort the results) - literally, a wall clock. But it is noticeably > a little slower. Any ideas? Well a few thoughts: 1) and index on (type) vs (type,eid,desc) will be smaller and get more entries per page so you wil like have to bring less pages into memory. 2) I'm not sure of the statistics in your various indexes, but you would have to trade off whether or not the added resolution of (type,eid,desc) adds more value, then finding N records at (type) and doing a seqential scan of those vs find less and N records at (type,eid,desc) and returning them. I think you need to look at each with more specific timing stats and be aware of what the cache status of pages are when you run the tests because this might affect the timing results. -Steve > On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: > >> В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson >> написал(а): >>> When I do the following query: >>> >>> SELECT events.* FROM events WHERE ( events.type=22) AND >>> ( events.tid=9) >>> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >>> DESC LIMIT 1; >>> >>> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >>> it's very fast. >> As described in http://www.sqlite.org/lang_createindex.html >> "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- >> name .] >> index-name >> ON table-name ( column-name [, column-name]* ) >> column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" >> >> You can try create additional index as >> CREATE INDEX ev4_idx ON events (type,eid desc); >> >> ___ >> 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] Index and ORDER BY
I see. Do you have any suggestions on where I can find out about how to get more specific timing information? I'm pretty much an sqlite novice. Thanks, Jeff On Jun 28, 2008, at 12:02 PM, Stephen Woodbridge wrote: > [EMAIL PROTECTED] wrote: >> Very strange. I modified my query to not use verbose or tid, so only >> the indexed columns are relevant. >> >> With: >> >> CREATE INDEX ev4_idx ON events(type); >> >> The query runs in about 9 seconds. >> >> With: >> >> CREATE INDEX ev4_idx ON events(type,eid desc) >> >> It runs in 11 seconds. >> >> I'm not using the most accurate timing in the world (not using >> database functions for the timing, since I don't know if that would >> distort the results) - literally, a wall clock. But it is noticeably >> a little slower. Any ideas? > > Well a few thoughts: > > 1) and index on (type) vs (type,eid,desc) will be smaller and get more > entries per page so you wil like have to bring less pages into memory. > > 2) I'm not sure of the statistics in your various indexes, but you > would > have to trade off whether or not the added resolution of > (type,eid,desc) > adds more value, then finding N records at (type) and doing a > seqential > scan of those vs find less and N records at (type,eid,desc) and > returning them. > > I think you need to look at each with more specific timing stats and > be > aware of what the cache status of pages are when you run the tests > because this might affect the timing results. > > -Steve > >> On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: >> >>> В сообщении от Saturday 28 June 2008 02:28:05 Jeff >>> Gibson >>> написал(а): When I do the following query: SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid DESC LIMIT 1; it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", it's very fast. >>> As described in http://www.sqlite.org/lang_createindex.html >>> "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- >>> name .] >>> index-name >>> ON table-name ( column-name [, column-name]* ) >>> column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" >>> >>> You can try create additional index as >>> CREATE INDEX ev4_idx ON events (type,eid desc); >>> >>> ___ >>> 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] Index and ORDER BY
Unfortunately, I can't, being that the data is proprietary. It's also rather large (~ 2GB). Jeff On Jun 28, 2008, at 10:59 AM, Alexey Pechnikov wrote: > Can you send link to you database file? You results are strange. > > В сообщении от Saturday 28 June 2008 21:44:15 > [EMAIL PROTECTED] написал(а): >> Very strange. I modified my query to not use verbose or tid, so only >> the indexed columns are relevant. >> >> With: >> >> CREATE INDEX ev4_idx ON events(type); >> >> The query runs in about 9 seconds. >> >> With: >> >> CREATE INDEX ev4_idx ON events(type,eid desc) >> >> It runs in 11 seconds. >> >> I'm not using the most accurate timing in the world (not using >> database functions for the timing, since I don't know if that would >> distort the results) - literally, a wall clock. But it is noticeably >> a little slower. Any ideas? >> Thanks, >> Jeff >> >> On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: >>> В сообщении от Saturday 28 June 2008 02:28:05 Jeff >>> Gibson >>> >>> написал(а): When I do the following query: SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid DESC LIMIT 1; it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", it's very fast. >>> >>> As described in http://www.sqlite.org/lang_createindex.html >>> "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- >>> name .] >>> index-name >>> ON table-name ( column-name [, column-name]* ) >>> column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" >>> >>> You can try create additional index as >>> CREATE INDEX ev4_idx ON events (type,eid desc); >>> >>> ___ >>> 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] Index and ORDER BY
Show "explain query plan ..." for you query. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
On Jun 28, 2008, at 8:10 PM, [EMAIL PROTECTED] wrote: > I see. Do you have any suggestions on where I can find out about how > to get more specific timing information? I'm pretty much an sqlite > novice. In the sqlite3 command line: .timer ON|OFF Turn the CPU timer measurement on or off -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Petite Abeille wrote: > On Jun 28, 2008, at 8:10 PM, [EMAIL PROTECTED] wrote: > >> I see. Do you have any suggestions on where I can find out about how >> to get more specific timing information? I'm pretty much an sqlite >> novice. > > In the sqlite3 command line: > > .timer ON|OFF Turn the CPU timer measurement on or off or in C you can use time or gettimeofday functions. see the man pages for them. -Steve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Show results of this queries: select max(eid) from events; select count(eid) from events; select count(eid) from events where type=22; select count(eid) from events where eid<=3261976; select count(eid) from events where eid<=3261976 and type=22; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
sqlite> SELECT max(eid) from events; 16643833 sqlite> SELECT count(eid) FROM events; 16643833 sqlite> SELECT count(eid) FROM events WHERE type=22; 8206183 sqlite> SELECT count(eid) FROM events WHERE eid<=3261976; 3261976 sqlite> SELECT count(eid) FROM events WHERE eid<=3261976 AND type=22; 2062728 And for performance: sqlite> CREATE INDEX ev4_idx ON events(type); No ordering: sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 LIMIT 1; 13|63922|6|0|22|9|4 CPU Time: user 0.00 sys 0.044993 Ascending order: sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid ASC LIMIT 1; 13|63922|6|0|22|9|4 CPU Time: user 0.00 sys 0.00 Descending order: sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 3261891|4910298|1206924|1|22|9|4 CPU Time: user 4.204361 sys 0.885865 (wall clock time is roughly double user+sys - I guess time waiting for disk isn't being counted in system time) Sanity check: sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev4_idx ORDER BY With a different index: sqlite> CREATE INDEX ev5_idx ON events(type,eid desc); sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev5_idx ORDER BY sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 3261891|4910298|1206924|1|22|9|4 CPU Time: user 4.282349 sys 0.901862 (again, wall-clock time is roughly double this amount) And ascending order is very fast: sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid ASC LIMIT 1; 13|63922|6|0|22|9|4 CPU Time: user 0.00 sys 0.052992 It seems that sqlite wants to do its index scan in ascending order, so returning the first one is very quick, but returning the last one (first in descending order) is slow. Is there any way to give the engine an idea that it should do its index scan in descending order so that the ORDER BY is cheap? Thanks, Jeff On Jun 28, 2008, at 11:50 AM, Alexey Pechnikov wrote: > Show results of this queries: > > select max(eid) from events; > select count(eid) from events; > select count(eid) from events where type=22; > select count(eid) from events where eid<=3261976; > select count(eid) from events where eid<=3261976 and type=22; > ___ > 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] rtree module crashes
When I compile SQLite using SQLITE_ENABLE_RTREE set to 1 SQLite will crash when opening a database (Mac OSX). The reason seems to be that in rtree.c sqlite3ext.h is included instead of sqlite3.h. This can be prevented by setting SQLITE_CORE to 1 but then the types i64, u8 etc. are undefined. So, my solution: SQLITE_ENABLE_RTREE set to 1 SQLITE_CORE set to 1 and define i64, u8 etc. in all cases. Did I do anything wrong? Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unsigned 64 bit integers
Hi. I have a newbie question. When trying to store 64-bit unsiged integers in sqlite, some of them come back as floats. There is some internal reference on the web page to 64-bit unsiged integers; http://www.sqlite.org/c3ref/int64.html. On the other hand, the faq's entry on AUTOINCREMENT fields suggests that they may not be supported above 9223372036854775807. Does sqlite support 64-bit unsigned integers? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lemon
Hi Arjen,
Thanks for the mail...I found it very helpful and i started to execute
it..I succeeded in doing it...
However i started writing a sample program for converting "Infix to
Prefix"...I am struck at this point ...so can u help me in
writing this program...
Thanks in advance
Regards,
Arjun
On Sat, Jun 28, 2008 at 12:54 AM, Arjen Markus <[EMAIL PROTECTED]>
wrote:
> > Hi,
> > Can u send me any documentation on LEMON that u have worked out.
> > I have some queries also.
> > 1.Does LEMON work on Windows environment?
> > 2.I tried but it is not.I heard that it works on Linux environment.I am
> > trying to know why not on windows...can u give me some info about it
> >
> > I am reading the material u have suggested me but it did not have any
> > information regarding to my queries.
> >
> > Can u mail me if u have made any documentation on LEMON.
> >
>
> Well, basically lemon works as follows:
> 1. It reads the definition of the parser from the input file
> you give it as the first argument. From that it produces
> a set of tables that implement the parser. This information
> gets filled in in a template.
> 2. The result is a C file that you can use to parse (interpret)
> your input.
> 3. What it does not do is split up the input into tokens. You
> have to do that yourself.
>
> I attach a small - not very efficient - example that shows a
> few features of lemon.
>
> As for your questions:
> There is no reason it would not work on Windows: it is a
> perfectly straghtforward C program, you get the source code
> and compile it. One thing though: the template file must be
> in the directory where you run the program, where the
> executable lives or in the PATH.
>
> (I am using it myself on Windows :). I am adapting it so that
> it will produce Fortran code instead of C code. But that is
> off topic.)
>
> Here is my sample parser definition:
>
> // expr.y
> // Simple parser:
> // expr = vaue + value + value + ...
>
> %extra_argument {State *state}
> %token_type {Token*}
> %type term {int}
>
> expr ::= firstterm plusterms . {
>printf( "Result: %d\n", state->sum );
> }
>
> firstterm ::= term(T) . {
>state->sum = T;
>printf("First term: %d\n", T );
> }
>
> term(V) ::= NUMBER(N) . {
>sscanf( N->token, "%d", &V );
>printf("Term: %d -- %s\n", V, N->token );
> }
>
> plusterms ::= .
> plusterms ::= plusterms plusterm .
>
> plusterm ::= PLUS term(T) . {
>state->sum = state->sum + T;
>printf( "Result so far: %d\n", state->sum );
> }
>
> --
> And here is the main program that includes the
> resulting C code:
>
> #include
> #include
> #include
>
> typedef struct {
>char *token;
> } Token;
> typedef struct {
>int sum;
> } State;
>
> #include "expr.h"
> #include "expr.c"
>
> int main( int argc, char *argv[] ) {
>Token token[10];
>State state;
>
>void *pParser = (void *) ParseAlloc( malloc );
>
>ParseTrace( stdout, ">>" );
>
>token[0].token = "1"; Parse( pParser, NUMBER, &token[0], &state );
>token[1].token = "+"; Parse( pParser, PLUS, &token[1], &state );
>token[2].token = "2"; Parse( pParser, NUMBER, &token[2], &state );
>token[3].token = "+"; Parse( pParser, PLUS, &token[3], &state );
>token[4].token = "3"; Parse( pParser, NUMBER, &token[4], &state );
>
>Parse( pParser, 0, &token[0], &state );
>
>ParseFree( pParser, free );
>
>return 0;
> }
>
> -
> One thing to note:
> As the tokens are not always treated the moment they are passed
> to the parser - this depends on the grammar and the moment in the
> parsing process, you need to make sure that the tokens remain
> available. I have done that in this silly program by using an
> array of them. But a smarter program would use lemon's
> abilities to destroy the tokens when ready.
>
> Regards,
>
> Arjen
>
> ___
> 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] Unsigned 64 bit integers
No. The range is for 64 bit signed. SQLite has manifest typing it is like Ruby Duck Typing or Dynamic Typing. The value defines the type not the column type or lack there of it. If you value is a small integer it will only use 1 byte. On Sat, Jun 28, 2008 at 6:02 PM, freeav8r <[EMAIL PROTECTED]> wrote: > Hi. I have a newbie question. > > When trying to store 64-bit unsiged integers in sqlite, some of them come > back as floats. There is some internal reference on the web page to 64-bit > unsiged integers; http://www.sqlite.org/c3ref/int64.html. On the other > hand, the faq's entry on AUTOINCREMENT fields suggests that they may not be > supported above 9223372036854775807. > > Does sqlite support 64-bit unsigned integers? > > > > > > ___ > 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] Using SQLite in embedded environment
On Sat, Jun 28, 2008 at 8:37 PM, dcharno <[EMAIL PROTECTED]> wrote: > Steven Woody wrote: >> On Sat, Jun 28, 2008 at 11:30 AM, dcharno <[EMAIL PROTECTED]> wrote: As an option, we also think about Berkeley DB, do you experts has experience using Berkeley DB on ARM/Linux with ulibc or glibc? >>> Berkeley DB may also be an option. It really depends upon what you are >>> trying to accomplish, what your data set looks like, etc. >>> >>> Ironically yes; I am translating Berkeley DB databases into SQLite for >>> analysis. >> >> :-) sounds like a good method > > Except for legacy issues, I can't think of any reason you'd ever need both. > > If your options are SQLite and Berkeley DB then a couple of points to > consider: > > With SQLite, you obviously have the full power of SQL to model your > system's data and write arbitrarily complex queries to filter and > analyze your data. And SQL is standard so it is well documented and > easy for other team members to access. > > Berkeley DB is a persistent hash table. Its good if your data is > primarily key/value based and you only need to do key lookup. But, its > really just a storage layer. It doesn't provide any type of query > capability for filtering or searching through your data -- all of that > has to be written as a layer above Berkeley DB by you. > > Berkeley DB is a bit bigger than SQL Berkeley DB bigger? Why? To my understand, Bkerkeley DB is database core and and many SQL are based on it. > > Compare the licenses. Be sure to read the license of Berkeley DB to > make sure it is compatible with your application. > >http://en.wikipedia.org/wiki/Berkeley_DB#Licensing > > SQLite is in the public domain, so you are free to do with it what you > please. > >http://www.sqlite.org/copyright.html > > > > > > ___ > 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] select with union fails on an attached database for 500 records
Hi All, We are facing a problem with the select command with union on an attached database and getting an error "SQL logic error or missing database" 1.We have an active database connection (for eg a.db which contains a table with following columns)CREATE TABLE Contact_Primary_Info([uid] BIGINT PRIMARY KEY NOT NULL,[phonename] VARCHAR(41) ,[phonenumber] VARCHAR(42));2.We have created an in memory database with the attach database command (attach ':memory:' as 'SIM' ) with a similar schema.CREATE TABLE SIM.Contact_SIM ([itemId] BIGINT PRIMARY KEY NOT NULL,[name] VARCHAR(41) ,[number] VARCHAR(42)); where SIM is the attached database. 3.Now the usecase we want to get all the records in the Contact_Primary_Info and Contact_SIM table..The following is the query i wrote: select phonename,uid from contact_primary_info union select name,itemId from Contact_SIM order by 1 ASC; We created 250 records in the SIM.Contact_SIM table and tried the above query for 10 to 100 records in the table Contact_Primary_Info.It worked perfectly.When we create 500 records in the Contact_Primary_Info table,we are getting an error on the sqlite3_step command which states "SQL logic error or missing database" Can anyone please help us on whats going wrong with this?The system we are trying is an embedded device which runs on a ARM11 processor. Best Regards,N.RajeshCourage is the knowledge of how to fear what ought to be feared and how not to fear what ought not to be feared. WL Messenger : Stay connected to friends and family with the New Windows Live Messenger. Get it now. Try it now! _ Watch hottest Bollywood videos, clips, movie tailors, star interviews, songs and more on MSN videos. http://video.msn.com/?mkt=en-in ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree module crashes
On Jun 29, 2008, at 3:02 AM, Hartwig Wiesmann wrote: > When I compile SQLite using SQLITE_ENABLE_RTREE set to 1 SQLite will > crash when opening a database (Mac OSX). The reason seems to be that > in rtree.c sqlite3ext.h is included instead of sqlite3.h. This can be > prevented by setting SQLITE_CORE to 1 but then the types i64, u8 etc. > are undefined. > > So, my solution: > > SQLITE_ENABLE_RTREE set to 1 > SQLITE_CORE set to 1 > and define i64, u8 etc. in all cases. > > Did I do anything wrong? That will probably work. The problem with the i64, u8 types not being defined was fixed here: http://www.sqlite.org/cvstrac/chngview?cn=5282 After this fix, you should be Ok with just SQLITE_ENABLE_RTREE and SQLITE_CORE defined. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
