> 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 optimization tips are highly welcome.
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 have time to try it (sorry). 1) A function which turns a numeric IP address or a block into some standard easy-to-process representation in string form. Possibly a pair of strings with the first string being an address the second being something indicating the extent of the block, perhaps something like '2001:0db8:8500:0000:0000:0000:0000:0000vffff:ffff:ff00:0000:0000:0000:0000:0000'. You could make it shorter by leaving out the colons but my experience is that although this leads to less data stored on disk it doesn't speed up processing by much. But if you have a great deal of data you might want to do it anyway. 2) A comparator function (maybe a SQLite function, maybe not) which takes two such addresses or blocks and returns a value indicating whether they're identical, whether block A contains block or address B, or neither. The closest I got to the above was when I needed a program which intensively searched and sorted individual IPv4 addresses. I got best results by defining a SQLite function which converted IP addresses of all formats into 'standard format' where each byte was two hex digits. All values stored in the database were stored in my 'standard' format. This allowed easy collation using standard text sorting. Everything else turned out faster to implement in my own programming language than it was to build as SQLite functions. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
