List:General Discussion« Previous MessageNext Message »
From:John Kopanas Date:November 23 2006 11:50pm
Subject:Performance Question And Problem
View as plain text  
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?

-- 
John Kopanas
john@stripped

http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
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