Re: update statement with unacceptable performance

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 11 Jun 2013 11:04:50 -0700 (PDT)
Message-ID: <e05a80d0-d985-4755-9fee-0639b24504be_at_googlegroups.com>

On Tuesday, June 11, 2013 10:50:55 AM UTC-4, Juerg wrote:
> hello
>
> I have to update (snap) the coordinates of a first table (geometry) to the cordinates of a second table (point) if the difference is > 0 and < 0.0007 meter. I wrote the following statements:
>
> SQL> select count(*) from geometry
>
>
>
> COUNT(*)
>
> --------
>
> 218037
>
> 1 Zeilen ausgewählt.
>
>
>
> SQL> select count(*) from point
>
>
>
> COUNT(*)
>
> --------
>
> 30664
>
> 1 Zeilen ausgewählt.
>
>
>
> update geometry g
>
> set
>
> y1 = (
>
> select y1 from point p
>
> where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0
>
> and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007
>
> ),
>
> x1 = (
>
> select x1 from point p
>
> where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0
>
> and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007
>
> )
>
> where exists (
>
> select 1 from point p
>
> where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0
>
> and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007
>
> );
>
>
>
> Well, the update took 9 hours (Oracle Enterprise Edition 10.2.0.3). In this data model oracle spatial option is not avaiable, I have to do it without this extension. There are indexes on y1 and x1 on both tables, but I suppose they are not used.
>
>
>
> Any idea to solve this slowness? Thank in advance
>
>
>
> kind regards
>
>
>
> Juerg

My first thought is to give Oracle Database an easier math problem to solve before forcing it to calculate the Pythagorean theorem. Doing so should help eliminate a large number of rows before having to calculating the two squares and a square root. The concept is found at the end of this article: http://hoopercharles.wordpress.com/2011/06/13/calculate-the-distance-between-two-latitudelongitude-points-using-plain-sql/

Assume that g.y1-p.y1 is 0 - then that means the only acceptable values for g.x1-p.x1 are greater than -0.0007 and less than 0.0007 - those would always be the maximum extremes. Assume that g.x1-p.x1 is 0 - then that means the only acceptable values for g.y1-p.y1 are greater than -0.0007 and less than 0.0007. As such, you should be able to add the following to each of the WHERE clauses (you might also try switching the g and p alias in the following):   AND g.x1 BETWEEN (-0.0007 + p.x1) AND (0.0007 + p.x1)   AND g.y1 BETWEEN (-0.0007 + p.y1) AND (0.0007 + p.y1)

update geometry g
set

   y1 = (

      select y1 from point p 
      where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0 
        and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007 
        AND g.x1 BETWEEN (-0.0007 + p.x1) AND (0.0007 + p.x1)
       AND g.y1 BETWEEN (-0.0007 + p.y1) AND (0.0007 + p.y1)

   ),
   x1 = (

      select x1 from point p 
      where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0 
        and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007 
        AND g.x1 BETWEEN (-0.0007 + p.x1) AND (0.0007 + p.x1)
        AND g.y1 BETWEEN (-0.0007 + p.y1) AND (0.0007 + p.y1)

   )
where exists (

   select 1 from point p
   where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0

     and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007 
     AND g.x1 BETWEEN (-0.0007 + p.x1) AND (0.0007 + p.x1)
     AND g.y1 BETWEEN (-0.0007 + p.y1) AND (0.0007 + p.y1)

);

I wonder if an updateable inline view is acceptable here (see http://jonathanlewis.wordpress.com/2008/12/19/updatable-join-views/ for the concept) - that way the sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) would not need to be calculated so many times.

This check in your SQL statement is unnecessary, unless you are working with imaginary numbers (or you are verifying that the two sets of coordinates are not identical) - squaring a number always produces a positive number, so you are adding 2 positive numbers to see if the result is greater than 0 (a positive number): sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0

The above could simplified as:
  AND (g.x1<>p.x1 OR g.y1<>p.y1)

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jun 11 2013 - 20:04:50 CEST