I am trying to figure out how best to optimize a rails view that I am serving from DreamHost. Development Log: Controler Load (18.165430)... SQL... Completed in 22.34315 (0 reqs/sec) | Rendering: 2.35181 (10%) | DB: 18.17923 (81%) | 200 OK [http://development.site.com/controller/action/ id] It appears that the performance issue is the SQL statement which takes 81% of the elapsed time. Elasped time within mysql via SSH: 146 rows in set (9.50 sec) Elasped time within phpMyAdmin sql tab: 146 total, Query took -0.9953 sec Why is phpMyAdmin so much faster? I did an explain on the SQL within mysql: +----+-------------+-------+-------+------------------+-------------- +---------+ --------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------+-------------- +---------+ --------------------------+------+---------------------------------+ | 1 | PRIMARY | po | const | PRIMARY,username | PRIMARY | 4 | const | 1 | Using temporary; Using filesort | | 1 | PRIMARY | sk | ref | portfolio_id | portfolio_id | 4 | const | 102 | Using where | | 1 | PRIMARY | sp | ref | symbol | symbol | 22 | ts_development.sk.symbol | 1 | | | 1 | PRIMARY | pe | ref | symbol,quotedate | symbol | 22 | ts_development.sk.symbol | 17 | | | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+-------+------------------+-------------- +---------+ --------------------------+------+---------------------------------+ 5 rows in set (0.02 sec) Any ideas on how to optimize this custom SQL which contains three left joins and a sub-select query, that is run via Controller.find_by_sql(@sql)? Thanks for any help, Asa --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Asa wrote:> I am trying to figure out how best to optimize a rails view that I am > serving from DreamHost. > > Development Log: > > Controler Load (18.165430)... SQL... > Completed in 22.34315 (0 reqs/sec) | Rendering: 2.35181 (10%) | DB: > 18.17923 (81%) | 200 OK [http://development.site.com/controller/action/ > id] > > It appears that the performance issue is the SQL statement which takes > 81% of the elapsed time. > > Elasped time within mysql via SSH: > 146 rows in set (9.50 sec) > > Elasped time within phpMyAdmin sql tab: > 146 total, Query took -0.9953 sec > > Why is phpMyAdmin so much faster? > > I did an explain on the SQL within mysql: > +----+-------------+-------+-------+------------------+-------------- > +---------+ > --------------------------+------+---------------------------------+ > | id | select_type | table | type | possible_keys | key | > key_len | > ref | rows | Extra | > +----+-------------+-------+-------+------------------+-------------- > +---------+ > --------------------------+------+---------------------------------+ > | 1 | PRIMARY | po | const | PRIMARY,username | PRIMARY | > 4 | > const | 1 | Using temporary; Using filesort | > | 1 | PRIMARY | sk | ref | portfolio_id | portfolio_id | > 4 | > const | 102 | Using where | > | 1 | PRIMARY | sp | ref | symbol | symbol | > 22 | > ts_development.sk.symbol | 1 | | > | 1 | PRIMARY | pe | ref | symbol,quotedate | symbol | > 22 | > ts_development.sk.symbol | 17 | | > | 2 | SUBQUERY | NULL | NULL | NULL | NULL | > NULL | > NULL | NULL | Select tables optimized away | > +----+-------------+-------+-------+------------------+-------------- > +---------+ > --------------------------+------+---------------------------------+ > 5 rows in set (0.02 sec) > > Any ideas on how to optimize this custom SQL which contains three left > joins and a sub-select query, that is run via > Controller.find_by_sql(@sql)?Without knowing the query(s) that Rails is executing it''s impossible to say. There could any number of other queries that are being executed in the view besides the one you are doing with find_by_sql since you haven''t shown us the view or controller so that one might not even be the one that''s taking most of the time. -- Michael Wang --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
You might want to check out the query_stats and [less so] query_trace plugins. They''ll give you a lot more info about the sql used than you''re providing right now. :) RSL On 3/13/07, Asa <bourea-PkbjNfxxIARBDgjK7y7TUQ@public.gmane.org> wrote:> > > I am trying to figure out how best to optimize a rails view that I am > serving from DreamHost. > > Development Log: > > Controler Load (18.165430)... SQL... > Completed in 22.34315 (0 reqs/sec) | Rendering: 2.35181 (10%) | DB: > 18.17923 (81%) | 200 OK [http://development.site.com/controller/action/ > id] > > It appears that the performance issue is the SQL statement which takes > 81% of the elapsed time. > > Elasped time within mysql via SSH: > 146 rows in set (9.50 sec) > > Elasped time within phpMyAdmin sql tab: > 146 total, Query took -0.9953 sec > > Why is phpMyAdmin so much faster? > > I did an explain on the SQL within mysql: > +----+-------------+-------+-------+------------------+-------------- > +---------+ > --------------------------+------+---------------------------------+ > | id | select_type | table | type | possible_keys | key | > key_len | > ref | rows | Extra | > +----+-------------+-------+-------+------------------+-------------- > +---------+ > --------------------------+------+---------------------------------+ > | 1 | PRIMARY | po | const | PRIMARY,username | PRIMARY | > 4 | > const | 1 | Using temporary; Using filesort | > | 1 | PRIMARY | sk | ref | portfolio_id | portfolio_id | > 4 | > const | 102 | Using where | > | 1 | PRIMARY | sp | ref | symbol | symbol | > 22 | > ts_development.sk.symbol | 1 | | > | 1 | PRIMARY | pe | ref | symbol,quotedate | symbol | > 22 | > ts_development.sk.symbol | 17 | | > | 2 | SUBQUERY | NULL | NULL | NULL | NULL | > NULL | > NULL | NULL | Select tables optimized away | > +----+-------------+-------+-------+------------------+-------------- > +---------+ > --------------------------+------+---------------------------------+ > 5 rows in set (0.02 sec) > > Any ideas on how to optimize this custom SQL which contains three left > joins and a sub-select query, that is run via > Controller.find_by_sql(@sql)? > > Thanks for any help, > Asa > > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---