At 09:24 PM 19/04/2007, you wrote:

>Hi,
>(using Firebird 1.5.3 on WindowsXP)
>
>if i try this update:
>update tl_neu_ku
> set pnr_id = 38, p_nr = 'USER23'
>where ku_nr in (select u.ku_nr from tl_neu_ku u, tl_neu_dat d where
>d.ku_nr = u.ku_nr and d.bezirksnr = '128034')
>
>it takes 8 minutes and more.
>
>I also tried to use EXISTS:
>
>update tl_neu_ku u
> set pnr_id = 38, p_nr = 'USER23'
>where exists (select 1 from tl_neu_dat d where u.ku_nr = d.ku_nr and
>d.bezirksnr = '128034')
>Result:
>then i update all the data in table tl_neu_ku (7.000 records) instead
>of 167 records, mhm

These two statements are not equivalent. The use of the implicit
join in the first one is causing the performance hit - you don't
scramble up a correlated subquery with an attempt to join back to the
correlating table. Horrendous things are going on back there while
that statement is thrashing away.

So, yes, the first statement needed fixing and using exists() is
about the best way.

Make sure you qualify the columns you're updating:

update tl_neu_ku u
set u.pnr_id = 38, u.p_nr = 'USER23'
where exists (select 1 from tl_neu_dat d where u.ku_nr = d.ku_nr and
d.bezirksnr = '128034')

FWIW, this statement does exactly the same behind the scenes:

update tl_neu_ku u
set u.pnr_id = 38, u.p_nr = 'USER23'
where u.ku_nr in (select d.ku_nr from tl_neu_dat d
where d.bezirksnr = '128034')

Are these searched columns indexed?

./h