Re: [sqlite] Question about searches
> You write your own comparison function that would consider these two > strings equal. See sqlite3_create_function, sqlite3_create_collation. > Well, this problem pertains not only to Zürich, but to 24000 other entries, so I guess that this is no option for me. And again, I am using the sqlite3 command line exe and can't compile a custom version. > Why would you ever want two % in a row? A % matches zero or more of > arbitrary characters. You might be thinking of an underscore _. > OK. Thanks for the hint, I was under the wrong assumption that % matches one character exactly, whereas this seems to be "_". >> So far , so good, but my client also expects ANY simplification of a >> character to be recognized: >> Cote d'azur for instance should return "Côte d'azur" >> or the Sao Paulo issue - how can a search for "Sao Paulo" return "Sào >> Paulo" in the result set? >> > How are these examples different from previous ones? > I am sorry, but I find this to be quite obvious? Here, the problematic char is to be found in the *result set*, not in the query itself. How do you educate SQlite to return me "Sào Paulo" if only "Sao Paulo" is being entered? How do I know which character to substitute with a placeholder? Is it S%o Paulo to look for? Or Sa% Paulo? Or Sao P%ulo? I can't know this beforehand. These are just examples, i need a generic solution if possivble. All i can see so far is to build a table of all special characters ever used in the 24000 names of cities which make problems and remap them accordingly. -- Christophe Leske www.multimedial.de - i...@multimedial.de http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany +49(0)180102 - 06 60 02 96 // +49(0)177 249 70 31 This e-mail may contain confidential information. If you are not the intended recipient, it is appreciated that you notify the sender and delete your copy. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about searches
Hi there,
i have written an application which runs under german and englisch
versions of Windows.
It includes a city databases which is ought to be searchable, yet there
are a couple of issues which are of more logical nature...
My shell application surrounding the sqlite database only supports the
ANSI charset (no Unicode), yet the underlying SQlite database has been
created with UTF-8 support.
So far so good.
I got complaints from my client here that excentric city names like "Sào
Paulo" cannot be found.
Further digging revealed two potential problems:
- how can SQlite be instructed to return search results which include a
special character in it?
E.g. you search literally for "Zurich" on an englisch system and expect
"Zürich" to be in the result set.
The next problem is that educated users might know that german Umlaute
can be written out. The rules are simple:
ä becomes "ae"
ö becomes "oe"
ü becomes "ue"
So how would I go about filtering an educated user which looks for
"Zuerich" and expects "Zürich" in the result set?
Best find on my behalf so far is to build a filter which replaces any
occurence of "ae", "oe", "ue" with two placeholders ("%") which would
effectively lead to a search of the type
select * from cities where name like "Z%%rich"
So far , so good, but my client also expects ANY simplification of a
character to be recognized:
Cote d'azur for instance should return "Côte d'azur"
or the Sao Paulo issue - how can a search for "Sao Paulo" return "Sào
Paulo" in the result set?
Please note that I am using the normal command line sqlite3.exe application. I
already started looking into soundex() yet unfortunately, it does not seem to
be compiled into the normal command like executable. I also doubt that it would
help?
Any help much appreciated,
Christophe Leske
www.multimedial.de - i...@multimedial.de
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
+49(0)180102 - 06 60 02 96 // +49(0)177 249 70 31
This email may contain confidential information. If you are not the intended
recipient, it would be appreciated that you delete it and notify the sender.
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample file search algorithm
Rahul Jagtap schrieb: > Hi all, > I am new to sql database and want to search file in database. Can you please > tell me from where I should get and sample file search algorithm.. > Please reconsider your job or at least your task at hand. You cannot search files in a database, unless you put them in there beforehand as blobs. But then, maybe I misunderstood? -- Christophe Leske www.multimedial.de - i...@multimedial.de http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany +49(0)211- 17 80 48 94 // +49(0)177 249 70 31 ***Note our new phone number. Thank you. Diese E-Mail könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Danke sehr. This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.10 - Warning
> After you complete your ".dump" do: > > .output stdout > Ok, that was stupid, my apologies. Yet somehow this used to work i think... Still, when rereading the dumped table, it does not reread the values correctly for the rtree table. I am sorry, I must go now, I can provide further information on the 25th. Thank you for your patience, -- Christophe Leske www.multimedial.de - i...@multimedial.de http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.10 - Warning
> Try instead: > > .dump cl1% Apparently, this doesn´t help either. Once I tried to export the table, the application refuses to show the entries in the table. The database i am opening here is correct, it holds data in cl1 prior to opening it for this step here: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Arbeit\Arbeit\__Projekte\2007\MMCD\SRC\globe\DB>sqlite3 countries2.db SQLite version 3.6.10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .o cl1.sql sqlite> .dump cl1% sqlite> select * from cl1; sqlite> Also, a .show statement returns nothing either. It seems as if the database does simply not exist anymore. Furthermore, i got a corrupted version of the database here as the result of my operations as well - this used to be the same database than the one above, yet after exporting and reimporting the data for the cl1 table, it is now corrupted. C:\Arbeit\Arbeit\__Projekte\2007\MMCD\SRC\globe\DB>sqlite3 countries-corrupted.d b SQLite version 3.6.10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .s sqlite> .s cl1; sqlite> We also experience crashes with the new version, but this may be due to the component we are using (i am not sure it is supporting the new version of sqlite.dll) Do you want me to mail you the corrupted database for further inspection? I also understand that this is not the right place for bugreports eventually. Christophe Leske www.multimedial.de - i...@multimedial.de http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.10
Jim Dodgen schrieb: > I'm a strong believer in the "continuous improvement" philosophy. Keep up > the good work. > Same here. Better fix stuff as you know of it. Please keep up the great work with SQlite, i never used such a good and sturdy tool that just did what it was supposed to do. I think this is what I like best about it: it does the stuff it is supposed to do, no more, but also not less. -- Christophe Leske www.multimedial.de - i...@multimedial.de http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.10 - Warning
> Try instead: > > .dump cl1% Apparently, this doesn´t help either. Once I tried to export the table, the application refuses to show the entries in the table. The database i am opening here is correct, it holds data in cl1 prior to opening it for this step here: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Arbeit\Arbeit\__Projekte\2007\MMCD\SRC\globe\DB>sqlite3 countries2.db SQLite version 3.6.10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .o cl1.sql sqlite> .dump cl1% sqlite> select * from cl1; sqlite> Also, a .show statement returns nothing either. It seems as if the database does simply not exist anymore. Furthermore, i got a corrupted version of the database here as the result of my operations as well - this used to be the same database than the one above, yet after exporting and reimporting the data for the cl1 table, it is now corrupted. C:\Arbeit\Arbeit\__Projekte\2007\MMCD\SRC\globe\DB>sqlite3 countries-corrupted.d b SQLite version 3.6.10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .s sqlite> .s cl1; sqlite> We also experience crashes with the new version, but this may be due to the component we are using (i am not sure it is supporting the new version of sqlite.dll) Do you want me to mail you the corrupted database for further inspection? I also understand that this is not the right place for bugreports eventually. Christophe Leske www.multimedial.de - i...@multimedial.de http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.10 - Warning
Hi, if my findings are not mistaken, then the commandline executable of the new version has some serious problems: I have a database (which seems correct so far) from which I would like to dump a table called "cl1" which is a virtual table using an rtree: sqlite> .o cl1.sql sqlite> .d cl1 sqlite> drop table cl1; SQL error: no such table: main.cl1_node After exporting the table, it seems that there is some corruption going on? Also, when trying to import the data, the data is not read back correctly (the number ordering gets out of whack). I am in the midst of signing off my project, but how can I contribute ? -- Christophe Leske www.multimedial.de - i...@multimedial.de http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is a Group By, having clause?
> Think of HAVING as being analogous to WHERE. While WHERE applies to > the TABLE, HAVING applies to the results of GROUP. Here is a contrive yes, thank you. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is a Group By, having clause?
[EMAIL PROTECTED] wrote: > http://www.w3schools.com/sql/default.asp > Thanks! That is great! -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What is a Group By, having clause?
Hi, can someone point me to some docs where the difference between a normal "where"-clause and the "Group by", and "having" statements are being explained? I don´t quite understand what these are actually good for. Thanks, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] contstraint failed problem
Hello, i have a rtree table which I dynamically opulate with results. Sometimes however, I get a "constraint failed" error from SQLite. After some poking, I found that it occurs whenever my statement tries to insert a record which has an ID that is already in the rtree-table, which is ok, as the ID is to be unique. However, the insert statement is written as followed: insert or ignore into idlookup select * from (select * from cl2) where ( lomi>2.64 and loma<3.29 and lami>42.48 and lama<42.82); SQL error: constraint failed Like i said, the error occurs because one of the records to be added are already in the idlookup table. It would be my understanding however that the "or ignore" statement would silently let those inserts fail if they are already in? What´s wrong here? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] optimizing a multiple select statement
> insert or ignore into idlookup > select * from ( > select * from cl1 > UNION ALL select * from cl2 > UNION ALL select * from cl3 > UNION ALL select * from cl4 > UNION ALL select * from cl5 > ) > where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23); > Thanks, but sometimes it says "constraint failed"? What does this mean? Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] optimizing a multiple select statement
Hello, i am doing this request and was wondering if I can coerce it, as all tables of the subset (the cl* tables) all have the same fields. As you can see, i need to sort them by lomi,loma,lami,lama and ID. insert into idlookup select * from cl1 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) UNION select * from cl2 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) UNION select * from cl3 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) UNION select * from cl4 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) UNION select * from cl5 where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup)" Sorry if this is trivial, but is there no way to do something like: insert into idlookup ( ( select * from cl1 UNION select * from cl2 UNION select * from cl3 UNION select * from cl4 UNION select * from cl5 ) where (lomi>13.96 and loma<13.96 and lami>53.23 and lama<53.23) and id not in (select id from idlookup) ) ? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes
>> Any chance to spare the ID field and get an index on the rowid for a >> given table? > I do not understand the question. Please rephrase. Use more words. > Most of the ID fields are primary integer keys which also coincident with the value of the rowid for a given table. Yes, you cannot count on it, but as long as you do not change the content of a given table, the value of an ID field declared as a integer primary key is usually the same as the rowid of the record: rowid=id I was wondering if there is a cheap way (memorywise) to index the rowid and thus omit the ID field. Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes
> The INTEGER PRIMARY KEY is always included in every index as an > implied extra column on the end. If you explicitly add the INTEGER > PRIMARY KEY as a column in the index, then you have it in the index > twice, which serves no purpose but does confuse the optimizer. Don't > do that. > Any chance to spare the ID field and get an index on the rowid for a given table? Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Enforcing Uniqueness for tables created using a select statement?
Hi, i am creating my table as such: create temp table idlookup as select id from ... I would like ID to be unique in my idlookup table. How would I do this using this construct? Or is it impossible? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another set of questions
> ahhh... now we are getting somewhere. > > Here is a suggestion... start a new thread, with a clear and relevant > subject line, describe your app succinctly, note that it works on such > and such machine, and that it fails to work on such and such machine, > describe exactly what "does not run" mean -- does it not start, does > it start and then hang, does it start and then quit, does it give an > error message, etc., describe the specs of the machine it does not > work on... > > That would be very helpful to all. > Hi, I understand what you are saying, but again, there is no such thing (yet). I am finishing off my application and would like to prepare it to the best. I already got feedback that it is stalling sometimes when running from DVD, yet the database is also fighting with another vital part of the application, which is the tile loader (for the graphical patches on the globe). I can also give you my machine specs, yet my client is unable to produce a lowest common denominator spec machine on which the app still must run. Furthermore, I am not your average user - i use sqlite in a multimedia authoring program called Adobe Director, and I got a code extension for the app (a so-called Xtra), as well as an external (threaded) shell running sqlite3.exe for repeated queries on a city database in order to show the name of the cities currently visible. Other than that, i am on Windows XP, yet the app is ought to run on Win2K, WinXP and Vista with a 1.6 GHz CPU, 1 GB of RAM and a 3d graphics card and DVD drive. My dev machine is different of course (2Ghz, 2GB of RAM, Windows XP). Does it run? Yes. Does it run fast? For me here, yes, it does. Does it run fast enough? Does an application ever run fast enough? I am tweaking it to the max, also considering Dr Hipps compression extension in order to squeeze out the maximum amount of speed available in order to let the application run on a maximum of machines. We are currently facing stalling of the app when loading tiles and querying the database, especially at a mid-size altitude from earth, where class>2 cities are being shown (class of the city = its importance). It is a typical loading bottleneck between pulling graphics in (threaded as well) and querying/loading database content. Since the DVD can only load one thing at a time, the queries for the database get apparently stalled in the thread until the DVD hits the database file again and executes them. I have experiemented extensively with caching, making the cache size high, low and medium - to no avail, the sheer amount of data (the size of the database) is to big. We have 90Mb of data. Situation got better when we yanked class 6 "cities" (small towns) which made up 2/3 of the database. I am currently doing in-memory databases of the most important stuff in order to speed up the queries and get it to work. So all in all, i am already finetuning, yet to a target "system" which is at my client´s site through educated guesses in the code. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another set of questions - subject line - will do ! :-)
> Any chance that you could hint at the subject matter of the questions > in the Subject line ? In this case it would appear to be Performance ... > Helps to get your questions answered ! > ;-) > Yes, will do so. My apologies. Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another set of questions
> Why don't you just do a > > DELETE FROM table; > > and start inserting new results? (end result is the same as dropping > and then recreating the table, but you wouldn't know). > I am worried about creeping memory useage, as this is in-memory. The timing in the app is critical, not on my dev machine, but the final application (a 3d globe done in Director using an external thread with sqlite3.exe to query the db in memory). It should run on lower machines. > As I referenced in an earlier email, when in doubt, try. When try > fails, then ask, but don't worry about saving space and time when > those may not really be the constraining factors. Instead, worry about > getting your application right, that is, doing what you want it to do > without having unintended consequences such as rebooting your computer > or flushing your toilet. Once everything is working, then work slowly > and carefully to make things faster better cheaper. > It IS doing what I want. However, it does not run on lower-spec machines, which is why i am trying to optimize anything available. Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Two questions
>> Can I spare some bytes in my DB by defining the ID field of the >> standard >> table as being a foreign key of the rtree table? In other words, when >> defining a foreign key, is the coloumn referencing the ID field of the >> foreign table and thus NOT replicating them (using a smaller memory >> footprint in the file)? >> Or does the table which has a foreign key still have its own ID >> coloumn? >> > The ID column is replicated in each table. > Is there a construct in sqlite which would allow a coulumn to be shared amongst tables in such a way that the data is there only once, thus creating a smaller file? In the example provided, the iDs are exactly the same, yet they are there twice... Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 lock issue with NFS and read-only queries
> PRAGMA omit_readlock=ON; > Hm, should be documented, no? I could use that as well... Just wondering if this has any speed advantages? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another set of questions
> I don't know. Have you run an experiment to see for yourself? > Yes, but my results are inconclusive. Currently, i am doing this: drop table idlookup;create temp table idlookup as select id from (select statement for temporary result set) Thus the statement is shorter than create temp table if not exists idlookup (id integer);insert into idlookup id=select id from (select statement for temporary result set) My thought was that by using "replace" after the table has been created, i could simply expand it (if there are more results than used to be in the table). This is all fine for that case, but if the new result set has LESS results than the previous one, then I end up with a temporary table holding the new result set and leftovers from the previous one... -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Another set of questions
Hi, some more questions... I am using in-memory temporary tables with results sets created on the fly. On each round, i would like to create a new table with those interims results. Question is: - what is quicker/better? Dropping the temporary table on every time and recreate it from scratch? Or just deleting the entries? problem is, the result set can have a different amount of results, thus one time for instance it can have 200 entries, then the next time 300, then just 50. If I use "replace", i potentially have leftover results if I do not clear the table before rebuilding it. Am I being clear? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Two questions
Hi, first i´d like to thank the people on this list that I have found to be very helpful in the past. This list is truly great and friendly. I got two questions today: I have two tables, a standard one and an rtree table, which are both linked together logically by the ID field of the rtree table. Can I spare some bytes in my DB by defining the ID field of the standard table as being a foreign key of the rtree table? In other words, when defining a foreign key, is the coloumn referencing the ID field of the foreign table and thus NOT replicating them (using a smaller memory footprint in the file)? Or does the table which has a foreign key still have its own ID coloumn? And regarding Indices: my standard table has two indices, one for the coloumn ID (since it is being referenced from the rtree), plus another one on a coloumn which I use for sorting the results coming from that table. Are indices also used for sorting results, or do they do just apply for searching? Thanks in advance, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Condition on several tables
> Perhaps something like this: > > select * from > (select * from c1 > union all > select * from c2 > union all > select * from c3 > ... > ) > where foo1 < 10; > Yes! And to answer Mike´s email as well: these table represent higher and detailled data for deeper research, so all of these tables have the same data c1 to c5 are in fact rtree tables for different classes of cities. Depending on the distance from the viewer onto a 3d globe, i want just to show the capitales, then the capitales and the big cities (c2), then the capitales (c1), the big cities (c2) and the not so big cities (c3) and so forth. Since it is depending on the distance from the ground and the rtree table only works with coordinates, i cannot really include the class-attribute into the rtree, hence several rtrees. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Condition on several tables
> When you add multiple tables into the FROM clause, you make a single > conceptual table out of them by using the JOIN operator. So (with c1,c2,c3 all being rtrees) select * from (select * from c1,c2,c3) where bla>10 is *not the same as select * from c1 where bla>10 union all select * from c2 where bla>10 union all select * from c3 where bla>10 ... > If you want the same query out of a number of different tables that > are identical, and join the results together, then you use the UNION > clause... in other words, you do indeed perform the SELECTs separately > and then UNION them together, but you have to apply the WHERE > constraint only once because all the tables are identical. > Do you mind elaborating on this point? Is that what Igor wrote me? Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Condition on several tables
Hi, pardon me if this is a pretty easy SQL question, yet i am after a statement compound which applies just ONE where clause to a group of tables. Something like select * from c1,c2,c3where foo1<10 with foo1 being in all of the specified tables. So far, all i can see is that I have to build several statements which all have the where clause in there. Isn´t there something more tersed? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting the size of a in-memory table somehow?
Hi, I asked this a couple of days ago and would still be interested to know if there is any chance, method or way to get the current size of an in-memory database. We are using a static DB as well as a temporary cache-DB in memory for which we would like to know the size in order to purge elements when needed. Thanks for any info, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size of a memory DB?
> is there a way to get the size of a :memory: db in the sqlite3.exe > command line interpreter? > Or any other way? > Also, would using a small PRAGMA page_size value decrease the amount of memory used? I think the default size is 4096 bytes, how about 512 bytes for an in-memory database with only 3 fields? (string, and two ints?) -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Size of a memory DB?
Hi, is there a way to get the size of a :memory: db in the sqlite3.exe command line interpreter? Or any other way? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
> How many memory has your embedded project? You can create a new > in-memory database and copy there your database data. > That´s what i am currently doing, but we are using too much memory this way, we are out of specs. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
> Speedup tip: > http://article.gmane.org/gmane.comp.db.sqlite.general/41990 > Hello Mike, first of all, thank you for your tips. Yes, i saw that posting, and i am already using it in my code. But thanks again, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compressing read-only dbs?
>>> How about storing the DB file in a cramfs filesystem >>> >> Forgot to say that this is Windows XP, sorry > Erm, compressed attribute under NTFS ? > Maybe 25%-30% reduction in size ... > Yes, nice try, thank you, but this file goes onto a DVD (i should have said that, I am sorry). CramFS looks pretty promising, is there any extension to sqlite which could read a DB file from it under Windows? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compressing read-only dbs?
> How about storing the DB file in a cramfs filesystem Forgot to say that this is Windows XP, sorry. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compressing read-only dbs?
Hi, i was wondering if there is any compression/decompression extension for read-only databases other than Dr Hipps compression extension, which unfortunately is financially out of scope for my current project. Thanks, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
> Or you could have a look at the Perl SQLite functionality: > http://search.cpan.org/~msergeant/DBD-SQLite-0.31/lib/DBD/SQLite.pm > > Yes, Perl is weird initially if you have only (say) written VB !! > Improves the résumé though ... and your ninja status ! > Perl is fine, no problem, but it does not satisfy my criteria for a lean and slim SQlite3 db access (read "command line interpreter") I can use in my DVD-ROM project. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
John Stanton schrieb: > The sqlite3.exe program is set up as a utility and maintenance tool, not > a production environment and is designed to that end. If you want > maximum performance it is not the way to go; instead embed the Sqlite > calls inside your application and optimize access. If you are > performing ad-hoc DB tasks then it or one of the many similar function > Ssqlite tools are appropriate. One is the Firefox plug in. > > You can imagine that having to compile the SQL for over and over instead > of storing and re-using the compiled code adds considerably to overhead > on frequently run jobs. Yes, but I am using Adobe Director as a production environment. This is a single threaded application, which also doesn´t allow for threaded calls to a database. Plus, i got no access to the source code of the so-called Xtra (=DLL) which emits the call to the DB. All i got is an Xtra which spawns a new thread in which the command line executable is run. I need the thread in order to keep my application running smoothly which otherwise stalls. Regading the pre-recording of statements: can this be achieved somehow if the parameters of the call change all the time? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
John, thanks for your suggestions, but i am wondering if any of your suggestions can be applied to the sqlite3.exe command line application? > Prepare your statements only once and then use bind. Do not use > How is this done? Can the command line executable be modified in such a way? > sqlite3_exec. Do not open and close the DB for each read, instead open > once and let the cache work. That´s already done. > Avoid row scans by defining indices. Already there. > Use > the new index selection functionality to force the use of the best > index. Would you care to provide more information about this? A simple link would be enough. > Place large and less frequently accessed columns at the end of > the Sqlite rows. > Ok, thanks. Will do. However, i read out the whole row all the time. BTW, does it help to specify which coloumns i would like instead of all? I would like to get all minus one coloumn in general. > When you obey these rules you will get very good read perfprmance from > Sqlite. The cacheing is important if you are using a slow disk or flash > memory. Look at shared cache mode if you have multiple users Nope, just one from DVD. Thanks again, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
> When you say 'sqlite.exe' I presume you are referring to 'sqlite3.exe' ? > (http://www.sqlite.org/sqlite.html) > Yes. And I am using v3.6.4. > Using the correct INDEX can speed queries up vastly, so if you can > identify how you are accessing the data, and then set that/those > columns as INDEXed, that will help ... > Have done that. It almost doubles my database, but it is worth it. How about the cache size? or does this only pertain to databases which get inserts? > (Techie note > http://20bits.com/2008/05/13/interview-questions-database-indexes/) > Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to speed up read-only databases?
Hi there, i am using a 120MB database in an embedded project (a DVD-ROM project) and was wondering what I can do to speed up its reading using diverse PRAGMA statements. The database is locked, meaning that no data is being inserted or deleted from it. I am solely after speeding up its reading performance. Indices have been set, would augmenting the cache size for Sqlite do something? Grateful for any info, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] More on ICU extension (windows)
Graeme schrieb: > I apparently successfully compiled the ICU extension with: > Has anyone sucessfully compiled the ICU extension for Windows (XP)? If so, i would be very interested in the steps, thank you. Also, isn´t it that the command line interpreter for windows (the one offered at the sqlite site) should have the ICU extension built-in? If so, then i must say that it apparently hasn´t, as i cannot do any SQL statements in it whatsoever. Any diacritic character like "ü" gets apparently garbled in the statements. I am doing something like this select * from cities where name like "münchen"; --> no result whereas if i do select * from cities where name like "m³nchen"; --> correct results Note the weird "upper 3" character in the request, which apparently represents the Umlaut "ü". Either the command line interpreter does not correctly handle diacritic characters, or my sqlite database was wrongly created (it was an automatic conversion from an Access MDB file thought). Thanks for any help, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Like statement and Unicode support
Hi, i am in need for a like statement which correctly resolves diacritic charcaters. I found this posting on the net: http://www.mail-archive.com/sqlite-users@sqlite.org/msg35613.html So i looked for the ICU extension, downloaded it and tried to compile it. Surprise, it also wants some ICU headers, so I went there and downloaded the latest ICU unicode package. I still cannot compile it - the needed header files are there and being sucked in while compiling apparently, yet the linker complains about a missing "unicode.obj" file at link time. can anyone help? I am on Windows XP and ideally looking for a precompiled version of the DLL. Thanks, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: sqliteman 1.2.0
Hi Petr, > I'm glad I can announce new stable version of Sqliteman - the GUI for > developers and admins: > http://sqliteman.com/ > I gave it a try, and it seems as if SQLiteman can´t handle extensions for databases, is this right? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ambigous column
Simon Davies schrieb: > Christophe, > > iso is a column in the subquery and in countries. > > so: > > select distinct * from (select * from Cities where name like > 'dusseldorf%' union select * from Staedte where name like > 'düsseldorf%') as sub, countries where sub.iso=countries.iso; > Yes, that was it! I was looking for the "as sub" part, i didn´t know how to name my subquery. Thanks a bunch, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ambigous column
I think that the column "ISO" is pretty clear here? select distinct * from (select * from Cities where name like 'düsseldorf %' union select * from Staedte where name like 'düsseldorf%'),Countries where ISO=countries.iso order by class; SQL error: ambiguous column name: ISO I mean the ISO from the first selection? This used to work... Can anyone help? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Brain error
> Use actual field names in lookup table for idField, xField and yField. > Thank you! -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree extension question
Shane Harrelson schrieb: > Were you able to try this Christophe? I expect it to only be a very slight > performance improvement, but I'm still curious as to how much. > Shane, thanks for getting back to me for this, but I haven´t tried this yet. I will do so now. However, i need 7 dimensions now in my rtree class, as i added the size of the city to the rtree table (along with lon and lat) My setup is somewhat special, in that i am using the sqlite3 command line interpreter for querying the database and then retrieving the info via stdout. I was also busy investigating a crash that occured when using the rtree extension under Windows Vista - it seems as if there is a path size limit and/or path character issue in sqlite3. My client was using a long, convulated path to the directory holding the database and extension, and the app kept crashing although it worked here for me on my Vista machine. Moving the app to a root folder of the disk fixed the issue apparently. I got several questions regarding this: - what is the path length imposed in sqlite for extension loading? - are there any special characters not to use in a path to an extension? - are there any special switches one is supposed to use for Vista compilation of the rtree extension? - what is the scheduled publishing date (if any) for Sqlite 3.6 in which the rtree extension is supposed to be part of the standard distribution? I´d rather prefer to use an official build of the rtree than mine, as I am having trouble to get consistent file sizes when compiling the rtree.dll. Strangely enough, the first version that actually worked of the rtree dll is just 25Kb in size. Any other attempt since then to compile a release version of the dll always resulted in a clearly bigger filesize, like 80Kb. Smallest i am getting now is about 60Kb, and i can´t figure out what I did, as this is exactly the same source code, minus the manifest file. But even by readding it, the resulting dll is always bigger... -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree extension question
> You can improve performance (space/speed) a little by changing the > RTREE_MAX_DIMENSIONS at the top of rtree.c to match you data set. It > defaults to 5 dimensions, but you could reduce to this to 2, or 3 with city > size. > > #define RTREE_MAX_DIMENSIONS 5 > Hi Shane, thanks for the answer. AFAIK, this wouldn´t work though, as 3 dimensions would mean that you could only query if a given point is in a line? If i do a 3 dimensional rtree, then 1 field = ID 2 field = longitude_min 3 field = longitude_max (and NOT latitude) The third parameter also always needs to be smaller than the second one passed (or generally spoken, the second one has always to be bigger than the first one), otherwise a rtree request wouldn´t work... Say we would do such a 3 dimensional rtree (id, longitude, latitude) - how could I query for cities in a given rectangle? Select * from rtree where longitude>longitude_minimal and longitudelatitude_minimal and latitude<latitude_maximal? Would that actually work? Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rtree extension question
Hi, i am now using the rtree extension in my project, yet still see some slowdown for some scenarios (especially for searches in narrow/small rectangles). I was therefore wondering if... - one can somehow index the fields of an rtree table, and if so how? - if there could be any other information in an rtree table other than the integer ID and then the real values for the rectangles. The reason i am asking for this is because this yields to aways the same setup - an rtree lookup table, which ndexes back to the real information via its ID. I would eventually like to ease my normal data table for the entries in the rtree table (in my case, i?d like to purge the longitude and latitude values that are used in the rtree table from the "normal" data table, as they are redundant in there), yet this yields to slower queries. Usually, my rtree is used in queries like select * from citydatabase where id in (select id from rtree where longitude_min>XX and longitude_maxXY and latitude_max<XZ) Effectively, these are two queries, and the second one may results in a big subset (e.g. for big rectangle, like views from a high altitude on a 3d globe). I was also wondering if there is any way to get SQlite to cache the results of a previous query - since many of my requests are based on a Zoom in or zoom out in a given rectangle, it would probably be wise to cache an initial query with its results and use this for subsequent queries when zooming in for instance. Is there any function that would allow for tests like "new rect is inside older rect" ? Thanks for any insights, Christophe Leske ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thank you
I would like to thank all the participatns of this list for the very useful information i got here the last days. A big thank you to everyone, including of course Mr Hipp. The rtree implementation is really quick and does work like a charm. Best regards, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
> If you aren't storing the lat and long data in the main table anymore, > you will have to join the RTree table on the id to get that data. I'm > guessing about your tables definitions, but you should get the idea from > this: > Yes, that is my setup, however, the new query is slow as hell.. so i better duplicate the lat/long data then in my city table. Uh, i am never satisfied! :-) -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Dennis Cote schrieb: > Shane Harrelson wrote: > >>> -- a further simplification of the general case that removes >>> -- redundant terms >>> select * from City >>> where id in >>> ( >>> select id from CityLoc >>> where (lat_min < :max_lat and lat_max > :min_lat) >>> and (long_min < :max_long and long_max > :min_long) >>> ) >>> and class <= :max_class >>> order by class >>> limit 20; >>> I need lat and long pos from CityLoc. I got this currently, sqlite> Select cities.*, citylookup.longitude_min from cities,citylookup where c ities.id in (select id from citylookup where (citylookup.longitude_min>-45.0 0 and citylookup.longitude_max<45.00) and (citylookup.latitude_min>-45.11050 2 and citylookup.latitude_max<44.889498)) and cities.class_dds<2 order by class_ dds limit 50; However, this doesn´t give me the city with the longitude and latitude for its position (which i need to position a label). -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Any SQLite GUI application that can handle SQlite files with extension tables?
Hi, every SQlite file that has extension files in it is being reported to me by SQLite Database Browser as being empty. Is there any GUI app on Windows that can handle databases which have extension tables in it? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
> I added an index on the ID field for the search in the city database, > that helped a bit, but i am dissapointed that the rtree search is not > faster than the normal search for bigger areas.. Here are the query times in ms for full globe view with zooming in to Romania: -- 21290 -- full globe view -- 5338 -- 2347 -- 2621 -- 82 -- romania The last one is pretty good - i get almost all the cities in the country in 82ms, which is great. But the intial one is way to slow, it blocks the app 21 seconds - granted, it is the inital start up, but still... Here is the SQL used, with the query time below it. The database "Citylookup" is an rtree-table: -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>-45.00 and citylookup.longitude_max<45.00) and (citylookup.latitude_min>-45.110066 and citylookup.latitude_max<44.889934)) and cities.class_dds<2 order by class_dds limit 50" -- 2008 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>-36.913433 and citylookup.longitude_max<53.086567) and (citylookup.latitude_min>-29.448473 and citylookup.latitude_max<60.551527)) and cities.class_dds<2 order by class_dds limit 50" -- 4305 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>-28.706917 and citylookup.longitude_max<61.293083) and (citylookup.latitude_min>-5.173247 and citylookup.latitude_max<84.826753)) and cities.class_dds<2 order by class_dds limit 50" -- 4299 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>1.764689 and citylookup.longitude_max<28.204563) and (citylookup.latitude_min>32.128411 and citylookup.latitude_max<46.077725)) and cities.class_dds<4 order by class_dds limit 50" -- 425 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>23.468423 and citylookup.longitude_max<50.946270) and (citylookup.latitude_min>34.570643 and citylookup.latitude_max<48.494728)) and cities.class_dds<4 order by class_dds limit 50" -- 278 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>13.282298 and citylookup.longitude_max<40.226794) and (citylookup.latitude_min>33.355038 and citylookup.latitude_max<47.291672)) and cities.class_dds<4 order by class_dds limit 50" -- 297 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>21.390184 and citylookup.longitude_max<34.262570) and (citylookup.latitude_min>37.546776 and citylookup.latitude_max<44.330523)) and cities.class_dds<5 order by class_dds limit 60" -- 63 -- "Select * from cities where id in (select id from citylookup where (citylookup.longitude_min>21.477932 and citylookup.longitude_max<29.315407) and (citylookup.latitude_min>40.614945 and citylookup.latitude_max<44.657669)) and cities.class_dds<6 order by class_dds limit 80" -- 36 -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Shane Harrelson schrieb: > Dennis- > > Your last "simplification": > I never got that email from Dennis, I would be very interested in it. Dennis, this is actually what i am currently doing. However: i see no speed up for large areas (half the globe, e.g.), but considerable ones for small areas (a country like france for instance), as well as very small areas (maximum zoom). I added an index on the ID field for the search in the city database, that helped a bit, but i am dissapointed that the rtree search is not faster than the normal search for bigger areas... -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree extension to use with my data
> The "R" in "R-Tree" is for rectangle. The structure is designed to > hold spaces, not points. You want to do something like: > > ... rtree(id, long-min, long-max, lat-min, lat-max) > > For cities where you only have point locations, enter each lat and > long twice. > Well, my database holds only cities for the time being! Does it make sense to use rtree then? Sorry if i am abusing the help of this list - I like SQlite and have studied it for personal pleasure so far, but I am by all means no database expert. Greets, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rtree extension to use with my data
Ok, so i got the rtree extension to work. It does load and creates the tables wanted. Now I am studying the ReadMe (http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.4) and there is this requirement: All r-tree virtual tables have an odd number of columns between 3 and 11. Unlike regular SQLite tables, r-tree tables are strongly typed. The leftmost column is always the pimary key and contains 64-bit integer values. Each subsequent column contains a 32-bit real value. For each pair of real values, the first (leftmost) must be less than or equal to the second. Hmm - well, how is one supposed to make longitude and latitude data fit in there? I have this so far: sqlite> create virtual table cityLookUp using rtree(id, longitude, latitude) But longitude can be bigger than latitude, and vice versa, so how would one fill and use this table for queries? I reckon I would further use the IDs I got back from the query to look up the corresponding records in my normal table, right? Something like select * from cities where cities.id=(select id from cityLookUp where ??? Long and Lat clause) -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
> You should modify the rtree.c source file and add the following before each > public function: >__declspec(dllexport) > > So for instance, line 2772: >int sqlite3_extension_init( > becomes: >__declspec(dllexport) int sqlite3_extension_init( > Thank you, I got it to work!!! Now, let´s see how we can this thing to work with my data ... Thank you, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Cole, thanks for your help. > I doubt that you will. They are going to produce the same code. I would > stick with between since it is logically clearer. > Yes, this is also what I am seeing here from my timings so far. > I would also use a single index on either longitude or latitude not > both. This will make the index smaller and denser, and therefore > somewhat faster to access. The second field in a compound index is only > useful if the first field is being tested for equality (i.e where long = > ? and lat between ? and ?). > > I suspect you will get the best results (short of switching to an RTree > index) using a query like this. > Thank you very much, that was very helpful and informative. I will do so. Thnks again, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
> If you can tell me what platform you're compiling for (processor, O/S > version, etc.), and what build tools > (cygwin/gcc, mingw, MSVC, etc.) you're using, I will try and walk you > through the steps for building the > RTree module as a separate DLL. > Shane, I got a version, but it is apparently not working. It is 23Kb in size, named rtree.dll and sits right next to the command line tool and the db. To load it, I do this: D:\Arbeit\__Projekte\2007\MMCD\SRC\globe\DB>sqlite3 countries.db SQLite version 3.5.9 Enter ".help" for instructions sqlite> .load rtree.dll Die angegebene Prozedur wurde nicht gefunden. The last sentence says that the specified procedure can´t be found. Any help is much appreciated - again, i am on Windows, using Visual Studio 2005. Thanks and greets, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Sorry, I was too quick - i now got a 80Kb rtree.dll file which seems fine. I will test it. Thanks to everyone for your support, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
> If you can tell me what platform you're compiling for (processor, O/S > version, etc.), and what build tools > (cygwin/gcc, mingw, MSVC, etc.) you're using, I will try and walk you > through the steps for building the > RTree module as a separate DLL. > Hi, i am on Windows, and I got myself the source ZIP and the rtree files pointed out by Dr Hipp. I am targeting Windows XP, single processor, and using Visual Studio 2005, but i am not bound to this if there are better options. I just got something by setting up a simpe project in Visual Studio 2005. Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
> To compile the R-Tree extension, you only need rtree.c and rtree.h, > which you can pull directly from the website without having to use > CVS. (OK, you'll probably also need sqlite3.h and sqlite3ext.h, but I > assumed you already have those.) > Hi, thank you for this, i got the files. Is there any document that would show some steps on how to compile the source for Windows in order to create an extension? I am sorry, but i am complete newbie to Sqlite's source. But SQLite rocks! -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
> Let me strongly reiterate that you look into using the new R-Tree > virtual table available for SQLite. R-Trees are specifically designed > to do exactly the kind of query you are asking to do. See > > http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.2 > > R-Trees will be way faster than anything you will do using B-Tree > indices. > Ok, my problem however is that I cannot recompile the DLL, as it is used by my middleware - i am stuck with a precompiled version of SQlite3 that was compiled into my tool as a static lib. Besides, how do I recompile the current version? Or does anyone have a precompiled DLL binary for me? Any help is much appreciated -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What is quicker?
Hi,
i am still fiddling around with my database and was wondering which kind
of query would be quicker?
I have three values i am interested in my request:
- longitude_dds
- latitude_dds
- class_dds (being the importance of the city, with 1 = capital and
6=village)
I have 2 indices so far:
class for class_dds
lola for longitude, latitude
Also, my statement used to be
SELECT * FROM Cities WHERE (longitude_DDS BETWEEN 6.765103 and 7.089129)
AND (latitude_DDS BETWEEN 44.261771 and 44.424779) AND class_dds<6 ORDER
BY class_dds ASC Limit 20
I understand that BETWEEN gets translated to >= and =< (bigger or
equal, and small or equal).
I am however not seeing any speed improvement when i rewrite my
statement from BETWEEN to a > and < pair, like this:
(longitude_DDS BETWEEN 6.765103 and 7.089129)
becomes
(longitude_DDS>6.765103 and longitude_DDS<7.089129)
I would reckon that this is quicker, as it does not need to check for
equality ("=")?
Also, what is "better", given my indices:
to first query for the class_dds value AND then for longitude and
latitude, or
to first query latitude and longitude, AND THEN go for the class_dds
statement?
In other words, which one should be quicker:
SELECT * FROM Cities WHERE (longitude_DDS>6.765103 and
longitude_dds<7.089129) AND (latitude_DDS>44.261771 and
latitude_dds<44.424779) AND class_dds<6 ORDER BY class_dds ASC Limit 20
or
SELECT * FROM Cities WHERE class_dds>6 AND (longitude_DDS>6.765103 and
longitude_dds<7.089129) AND (latitude_DDS>44.261771 and
latitude_dds<44.424779) ORDER BY class_dds ASC Limit 20
Also:
someone suggested to divide up the tables - something which led me to
the idea to create different views for each class_dds value:
create view Level1 as Select * from cities where class_dds=1
create view Level2 as Select * from cities where class_dds=2
create view Level3 as Select * from cities where class_dds=3
create view Level4 as Select * from cities where class_dds=4
create view Level5 as Select * from cities where class_dds=5
create view Level6 as Select * from cities where class_dds=6
So i could do select statements like:
select * from Level1
Union
select * from Level2
Union
select * from Level3
WHERE class_dds>6 AND (longitude_DDS>6.765103 and
longitude_dds<7.089129) AND (latitude_DDS>44.261771 and
latitude_dds<44.424779) ORDER BY class_dds ASC Limit 20
Would that be quicker eventually?
--
Christophe Leske
www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
> class_dds has a maximum value of 6, so there where-clause "class_dds<11" > is totally unecessary - if i ditch this part, the response time is > coming down to 900ms from 2700ms for my request. > I will now time again. > Some new timings - i basically got it. What I find to be weird is that just ONE index seems to yield the same results as several fields indexed: no indices: -- "TIME for Between statement:2548" -- 11 -- "TIME for <> statement:2528" -- 11 -- "TIME for <> statement without ORDER BY-clause:1070" -- 11 -- "TIME for <> statement without ORDER BY-clause:987" -- 11 On index (longitude) - WHOAA!: -- "TIME for Between statement:18" -- 11 -- "TIME for <> statement:11" -- 11 -- "TIME for <> statement without ORDER BY-clause:10" -- 11 -- "TIME for <> statement without ORDER BY-clause:9" -- 11 two indices (longitude and latitude): -- "TIME for Between statement:11" -- 11 -- "TIME for <> statement:11" -- 11 -- "TIME for <> statement without ORDER BY-clause:10" -- 11 -- "TIME for <> statement without ORDER BY-clause:10" -- 11 3 field index (class_dds, longitude, latitude) - ?What gives?: -- "TIME for Between statement:2540" -- 11 -- "TIME for <> statement:2528" -- 11 -- "TIME for <> statement without ORDER BY-clause:999" -- 11 -- "TIME for <> statement without ORDER BY-clause:991" -- 11 a 2 field index (longitude, latitude): -- "TIME for Between statement:10" -- 11 -- "TIME for <> statement:11" -- 11 -- "TIME for <> statement without ORDER BY-clause:10" -- 11 -- "TIME for <> statement without ORDER BY-clause:9" -- 11 -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
Christophe Leske schrieb: >> Question, have you tried an index on class_dds, longitude_DDS, and >> latitude_DDS? >> >> CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS); >> >> Since all three fields are used in the query, I am curious if that would >> help in any way. >> >> > Doesn´t do anything, there is something else going here, i think - it > might well be that because of the order - statement, none of the indices > is actually being used: > I got it: SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 class_dds has a maximum value of 6, so there where-clause "class_dds<11" is totally unecessary - if i ditch this part, the response time is coming down to 900ms from 2700ms for my request. I will now time again. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
> Question, have you tried an index on class_dds, longitude_DDS, and > latitude_DDS? > > CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS); > > Since all three fields are used in the query, I am curious if that would > help in any way. > Doesn´t do anything, there is something else going here, i think - it might well be that because of the order - statement, none of the indices is actually being used: here some more timings (yours is at the end - the number between the timings is a count of the result sets just to make sure that they all return the right set of 11 results): No indices whatsoever: -- "TIME for Between statement:2794" -- 11 -- "TIME for <> statement:2775" -- 11 One index (longitude): -- "TIME for Between statement:2776" -- 11 -- "TIME for <> statement:2770" -- 11 two indices (longitude and latitude separetely): -- "TIME for Between statement:2786" -- 11 -- "TIME for <> statement:2792" -- 11 A 3 field index (class_dds, latitude_dds, longitude_dds): -- "TIME for Between statement:2783" -- 11 -- "TIME for <> statement:2762" -- 11 -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
Federico Granata schrieb:
> can you post those rows with
> .mode insert
> so I can do a fast try ?
>
INSERT INTO table VALUES('Pietraporzio',5,-1,7.032936,44.345913);
INSERT INTO table VALUES('Sambuco',5,-1,7.081367,44.33763);
INSERT INTO table VALUES('Le Pra',6,-1,6.88,44.316667);
INSERT INTO table VALUES('Bagni',6,-1,7.08,44.3);
INSERT INTO table VALUES('Argentera',6,-1,6.937569,44.396168);
INSERT INTO table VALUES('Bersezio',6,-1,6.970739,44.377898);
INSERT INTO table VALUES('Saint-Dalmas-le-Selvage',6,-1,6.867705,44.285194);
INSERT INTO table VALUES('Ferrere',6,-1,6.950052,44.354508);
INSERT INTO table VALUES('San Bernolfo',6,-1,7.039278,44.263371);
INSERT INTO table VALUES('Murenz',6,-1,6.998868,44.348969);
INSERT INTO table VALUES('Bagni di Vinadio',6,-1,7.074884,44.290033);
--
Christophe Leske
www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
There is virtually no difference in using indices or not in my query. I also tried to reformulate my statement in order not to use BETWEEN but a sandwiched > and < statement: SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 became SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS>6.765103 and longitude_DDS<7.089129) AND (latitude_DDS>44.261771 and latitude_DDS<44.424779) ORDER BY class_dds ASC Limit 20 The timing with a latlon index (latitude and longitude indexed): TIME:2814 ms The timing with just one index (latitude): TIME:2797 ms Timing with two indices (latitude and longitude separetly): TIME:2787 ms Timing with two indices (lat/lon) and the reformulated query above: TIME:2763 ms So all in all, there is no substantial speed gain to be found - it probably has to do with the fact that i am sorting at the end? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
> Can you give me some row of your db (also fake data are ok) so I try to > populate a db with 840k row and test your query on my machine ... > You can either take these rows here: Pietraporzio|5|-1|7.032936|44.345913 Sambuco|5|-1|7.081367|44.33763 Le Pra|6|-1|6.88|44.316667 Bagni|6|-1|7.08|44.3 Argentera|6|-1|6.937569|44.396168 Bersezio|6|-1|6.970739|44.377898 Saint-Dalmas-le-Selvage|6|-1|6.867705|44.285194 Ferrere|6|-1|6.950052|44.354508 San Bernolfo|6|-1|7.039278|44.263371 Murenz|6|-1|6.998868|44.348969 Bagni di Vinadio|6|-1|7.074884|44.290033 or you can download a 170 MB big database similar to the one i am using, but populatet with free data from geonames.org from http://www.multimedial.de/earth/DB/free.7z That one is 1.4 Gb unpacked, and has yet no indices (i think, i prepared it some time ago). It should yield to similar results. Thanks for your time, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
Wilson, Ron P schrieb: > I'm not a guru yet, but I think you are not using the latlon index in > your query. Perhaps if you index on lat and lon separately your query > will use those indices. I think the lines below indicate using the > indices on class_dds and rowid. > Thanks to everyone who responded, i got some pretty good feedback. Thanks also for the tiling hint, but my application is already written, and I have to stick to the databse given. I might reconsider though if the performance is still bad. Can someone tell me what kind of performance one is to expect from a 40Mb Sqlite database like the one I have? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
Wilson, Ron P schrieb: > Hi Christophe, > > 1. Please give us an example query. SELECT * FROM Cities where > LONGITUDE_DDS=? AND LATITUDE_DDS=? > 2. Paste in the EXPLAIN results from the command line tool. > 3. Is the database file local or are you accessing it over a network? > Hi, the database file is local, right next to the app using it. I am using the sqlite3.exe command line tool for the queries, but would eventually like to ditch it for the native support. However, since the app I am using is a single threaded application (Adobe Director), eventual queries that take too long to complete do completely block the app which is why i have threaded the queries using a multi-threaded shell extension which does the queries, then reports back the results. A typical query that causes problems would be: SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 Am i right that no matter what limit is given to the SQL statement, the complete query is executed first, AND THEN filtered according to the limit? This is what i think i a seeing here... I am therefore also after something that cuts off the query after a certain amount of results have been found. The explain results from the command line tool: sqlite> EXPLAIN SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWE EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORD ER BY class_dds ASC Limit 20 ...> ; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 EXPLAIN SELECT * FROM Cities WHERE class_ dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWE EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 ; 00 1 Noop 0 0 000 2 Integer201 000 3 MustBeInt 1 0 000 4 IfZero 1 42000 5 Integer112 000 6 Real 0 3 0 6.765103 00 7 Real 0 4 0 7.089129 00 8 Real 0 5 0 44.2617710001 00 9 Real 0 6 0 44.424779 00 10Goto 0 43000 11SetNumColumns 0 6 000 12OpenRead 0 3 000 13SetNumColumns 0 2 000 14OpenRead 2 6 0 keyinfo(1,BINARY) 00 15Rewind 2 408 0 00 16SCopy 2 8 000 17IsNull 8 40000 18Affinity 8 1 0 cb 00 19IdxGE 2 408 1 00 20Column 2 0 11 00 21IsNull 1139000 22IdxRowid 2 11000 23MoveGe 0 0 11 00 24Column 0 3 12 00 25Lt 3 3912collseq(BINARY) 6b 26Gt 4 3912collseq(BINARY) 6b 27Column 0 4 17 00 28Lt 5 3917collseq(BINARY) 6b 29Gt 6 3917collseq(BINARY) 6b 30Column 0 0 22 00 31Column 2 0 23 00 32Column 0 2 24 00 33Column 0 3 25 00 34Column 0 4 26 00 35Column 0 5 27 00 36ResultRow 226 000 37AddImm 1 -1000 38IfZero 1 40000 39Next 2 19000 40Close 0 0 000 41Close 2 0 000 42Halt 0 0 000 43Transaction0 0 000 44VerifyCookie 0 202 000 45TableLock 0 3 0 Cities 00 46Goto 0 110 00 -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to speed up my queries?
Hi, i am a new member of this list and interested in speeding up my sqlite queries. I am using SQlite in a 3d environment which is close to Google Earth or Nasa WorldWind. We have a city database that is being queried regurlarly depending on the lat/long position of the viewport in order to show city names and labels. Plus, there are additional databases for special features, like natural hazards and catastrophies. The city database has around 840.000 records, the following schema and weights currently short under 40Mb: sqlite> .schema cities CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS NUMERIC, LONGI TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC); CREATE INDEX class ON Cities(CLASS_DDS ASC); CREATE INDEX latlon on Cities(latitude_dds,longitude_dds); My questions are: - how do I speed up the queries? For small lat/long windows, and high classes for the cities, i get long query times (e.g. about 600ms) Is this reasonable to ask for, or IS that already a top speed for this kind of query? - I have indexed latitude AND longitude,as you can see above. Is this ok? - I came across the EXLPAIN command, and have read an email by someone on this list on how to analyze my queries. I should probably do that, yet i am unfamiliar with reading the output of the Explain command. Thanks for your time and eventual help, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
> First - some sample code or queries would be helpful. > Second - start a new topic > (http://en.wikipedia.org/wiki/Thread_hijacking). > Yes, sorry, my fault, i am a lazy bum these days. My apologies. This was also an indirect test if this list is still alive.. Will start a new thread right away, thanks, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Hi, i am new to this list, can anyone point me to a good FAQ document on how to improve the speed of a SQLite database? I got a city database (a geographical database) that I need to query for lat/long values, and importance of the city (class value). For my smallest query, i am waiting several hundred milliseconds in a database that is about 40Mb in size and that has indices on latitude and longitude, as well as the class itself. I have indexed the database, analyzed it (in order to get the stats table), and vacuumed it. Any other hint on how one can speed up the queries? I ahve set PRAGME CACHE as well... Thanks in advance, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
