Slow performance using WHERE EXISTS to update large number of rows
The following query takes several minutes
to update about 5000 rows. It seems like
there should be a faster way. Note that the
table being updated has a 2-column primary key.
update ss_service set
billed_thru = ?,
last_invoice = (select invoice from ar_invoice
where custid = ss_service.custid and date = ?)
where exists (select service from invoice_temp
where service = ss_service.service
and version = ss_service.version);
Is there a better way?
Thanks,
Jeff
Jeff Larsen wrote:
>
> The following query takes several minutes
> to update about 5000 rows. It seems like
> there should be a faster way. Note that the
> table being updated has a 2-column primary key.
>
> update ss_service set
> billed_thru = ?,
> last_invoice = (select invoice from ar_invoice
> where custid = ss_service.custid and date = ?)
> where exists (select service from invoice_temp
> where service = ss_service.service
> and version = ss_service.version);
Do you have the indexes on ar_invoice.custid and invoice_temp.service
to support the subqueries? What does set explain say about whether
they are being used? You are obviously using ESQL/C or 4GL how about
making that second subquery a separate join to ss_service with a FOR
UPDATE OF clause and update in a loop fetching the service numbers?
Art S. Kagel
Jeff Larsen wrote:
> The following query takes several minutes
> to update about 5000 rows. It seems like
> there should be a faster way. Note that the
> table being updated has a 2-column primary key.
>
> update ss_service set
> billed_thru = ?,
> last_invoice = (select invoice from ar_invoice
> where custid = ss_service.custid and date = ?)
> where exists (select service from invoice_temp
> where service = ss_service.service
> and version = ss_service.version);>
> Is there a better way?
>
> Thanks,
>
> Jeff
Hi Jeff,
how about this ?
begin work;
create procedure wow( sbilled_thru date, sdate date )define ss datatype_of_service;
define sv datatype_of_version;
foreach select service, version INTO ss, sv from invoice_temp a,
ss_service
where a.service = ss_service.service
and a.version = ss_service.version
update ss_service set billed_thru = sbilled_thru,
last_invoice = (select invoice from ar_invoice
where custid = ss_service.custid and date = sdate )
where service = ss and version = sv;
end foreach;
end procedure;
execute procedure wow( ?, ? );
drop procedure wow;commit work;
Possibily I must rewrite the procedure because you didn't tell us the
column-names
of your primary key. I think your primary keys are: version and service
Don't worry about the procedue. The statements can be prepared in a
single prepare, it's an
easy multiple statement prepare.