Subject Re: [ib-support] Selects and Updates based on sub-queries
>>>>> On Tue, 07 May 2002 11:57:47 -0000, "sean_brad" <sean_brad@...> said:

sb> Hi, I'm using values in a temporary table as a means to select
sb> rows from a main table, as in the example below:

sb> update Table1 set Flag = -1 where RecNumber in (select RecNo
sb> from Temp_Table)

sb> However when I run this query it does not seem to terminate,
sb> and the IBServer process processor usage goes up to 99% even
sb> when there are very few rows in the temporary table.

This is because the stmt above does not use an index to resolve the
where clause. It's the IN construct the culprit.

sb> Is there a better way to write this type of query or could
sb> there be something wrong with my Server setup?

I think the best approach, performance-wise, is to write a simple
stored proc that performs the same operation using a FOR SELECT loop.

hth, ciao, lele.
nickname: Lele Gaifax | Quando vivro' di quello che ho pensato ieri
real: Emanuele Gaifas | comincero' ad aver paura di chi mi copia.
email: lele@... | -- Fortunato Depero, 1929.