--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> 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?
>

the ku_nr fields are indexed (primary key), the d.bzirksnr not.
there are a lot of different fields in the dat, depenting of
an project, makes no sense to index them.

so there its no way to make this faster, thanks Helen.

br
Gerhard