Re: [sqlite] What is quicker?
Dennis Cote wrote: > John Stanton wrote: >> But for practical arithmetic probability or possibility is not close >> enough. It must be certainty. > > There is a possibility that your code could be asked to compare two > equal floating point numbers. To be correct, it must handle that case. > If it does not, it is certainly broken. > > While you must be careful with floating point numbers, statements such > as yours: "The point about using floating point is that there is no > equal, only less or greater, because it is an approximation." just add > confusion to the issue. There is very definitely an "equal" when dealing > with floating point numbers, and it has nothing to do with floating > point format sometimes being an approximation. > > >> I make the point because it has been my >> observation over the years that some of the silliest and most >> embarrassing simple IT errors have been caused by the inappropriate >> usage of floating point numbers. > > That is true, but neither this or your first post was applicable to the > correction that Steve suggested (and Richard accepted). > > In fact the equal case, when xmin and xmax are set to the same value is > required to allow points (rectangles with zero area and zero length) to > be handled by the RTree module. It would be incorrect to say that xmin > must be less than xmax when they can be equal. > > Dennis Cote > I was not interested in being pedantic, just in offering some practical rule of thumb advice. > _ > 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] What is quicker?
In relation with the floating point number and its IEEE internal representation, may be of interest: http://babbage.cs.qc.edu/IEEE-754/ HTH Adolfo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Hence the word "SOME"... :) Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi Ken, On 6/6/08, Ken wrote: > Some numbers can be represented exactly using the > floating point type. . Here is a reference from "The C++ Programming Language, 3rd Edition" by Bjarne Stroustrup, Page 835, section - C.6.2.6: int i = float ( 1234567890); left i with the value 1234567936 on a machine, where both ints and floats are represented using 32 bits. Clearly, it is best to avoid using potentially value-destroying implicit conversions. . - -- Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Hi Ken, On 6/6/08, Ken <[EMAIL PROTECTED]> wrote: > Some numbers can be represented exactly using the > floating point type. . Here is a reference from "The C++ Programming Language, 3rd Edition" by Bjarne Stroustrup, Page 835, section - C.6.2.6: int i = float ( 1234567890); left i with the value 1234567936 on a machine, where both ints and floats are represented using 32 bits. Clearly, it is best to avoid using potentially value-destroying implicit conversions. . - -- Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
John Stanton wrote: > > But for practical arithmetic probability or possibility is not close > enough. It must be certainty. There is a possibility that your code could be asked to compare two equal floating point numbers. To be correct, it must handle that case. If it does not, it is certainly broken. While you must be careful with floating point numbers, statements such as yours: "The point about using floating point is that there is no equal, only less or greater, because it is an approximation." just add confusion to the issue. There is very definitely an "equal" when dealing with floating point numbers, and it has nothing to do with floating point format sometimes being an approximation. > I make the point because it has been my > observation over the years that some of the silliest and most > embarrassing simple IT errors have been caused by the inappropriate > usage of floating point numbers. That is true, but neither this or your first post was applicable to the correction that Steve suggested (and Richard accepted). In fact the equal case, when xmin and xmax are set to the same value is required to allow points (rectangles with zero area and zero length) to be handled by the RTree module. It would be incorrect to say that xmin must be less than xmax when they can be equal. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
One of the things that people fail to understand is that floating point
numbers are stored in *binary*. In fact, I bet a number of people who
understand the exact binary formatting of integers don't understand that the
technique translates pretty much directly into floating point: a floating
point number is recorded like 1.0111001010101110101
So when they hear "floating point numbers are inaccurate because of rounding
errors", they often think "Oh, that doesn't apply to me -- I'm not doing
anything with enough decimal places to hit a rounding error." This exact
sort of problem cropped up in a financial transaction processing system I
helped maintain.
Problem: Sometimes, members's transactions would be declined even though
they had enough to cover the transaction.
This was narrowed down to
Specific problem: Member X is unable to make a purchase for $1.40 cents
despite them having 40 cents in their account and a $1.00-off coupon.
I looked at the code in question and announced it was a rounding error due
to the use of a 'double' for storing currency values. "How is that
possible? It's only two decimal places, I've seen these things work for half
a dozen decimal places!" I was asked.
So I demonstrated what happened: The application's test was (total amount)
- (balance) <= (coupon). Or something like that. It was years ago; all I
remember is that the three numbers were $1.40, $1.00, and $0.40. So I
translated everything into the internal binary form:
Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
1.1001100110011001100110011001100110011001100110011010 * (2**-2)
To add these together, they need to be adjusted to the same exponent:
Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
0.011001100110011001100110011001100110011001100110011010
* (2**0)
This is where things go wrong. You see that extra '10' at the end of the
member's balance? The floating point process doesn't have room for it, so
it rounds. And much the same way as 0.5 rounds up to 1.0, so does binary
0.1:
Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
0.0110011001100110011001100110011001100110011001100111
* (2**0)
Now we subtract:
1.0110011001100110011001100110011001100110011001100110
- 0.0110011001100110011001100110011001100110011001100111
0.
This is *practically* 1, in much the same way as
0.99 is *practically* 1. But it's still
technically less than 1. So when the application compared it to the coupon
amount, or whichever it was, the rounding error caused a false failure and
the transaction was declined.
Things are easier to understand if you realize that for any fraction (P/Q),
if Q is not exactly a power of 2, then the answer cannot be exactly
represented in binary. In contrast, for our decimal system, any fraction
(P/Q) cannot be represented exactly unless Q can be expressed as (some power
of 2)*(some power of 5).
For your edification, I wrote a Perl script to tell how many Qs offer exact
representations in bases 2, 10, and 60. These are the results:
Bin: 25 of 16777216 (0.00015%)
Decimal: 143 of 16777216 (0.00085%)
Base60: 836 of 16777216 (0.00498%)
This roughly indicates that if you have a number that can be expressed
exactly in decimal, there's only about a 1-in-6 chance that it's *also*
expressible exactly in binary without running into rounding errors. I also
threw in base 60 for comparison -- an arbitrary number is nearly 6 times as
likely to be expressible exactly using base-60 than it is in base-10. GPS
coordinates are expressed using base-60 (degrees, minutes, seconds).
== script ==
#!/usr/bin/perl
my ($b,$d,$b60) = (0,0,0);
my $max = 16_777_216;
for (1..$max) {
my $q = $_;
while ( ($q % 2) == 0 ) { $q /= 2; }
if ($q == 1) { $b++; }
while ( ($q % 5) == 0) { $q /= 5; }
if ($q == 1) { $d++; }
while ( ($q % 3) == 0) { $q /= 3; }
if ($q == 1) { $b60++; }
}
printf "%s: %d of $max (%.5f%%)\n", @$_, 100*$_->[1]/$max
for [Bin=>$b],[Decimal=>$d],[Base60=>$b60];
#!/usr/bin/perl
my ($b,$d,$b60) = (0,0,0);
my $max = 16_777_216;
for (1..$max) {
my $q = $_;
while ( ($q % 2) == 0 ) { $q /= 2; }
if ($q == 1) { $b++; }
while ( ($q % 5) == 0) { $q /= 5; }
if ($q == 1) { $d++; }
while ( ($q % 3) == 0) { $q /= 3; }
if ($q == 1) { $b60++; }
}
printf "%s: %d of $max (%.5f%%)\n", @$_, 100*$_->[1]/$max
for [Bin=>$b],[Decimal=>$d],[Base60=>$b60];
== snip ==
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Some numbers can be represented exactly using the floating point type. According to ieee 754 spec: "All integers that are a power of 2 can be stored in a 32 bit float without rounding Precision decreases exponentially as the exponent increases" So for those numbers equality is necessary. John Stanton <[EMAIL PROTECTED]> wrote: Dennis Cote wrote: > John Stanton wrote: >> The point about using floating point is that there is no equal, only >> less or greater, because it is an approximation. If you want to use >> equality you must use some form of integer or fixed ppint numbers. >> > > That's not true at all. While it is not reliable to use equality tests > for different floating point numbers, there is very definitely the > possibility of two floating point numbers being equal. > > For any two floating point numbers with the same bit pattern, both < and > > will be false. All code using floating point numbers must be able to > handle the equal case correctly, it is usually just merged with one of > the < or > cases. > > That is what Steve's correction was. He suggested it should merge the > equal case with the < case. It usually makes no sense to merge the < and > > cases (this is simply another way to say not equal) as it was > originally documented. > > Dennis Cote But for practical arithmetic probability or possibility is not close enough. It must be certainty. I make the point because it has been my observation over the years that some of the silliest and most embarrassing simple IT errors have been caused by the inappropriate usage of floating point numbers. > ___ > 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] What is quicker?
John Stanton wrote: > > The point about using floating point is that there is no equal, only > less or greater, because it is an approximation. If you want to use > equality you must use some form of integer or fixed ppint numbers. > That's not true at all. While it is not reliable to use equality tests for different floating point numbers, there is very definitely the possibility of two floating point numbers being equal. For any two floating point numbers with the same bit pattern, both < and > will be false. All code using floating point numbers must be able to handle the equal case correctly, it is usually just merged with one of the < or > cases. That is what Steve's correction was. He suggested it should merge the equal case with the < case. It usually makes no sense to merge the < and > cases (this is simply another way to say not equal) as it was originally documented. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Steve Kallenborn wrote: > D. Richard Hipp wrote: >> On Jun 4, 2008, at 7:13 AM, Derrell Lipman wrote: >> >>> On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp <[EMAIL PROTECTED]> >>> wrote: >>> 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 >>> The README contains this text: >>> >>> CREATE VIRTUAL TABLE USING rtree() >>> >>>For example: >>> >>> CREATE VIRTUAL TABLE boxes USING rtree(boxno, xmin, xmax, ymin, >>> ymax); >>> CREATE VIRTUAL TABLE boxes USING rtree(1, 1.0, 3.0, 2.0, 4.0); >>> >>> Is that last line supposed to be >>> INSERT INTO boxes VALUES (1, 1.0, 3.0, 2.0, 4.0); >>> ? >> Yes. >> http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.3 >> >> D. Richard Hipp >> [EMAIL PROTECTED] >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > In the same document: > "For each pair of real values, the first (leftmost) must be > less than or greater than the second." > > should presumable read "less than or equal to" > > Thanks >SteveK The point about using floating point is that there is no equal, only less or greater, because it is an approximation. If you want to use equality you must use some form of integer or fixed ppint numbers. > ___ > 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] What is quicker?
On Jun 5, 2008, at 12:55 AM, Shane Harrelson wrote: > Once you get it working with your data, you may want to play around > with the > defines at the top of rtree.c. > > >> /* Either, both or none of the following may be set to activate >> ** r*tree variant algorithms. >> */ >> #define VARIANT_RSTARTREE_CHOOSESUBTREE 0 >> #define VARIANT_RSTARTREE_REINSERT 1 >> >> /* >> ** Exactly one of the following must be set to 1. >> */ >> #define VARIANT_GUTTMAN_QUADRATIC_SPLIT 0 >> #define VARIANT_GUTTMAN_LINEAR_SPLIT 0 >> #define VARIANT_RSTARTREE_SPLIT 1 > > > These defines affect the algorithms used for manipulating the internal > R-Tree data structures, and you may see improved performance by > tuning it > for your data. The algorithms turned on by default are those that seemed to create the best R-Tree structure for the randomly generated data set that was used to test performance while writing code (see the rtree_perf.tcl script in cvs). Messing around with them might speed up INSERT operations but generate a tree structure that is a bit slower to query. That's just a guess though. If you really care about performance, it is probably worth testing a few other combinations with "real" data. Please post any results you get! Dan. > -Shane > > > On 6/4/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > >> >>> 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 >> > ___ > 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] 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?
Christophe Leske wrote: > 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). > > I had the lat and long data duplicated in the RTree table (i.e. I was treating it much like an index). create table City ( id integer primary key, nametext, lat real, longreal, class integer ); create virtual table CityLoc using rtree ( id integer referneces City, lat_min real, lat_max real, long_minreal, long_maxreal ); 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: Select cities.*, citylookup.longitude_min, citylookup.latitude_min, from cities join citylookup on citylookup.id = cities.id where cities.id in ( select id from citylookup as c where c.longitude_min>-45.00 and c.longitude_max<45.00 and c.latitude_min>-45.110502 and c.latitude_max<44.889498 ) and cities.class_dds<2 order by class_dds limit 50; HTH Dennis Cote ___ 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
Re: [sqlite] What is quicker?
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; > > > > If I understand correctly, this is just suppose to select the City id's from > CityLoc that are completely inside the selection rectangle (with the > additional constraints)? > The sub select on the RTree table should return the ids of all the cities with a bounding rectangle that overlaps any part of the selection rectangle, not only those that are completely inside the selection rectangle. The outer select does the work of applying the other constraints, ordering, etc. I see now that this optimized where condition also correctly handles the single point special case (i.e. where lat_min = lat_max and long_min = long_max, and the rectangle has zero area) with the same number of comparisons so it can be used in all cases. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
In addition to playing with the splitting algorithms, since you are compiling your own DLL, you can customize the R-Tree module for 2 dimensions -- i.e. hard-code it for 5 columns of data. This would *roughly* be accomplished by replacing all occurrences of pRtree->nDim with (2) and replacing all occurrences of pRtree->nBytesPerCell with (24) and recompiling (don't forget to enable optimizations in MSVC). You will also have to comment out lines like: pRtree->nDim = (argc-4)/2; and pRtree->nBytesPerCell = 8 + pRtree->nDim*4*2; I have not tried this, but a quick review of the code implies this would create a version of the R-Tree module "hard-coded" to support 5 columns (or 2-dimensions) which should be a little faster. If you play with the database page size as was previously suggested, you may also want to try adjusting the RTREE_MAXCELLS #define (although it's not clear to me how this would affect performance). -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Regarding: -- 21290 -- full globe view ... is way to slow, it blocks the app 21 seconds - granted, it is the inital start up, but still. Well, some people aren't satisfied unless they can have the whole world, apparently.;-) 1) Have you already exhausted tuning the database with larger page sizes, cache sizes, etc. using the PRAGMA commands?(Note that you must recreate your database in order to try a different page size.) Maybe you have and I just missed it in all the emails on this topic. 2) How would selecting the full globe be useful? Would that not return a gigabyte of data consisting of a million table rows? Can your application really make use of these million items or do you instead want this set severely reduced? If it DOES truly want the million rows, then the 21 seconds of database time sounds pretty impressive -- and might turn out to be small compared to the time for you to further process them. If you want it severely restricted, say based on "largest populations" then you may decide to have two city tables -- one for the largest 1 cities, say, and the other table holding the remaining million. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
The default R-Tree code uses the "R*-tree algorithm" for splitting. While this should typically perform better than the other two variants (Guttman Quadratic and Linear splitting), you may want to test them with your data set and queries to see if either performs better. You will need to compile and change the #defines at the top of rtree.c to test these variants. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Christophe Leske wrote: > 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... > Why would you expect it to be faster? Think about it for a second ... 1) if we do a search that covers the whole world, are we not doing the equivalent of a full table scan + the additional over head of the the rtree processing. This is just like doing a query the selects all entries in a btree indexed table. A full scan is the slowest. Then in a btree indexed table selecting a single record via the index is fastest, likewise with an rtree, if you narrow the search area to a very small area you eliminate most of the records and return just one I would expect that to be the fastest. -Steve W ___ 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] What is quicker?
> Is this how you expect the RTree tables to be used in a case like the OP > is interested in? > > create table City ( > id integer primary key, > nametext, > lat real, > longreal, > class integer > ); > > create virtual table CityLoc using rtree ( > id integer referneces City, > lat_min real, > lat_max real, > long_minreal, > long_maxreal > ); I would have expected the relationship to work the other way, e.g. create table City ( id integer primary key, loc integer references CityLoc(id), nametext, lat real, longreal, class integer ); create virtual table CityLoc using rtree ( id integer primary key, lat_min real, lat_max real, long_minreal, long_maxreal ); Or perhaps a junction table between the two, if it was possible to have a city with more than one location, or if one location could be within two entities. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Dennis- Your last "simplification": > -- 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; If I understand correctly, this is just suppose to select the City id's from CityLoc that are completely inside the selection rectangle (with the additional constraints)? -Shane On 6/4/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > > D. Richard Hipp wrote: > > > > 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. > > > > Richard, > > Is this how you expect the RTree tables to be used in a case like the OP > is interested in? > > create table City ( > id integer primary key, > nametext, > lat real, > longreal, > class integer > ); > > create virtual table CityLoc using rtree ( > id integer referneces City, > lat_min real, > lat_max real, > long_minreal, > long_maxreal > ); > > > -- if CityLoc uses point at center of city > -- ie lat_min = lat_max and long_min = long_max > select * from City > where id in > ( > select id from CityLoc > where lat_min between :min_lat and :max_lat > and long_min between :min_long and :max_long > ) > and class <= :max_class > order by class > limit 20; > > > -- general case where CityLoc has the extents of the > -- city, ie lat_min < lat_max and long_min < long_max > -- and you need to select all cities where any portion > -- is between the limits > -- this is the longest and possibly clearest > -- where condition for the general case but it may > -- execute slower due to the extra comparisons > select * from City > where id in > ( > select id from CityLoc > where ((lat_min between :min_lat and :max_lat) > or (lat_max between :min_lat and :max_lat) > or (lat_min < :min_lat and lat_max > :max_lat)) > and ((long_min between :min_long and :max_long) > or (long_max between :min_long and :max_long) > or (long_min < :min_long and long_max > :max_long)) > ) > and class <= :max_class > order by class > limit 20; > > -- this is an alternate where condition that excludes the > -- the cases that do not overlap the area of interest > select * from City > where id in > ( > select id from CityLoc > where not ((lat_min < :min_lat and lat_max < :min_lat) > or (lat_min > :max_lat and lat_max > :max_lat)) > and not ((long_min < :min_long and long_max < :min_long) > or (long_min > :max_long and long_max > :max_long)) > ) > and class <= :max_class > order by class > limit 20; > > -- yet another alternate where condition after applying De'Morgans > -- rule to the previous inverted logic > select * from City > where id in > ( > select id from CityLoc > where (lat_min > :min_lat or lat_max > :min_lat) > and (lat_min < :max_lat or lat_max < :max_lat) > or (long_min > :min_long or long_max > :min_long) > and (long_min < :max_long and long_max < :max_long) > ) > and class <= :max_class > order by class > limit 20; > > -- 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; > > Dennis Cote > > ___ > 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] What is quicker?
D. Richard Hipp wrote: > > 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. > Richard, Is this how you expect the RTree tables to be used in a case like the OP is interested in? create table City ( id integer primary key, nametext, lat real, longreal, class integer ); create virtual table CityLoc using rtree ( id integer referneces City, lat_min real, lat_max real, long_minreal, long_maxreal ); -- if CityLoc uses point at center of city -- ie lat_min = lat_max and long_min = long_max select * from City where id in ( select id from CityLoc where lat_min between :min_lat and :max_lat and long_min between :min_long and :max_long ) and class <= :max_class order by class limit 20; -- general case where CityLoc has the extents of the -- city, ie lat_min < lat_max and long_min < long_max -- and you need to select all cities where any portion -- is between the limits -- this is the longest and possibly clearest -- where condition for the general case but it may -- execute slower due to the extra comparisons select * from City where id in ( select id from CityLoc where ((lat_min between :min_lat and :max_lat) or (lat_max between :min_lat and :max_lat) or (lat_min < :min_lat and lat_max > :max_lat)) and ((long_min between :min_long and :max_long) or (long_max between :min_long and :max_long) or (long_min < :min_long and long_max > :max_long)) ) and class <= :max_class order by class limit 20; -- this is an alternate where condition that excludes the -- the cases that do not overlap the area of interest select * from City where id in ( select id from CityLoc where not ((lat_min < :min_lat and lat_max < :min_lat) or (lat_min > :max_lat and lat_max > :max_lat)) and not ((long_min < :min_long and long_max < :min_long) or (long_min > :max_long and long_max > :max_long)) ) and class <= :max_class order by class limit 20; -- yet another alternate where condition after applying De'Morgans -- rule to the previous inverted logic select * from City where id in ( select id from CityLoc where (lat_min > :min_lat or lat_max > :min_lat) and (lat_min < :max_lat or lat_max < :max_lat) or (long_min > :min_long or long_max > :min_long) and (long_min < :max_long and long_max < :max_long) ) and class <= :max_class order by class limit 20; -- 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; Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Once you get it working with your data, you may want to play around with the defines at the top of rtree.c. > /* Either, both or none of the following may be set to activate > ** r*tree variant algorithms. > */ > #define VARIANT_RSTARTREE_CHOOSESUBTREE 0 > #define VARIANT_RSTARTREE_REINSERT 1 > > /* > ** Exactly one of the following must be set to 1. > */ > #define VARIANT_GUTTMAN_QUADRATIC_SPLIT 0 > #define VARIANT_GUTTMAN_LINEAR_SPLIT 0 > #define VARIANT_RSTARTREE_SPLIT 1 These defines affect the algorithms used for manipulating the internal R-Tree data structures, and you may see improved performance by tuning it for your data. -Shane On 6/4/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > > > 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 > ___ 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?
Without creating a .DEF file for MSVC to use, you need to tell it which functions to "export". The easiest way to do this is with the __declspec(dllexport). 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( Hope this helps. -Shane On 6/4/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > > > > 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 > ___ 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?
Christophe Leske wrote:
> 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:
>
I doubt that you will. They are going to produce the same code. I would
stick with between since it is logically clearer.
> (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 ("=")?
>
This is not really an issue since the instructions that do the test will
take the same amount of time (i.e. > vs >=, or < vs <=).
> 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?
>
This is perhaps your main issue. Since SQLite is doing a table scan and
eliminating records that don't match your constraints, and it can only
use a single index per table scan, it is best to use the index that will
eliminate the most data. You want to use the most selective index first.
I suspect it would be a longitude or latitude index in your case (one or
the other, but not both). This will select the subset of the data that
matches that one constraint (i.e long between ? and ?). This subset is
then scanned and each record is tested to see if the other conditions
pass or fail. You want to order your tests so that the fewest records
pass each sequential test.
I would remove the index on the class to ensure it is not selected by
SQLite. Especially since your test, class<6, selects nearly all records
(i.e. all except those where class is 6).
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.
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
With a single index on longitude. Depending upon your data it may be
better to index latitude and switch the order of the latitude and
longitude tests in the query.
> 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?
>
I can't see how this would help.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
On Jun 4, 2008, at 8:05 AM, Steve Kallenborn wrote: > In the same document: > "For each pair of real values, the first (leftmost) must be > less than or greater than the second." > > should presumable read "less than or equal to" > OK. Try http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.4 D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 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?
D. Richard Hipp wrote: > On Jun 4, 2008, at 7:13 AM, Derrell Lipman wrote: > >> On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp <[EMAIL PROTECTED]> >> wrote: >> >>> 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 >>> >> The README contains this text: >> >> CREATE VIRTUAL TABLE USING rtree() >> >>For example: >> >> CREATE VIRTUAL TABLE boxes USING rtree(boxno, xmin, xmax, ymin, >> ymax); >> CREATE VIRTUAL TABLE boxes USING rtree(1, 1.0, 3.0, 2.0, 4.0); >> >> Is that last line supposed to be >> INSERT INTO boxes VALUES (1, 1.0, 3.0, 2.0, 4.0); >> ? > > Yes. > http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.3 > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > In the same document: "For each pair of real values, the first (leftmost) must be less than or greater than the second." should presumable read "less than or equal to" Thanks SteveK ___ 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?
This wiki page (http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions) talks about SQLite's loadable extension functionality. 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 On 6/4/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > > > > 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 > ___ 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?
Hi Christophe, On 6/4/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > .. > AND (latitude_DDS BETWEEN 44.261771 and 44.424779) You might want to check if you can somehow store this data *without* the decimal point (with the point implied - counting six digits from right to left) and use *integer* mathematical operators to do the maths. > 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 > .. > So i could do select statements like: > > select * from Level1 > Union > select * from Level2 > ... Perhaps he meant "partitioning" when he said "dividing" and he was coming from an Oracle background or something ??? This is because UNIONs are ALWAYS slow unless you are using a higher end RDBMS such as Oracle where you can take special measures to speed up union using the Oracle provided facilities. -- Best regards, Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
On Jun 4, 2008, at 7:13 AM, Derrell Lipman wrote: > On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp <[EMAIL PROTECTED]> > wrote: > >> >> 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 >> > > The README contains this text: > > CREATE VIRTUAL TABLE USING rtree() > >For example: > > CREATE VIRTUAL TABLE boxes USING rtree(boxno, xmin, xmax, ymin, > ymax); > CREATE VIRTUAL TABLE boxes USING rtree(1, 1.0, 3.0, 2.0, 4.0); > > Is that last line supposed to be > INSERT INTO boxes VALUES (1, 1.0, 3.0, 2.0, 4.0); > ? Yes. http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.3 > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
On Jun 4, 2008, at 7:06 AM, Christophe Leske wrote: > >> 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? > You can compile the R-Tree extension as a separate DLL, independently of SQLite. Then load the R-Tree extension at run-time. The new R- Tree extension will work with older versions of SQLite - you do not need to use the most recent code from CVS. (FWIW, we are getting ready to break the code in CVS as we move forward to version 3.6.0, so right now might not be the best time to recompile the core from CVS.) 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.) D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > 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 > The README contains this text: CREATE VIRTUAL TABLE USING rtree() For example: CREATE VIRTUAL TABLE boxes USING rtree(boxno, xmin, xmax, ymin, ymax); CREATE VIRTUAL TABLE boxes USING rtree(1, 1.0, 3.0, 2.0, 4.0); Is that last line supposed to be INSERT INTO boxes VALUES (1, 1.0, 3.0, 2.0, 4.0); ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
>I understand that BETWEEN gets translated to >= and =< (bigger or >equal, and small or equal). Some time ago I tested and observed different behaviour for BETWEEN in SQLite (sometime as >= and =<, sometime >= and <), so test it for yourself. I think it is better to use comparison signs (=,<,>) than BETWEEN. IMPORTANT! The BETWEEN...AND operator is treated differently in different databases: 1. BETWEEN..AND selects fields that are between and excluding the test values; 2. BETWEEN..AND selects fields that are between and including the test values; 3. BETWEEN..AND selects fields between the test values, including the first test value and excluding the last test value. ___ 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
Re: [sqlite] What is quicker?
On 6/4/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 4, 2008, at 6:53 AM, Christophe Leske wrote: > > > Hi, > > > > i am still fiddling around with my database and was wondering which > > kind > > of query would be 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. > So, I downloaded and built SQLite from CVS. I see R*Tree being configured and built. Is there are README (other than the one in ext/rtree) that tells how to create and rtree index? Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
On Jun 4, 2008, at 6:53 AM, Christophe Leske wrote: > Hi, > > i am still fiddling around with my database and was wondering which > kind > of query would be 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. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
