List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 24 2006 3:39am
Subject:Re: Performance Question And Problem
View as plain text  
In the last episode (Nov 23), John Kopanas said:
> I have the following query:
> 
>      UPDATE companies c
>      SET
>        total_annual_service_charge =
>          (
>            SELECT SUM(annual_service_charge)
>            FROM purchased_services ps WHERE ps.company_id = c.id
>          );
> 
> 
> It takes 1s to run when I have two tables of 500 rows, 4s with two
> tables of 1000 rows, 15s to run with two tables to run with 2000
> rows, 90s for two tables of 5000 rows.  This is ridiculous.  And I
> need to run it on two tables of approx. 500,000 rows.  I need a
> better solution.
> 
> And there is an index on ps.company_id and c.id.  Any suggestions on
> how I can improve my query?

If you're I/O bound during this query, try an index on
(company_id,annual_service_charge) on your purchased_services table. 
That'll let the subquery complete using just an index scan.  If that
doesn't help, try mos's idea.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Performance Question And ProblemJohn Kopanas24 Nov
  • Re: Performance Question And Problemmos24 Nov
    • Re: Re: Performance Question And ProblemJohn Kopanas24 Nov
      • Re: Re: Performance Question And Problemmos24 Nov
      • Re: Re: Performance Question And ProblemBarry Newton24 Nov
  • Re: Performance Question And ProblemDan Nelson24 Nov