Re: Update too slow !!!
You may want to try;
update statistics for table t1then your statement
Hung Phi wrote:
> Hi
>
> I have two tables (t1, t2)
> t1 t2
> id recid
> col1
> col2
> t1.id is indexed primary key. It has 1,000,000 rows.
> t2.recid is indexed primary key. It has 50 rows.
> I have tried several queries below. They all take more than 3 minutes
> to update 50 rows in table t1.
> update t1
> set col1 = 'A',
> col2 = 0
> where t1.id in (select recid from t2)
> update t1
> set col1 = 'A',
> col2 = 0
> where exists (select recid from t2
> where recid = t1.id)
> update t1
> set col1 = 'A',
> col2 = 0
> where exists (select 'a' from t2
> where recid = t1.id)
> update t1
> set col1 = 'A',
> col2 = 0
> where t1.id = (select recid from t2
> where recid = t1.id)
> update t1
> set col1 = 'A',
> col2 = 0
> where t1.id IN (select recid from t2
> where recid = t1.id)
>
> It has to scan 1,000,000 rows in t1 table.
> How can I improve this Update query ?
> How can I let it scan the t2 instead of t1 table ?
>
> My machine is HP5900, 2 CPU, 1G of memory.
> DBMS is Informix 7.3.