Re: [sqlite] slowish R*Tree performance

On Wed, Jun 18, 2014 at 3:00 PM, Eric Rubin-Smith wrote: > Cory Nelson wrote: > > > Expand the prefix into the full feed:beef::etc > > > > Insert into a table (start binary(16), mask_length int) > > > > select top 1 binary,length from table where start <= @input order by >

Re: [sqlite] slowish R*Tree performance

Cory Nelson wrote: > Expand the prefix into the full feed:beef::etc > > Insert into a table (start binary(16), mask_length int) > > select top 1 binary,length from table where start <= @input order by > binary desc > > Check if the row is inside the range returned. This will take a

Re: [sqlite] slowish R*Tree performance

Cory Nelson wrote: > The phrase you're looking for here is "CIDR block". Well, I was avoiding the phrase on purpose :-). I was worried that using another bit of jargon -- one that is even more opaque than "prefix" to someone unfamiliar with the space -- did not seem likely to help get the

Re: [sqlite] slowish R*Tree performance

Carlos Ferreira wrote: > Regarding the R.Tree performance problem, > > What is the original problem that is causing slow performance in the > SQlite R-Tree implementation? I was populating my DB with bad data. In particular, I was first choosing a random prefix length, then filling up

Re: [sqlite] slowish R*Tree performance

On Wed, Jun 18, 2014 at 12:18 PM, Eric Rubin-Smith wrote: > Carlos Ferreira wrote: > > > 1 - There a data type named IPV6 Address. 2 - there is a table where > > this data type must be in. ( can be a set of fields, one blob, one > string > > ...) > > > > You want to: > > > >

Re: [sqlite] slowish R*Tree performance

Simon Slavin wrote: > Strongly suspect that although R*Trees produce an elegant solution to > your problem, the fact that they're a general case tool will make them too > slow to use for something like this. > > I propose an alternative solution, though I have not tried it and do not >

Re: [sqlite] slowish R*Tree performance

Carlos Ferreira wrote: > 1 - There a data type named IPV6 Address. 2 - there is a table where > this data type must be in. ( can be a set of fields, one blob, one string > ...) > > You want to: > > Given a certain IPV6, find in the database the existent IPV6 record with > the longest

Re: [sqlite] slowish R*Tree performance

...@sqlite.org] On Behalf Of Eric Rubin-Smith Sent: domingo, 15 de Junho de 2014 05:25 To: General Discussion of SQLite Database Subject: [sqlite] slowish R*Tree performance I am exploring a mechanism for finding the longest covering IPv6 prefix for a given IPv6 address by leveraging SQLite 3.8.5's R*Tree

Re: [sqlite] slowish R*Tree performance

On 16 Jun 2014, at 10:47am, Carlos Ferreira wrote: > What is the original problem that is causing slow performance in the SQlite > R-Tree implementation? Read earlier posts to this thread. Simon. ___ sqlite-users mailing list

Re: [sqlite] slowish R*Tree performance

: domingo, 15 de Junho de 2014 23:27 To: General Discussion of SQLite Database Subject: Re: [sqlite] slowish R*Tree performance > On 15 Jun 2014, at 5:21pm, Eric Rubin-Smith <eas@gmail.com> wrote: > > still not good enough for my use case > (unfortunately). Any further

Re: [sqlite] slowish R*Tree performance

> On 15 Jun 2014, at 5:21pm, Eric Rubin-Smith wrote: > > still not good enough for my use case > (unfortunately). Any further optimization tips are highly welcome. Strongly suspect that although R*Trees produce an elegant solution to your problem, the fact that they're a

Re: [sqlite] slowish R*Tree performance

On Sun, Jun 15, 2014 at 9:47 AM, Eric Rubin-Smith wrote: > Richard Hipp wrote: > > > What does this query return? > > > > SELECT count(*) FROM ipIndex > > WHERE minD1 <= 1220818432 and 1220818432 <= maxD1 > > AND minD2 <= 2120561472 and 2120561472 <= maxD2 >

Re: [sqlite] slowish R*Tree performance

Richard Hipp wrote: > What does this query return? > > SELECT count(*) FROM ipIndex > WHERE minD1 <= 1220818432 and 1220818432 <= maxD1 > AND minD2 <= 2120561472 and 2120561472 <= maxD2 > AND minD3 <= 1685398080 and 1685398080 <= maxD3 > AND minD4 <= 1685755328

Re: [sqlite] slowish R*Tree performance

On Sun, Jun 15, 2014 at 12:25 AM, Eric Rubin-Smith wrote: > > sqlite> explain query plan SELECT prefix, target FROM routeTarget WHERE id > = ( >...>SELECT id FROM ipIndex >...> WHERE minD1 <= 1220818432 and 1220818432 <= maxD1 >...> AND minD2 <=

[sqlite] slowish R*Tree performance

I am exploring a mechanism for finding the longest covering IPv6 prefix for a given IPv6 address by leveraging SQLite 3.8.5's R*Tree feature. I'm getting pretty bad performance and would like to know if I'm doing something obviously wrong. A 1-dimensional R*Tree of integers of width 128 bits