Re: Performance Question And Problem
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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Question And Problem
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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Performance Question And Problem
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? On 11/23/06, mos [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- John Kopanas [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
Re: Re: Performance Question And Problem
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- John Kopanas [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
Re: Re: Performance Question And Problem
At 10: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? No. Your approach was executing the subquery 2000 times for the 2000 records in your company file. And will run 500,000 times when you go to production data. Somebody with better math than I should try to project that. His prep query runs once, and his update query runs once. Scales very nicely. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
