List:General Discussion« Previous MessageNext Message »
From:mos Date:November 24 2006 4:06am
Subject:Re: Re: Performance Question And Problem
View as plain text  
At 09:47 PM 11/23/2006, John Kopanas wrote:
>That is awesome... thanks.  I still am not sure exactly though why
>this take 2 seconds while my methond took over a minute for the same
>amount of rows.  In essence don't the two methods do the same things?

The Group By executes in one operation. I may be wrong, but I think your 
SQL subselect has to be re-executed for each Company_Id that it finds. 
Joins are usually faster than a subselect and a memory table makes the 
joins faster.

BTW, if more than 1 person will be running this SQL at the same time, then 
you can use TEMPORARY table with MEMORY so each session gets their own 
memory table.

Glad it works.

Mike
(Please don't send me a service change, got enough of those already.<g>)


>On 11/23/06, mos <mos99@stripped> wrote:
>>At 05:50 PM 11/23/2006, you wrote:
>> >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
>>
>>John,
>>          I would split it into separate sql statements.
>>
>>1) Create a memory table of the totals:
>>
>>drop table if exists CompanyTotals;
>>create table CompanyTotals type=memory select Company_Id Id,
>>SUM(annual_service_charge) ServCharge
>>             FROM purchased_services ps group by Company_Id;
>>alter table CompanyTotals add index ix_Id (Id);
>>
>>2) Update the Companies table with the CompanyTotals:
>>update Companies C, CompanyTotals CT set
>>total_annual_service_charge=CT.ServCharge where C.Id=CT.Id;
>>
>>This should be much faster.
>>
>>Mike
>>
>>
>>
>> >http://www.kopanas.com
>> >http://www.cusec.net
>> >http://www.soen.info
>> >
>> >--
>> >MySQL General Mailing List
>> >For list archives: http://lists.mysql.com/mysql
>> >To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>
>
>--
>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