Geographical indexing

[Ruslan Zasukhin]

On 4/19/09 11:14 AM, "Thomas Flemming" <tf at ttqv.com> wrote:

> So my select is for example:
> 
> (select obj_id from tableidx_1 where not ((ext_north < 48903994) Or
>                                            (ext_south > 49146819) or
>                                            (ext_east  < 8998828)  or
>                                            (ext_west  > 9366460)))
> union
> (select obj_id from tableidx_2 where not ((ext_north < 48903994) Or
>                                            (ext_south > 49146819) or
>                                            (ext_east  < 8998828)  or
>                                            (ext_west  > 9366460)))
> ...
> 
> This gives me all object_ids which are not outside of the requested area.
> Its fast, but since it has to be queried very often while mapdetail changes it
> is now the bottleneck.
> 
> Do you see another way? Is there a chance to improve this?

First of all you should try to sue Binding.


(select obj_id from tableidx_1 where not ((ext_north < :1) Or
                                          (ext_south > :2) or
                                          (ext_east  < :3)  or
                                          (ext_west  > :4)) )
union
(select obj_id from tableidx_2 where not ((ext_north < :1) Or
                                          (ext_south > :2) or
                                          (ext_east  < :3)  or
                                          (ext_west  > :4)) )

When you use Bidning, you get the same form of query, you see?
So parsed tree is cached in the query pool.

This may dramatically improve.
If lucky up to x20 times





-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]