Re: Correlated update

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sun, 05 Dec 2010 08:57:38 +0100
Message-ID: <4cfb4584$0$14251$ba620e4c_at_news.skynet.be>

bob123 schreef:
> Hi,
>
> I have the following problem
> The update is very long (more than 4 hours)
> table T_SUBJECTVISITFORMITEMS is 3M rows
>
> How can I do that more quickly ?
>
> Thanks in advance
>
> CREATE TABLE T_ITEMFIRSTDATA_STATUS_ICLD
> AS
> SELECT MIN (ITEMFIRSTDATA) ITEMFIRSTDATA ,
> SUBJECTID,
> VISITID,
> DB_SOURCE
> FROM T_SUBJECTVISITFORMITEMS
> WHERE FORMMNEMONIC = 'STATU' GROUP BY SUBJECTID,VISITID,FORMID,DB_SOURCE;
>
> ALTER TABLE T_SUBJECTVISITFORMITEMS ADD ITEMFIRSTDATA_STATU DATE;
>
> UPDATE T_SUBJECTVISITFORMITEMS B SET ITEMFIRSTDATA_STATU = (SELECT
> DISTINCT ITEMFIRSTDATA FROM T_ITEMFIRSTDATA_STATUS_ICLD A WHERE
> A.SUBJECTID=B.SUBJECTID
> AND A.VISITID=B.VISITID
> AND A.DB_SOURCE= B.DB_SOURCE);
>
>
>
>

There's something fishy about the above: in the create table you group by "SUBJECTID,VISITID,FORMID,DB_SOURCE", but when querying, you only specify SUBJECTID,VISITID and DB_SOURCE. So I would expect errors during the update (select may return more than one row).

If you want to speed this up, don't use T_ITEMFIRSTDATA_STATUS_ICLD. Write a procedure so the update can be spread over multiple transactions. Put an index on SUBJECTID,VISITID,FORMID,DB_SOURCE. Received on Sun Dec 05 2010 - 01:57:38 CST