# Re: update statement with unacceptable performance

From: Kay Kanekowski <kay.kanekowski_at_web.de>

Date: Wed, 12 Jun 2013 01:21:27 -0700 (PDT)

Message-ID: <2abd320a-f47d-4e19-898c-da8c8dc77a8e_at_googlegroups.com>

Hallo Juerg,

do you know that you produce a cartesian product of 218.037 x 30.664 = 6.685.886.568 rows ? And you do it 3 times.

where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007 ;

Date: Wed, 12 Jun 2013 01:21:27 -0700 (PDT)

Message-ID: <2abd320a-f47d-4e19-898c-da8c8dc77a8e_at_googlegroups.com>

Hallo Juerg,

do you know that you produce a cartesian product of 218.037 x 30.664 = 6.685.886.568 rows ? And you do it 3 times.

As Charles said, you don't need sqrt(power(..))>0.

So i create a temp table like this:

create temp_x1_y1

as

select g.x1, g.y1, p.x1 as new_x1, p.y1 as new_y1 from point p, geometry g

where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007 ;

Ok, it is the cartesian product too, but only 1 time.

And with this i would try something like this
update geometry g

set

(x1, y1) = (select new_x1, new_y1 from temp_x1_y1 t where t.x1 = g.x1 and t.y1 = g.y1) where exists (select 1 from temp_x1_y1 t where t.x1 = g.x1 and t.y1 = g.y1);

hth

bis denn

Kay
Received on Wed Jun 12 2013 - 10:21:27 CEST