Topic: Improve performance of my app

Hi,

I'm developing a planning application. It's my first app with rails. The goal is to view if in a day a person or compagny have freetime. For each day, I use a helper who search in the database if there there is freetime. I display a calendar for each month. When I show my log/development.log, I can show near 60 request to database (2 request/month). To display the calendar (a month) I have to wait 20 SECONDS!!! I think i'm doing to much request what do you think?

Does I have to only 1 request for first_day of the month to last_day of the month and then process on the server to retrieve the data that I want? What can I do to improve the performance?

Thanks!!

Re: Improve performance of my app

Look into eager loading (:include) and indexing your SQL. If neither of them work then you need to rethink your database structure and how things work.

For more advice post the whole log entry for the request and some code.

Re: Improve performance of my app

I already index my SQL and I don't think eager loading can help, when I do a request, I retrieve not more than 10 results.

Here my development.log :

Processing PlanningController#index (for 127.0.0.1 at 2007-05-19 13:13:32) [GET]
  Session ID: 1509139cb1af2e092bb19e157df60550
  Parameters: {"action"=>"index", "controller"=>"planning"}
Rendering planning/index
  SQL (0.044087)   SET SQL_AUTO_IS_NULL=0
  WorkCompagny Load (0.067078)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.067301)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 18 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Columns (0.056589)   SHOW FIELDS FROM work_compagny
  SQL (0.042769)   SET SQL_AUTO_IS_NULL=0
  Charge Columns (0.083527)   SHOW FIELDS FROM charges
  Charge Load (0.147465)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-01 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.142072)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-01 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.138716)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-01 00:00:00' AND charges.`id_emp` = 3) 
  WorkCompagny Load (0.067160)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.066798)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 18 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.131421)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-02 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.119922)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-02 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.119645)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-02 00:00:00' AND charges.`id_emp` = 28) 
  WorkCompagny Load (0.065394)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.069978)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 18 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.123445)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-03 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.120343)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-03 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.064600)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.067474)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 18 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.146345)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-04 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.127828)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-04 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.120481)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-04 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.064296)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.066134)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 18 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.140207)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-05 00:00:00' AND charges.`id_emp` = 3) 
  WorkCompagny Load (0.065983)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.067923)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 18 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.065950)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.065654)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 19 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.125452)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-07 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.123308)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-07 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.131046)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-07 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.130411)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-07 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.065220)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.063680)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 19 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.127267)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-08 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.127170)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-08 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.069381)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.067920)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 19 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.123390)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-09 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.122821)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-09 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.064662)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.066550)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 19 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.129933)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-10 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.124460)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-10 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.062835)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.065029)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 19 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.123641)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-11 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.138037)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-11 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.065437)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.066179)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 19 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.132769)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-12 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.119859)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-12 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.128340)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-12 00:00:00' AND charges.`id_emp` = 28) 
  WorkCompagny Load (0.065073)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.063284)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 19 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.065634)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.065319)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 20 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.125352)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-14 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.117517)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-14 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.065626)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.062485)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 20 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.119053)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-15 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.123668)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-15 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.506210)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-15 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.070961)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.080861)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 20 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.423685)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-16 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.135641)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-16 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.064286)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.066576)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 20 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.118409)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-17 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.117676)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-17 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.067145)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.067831)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 20 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.139973)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-18 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.119018)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-18 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.065497)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.074217)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 20 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.119036)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-19 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.121804)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-19 00:00:00' AND charges.`id_emp` = 1) 
  WorkCompagny Load (0.064543)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.068554)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 20 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.064457)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.064259)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 21 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.117943)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-21 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.128027)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-21 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.118550)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-21 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.122498)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-21 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.118139)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-21 00:00:00' AND charges.`id_emp` = 28) 
  WorkCompagny Load (0.063708)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.063091)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 21 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.120385)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-22 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.121652)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-22 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.118288)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-22 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.063873)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.063795)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 21 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.117486)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-23 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.118537)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-23 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.118623)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-23 00:00:00' AND charges.`id_emp` = 28) 
  WorkCompagny Load (0.065560)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.062778)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 21 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.121015)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-24 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.119180)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-24 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.116975)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-24 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.063482)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.065393)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 21 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.119084)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-25 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.118769)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-25 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.121712)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-25 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.072525)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.067925)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 21 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.119784)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-26 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.124621)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-26 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.118602)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-26 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.120973)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-26 00:00:00' AND charges.`id_emp` = 28) 
  WorkCompagny Load (0.064277)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.066604)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 21 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.064691)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.064108)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 22 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.117002)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-28 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.125388)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-28 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.118277)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-28 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.119001)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-28 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.117599)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-28 00:00:00' AND charges.`id_emp` = 28) 
  WorkCompagny Load (0.064275)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.066987)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 22 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.119612)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-29 00:00:00' AND charges.`id_emp` = 4) 
  Charge Load (0.116873)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-29 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.117552)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-29 00:00:00' AND charges.`id_emp` = 18) 
  WorkCompagny Load (0.064240)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.064578)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 22 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.118171)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-30 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.118384)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-30 00:00:00' AND charges.`id_emp` = 18) 
  Charge Load (0.122210)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-30 00:00:00' AND charges.`id_emp` = 28) 
  WorkCompagny Load (0.069529)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 0 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  WorkCompagny Load (0.064540)   SELECT * FROM work_compagny WHERE (work_compagny.`week` = 22 AND work_compagny.`id_emp` IN (1,3,4,18,28) AND work_compagny.`id_comp` = 1) 
  Charge Load (0.121902)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-31 00:00:00' AND charges.`id_emp` = 3) 
  Charge Load (0.117742)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-31 00:00:00' AND charges.`id_emp` = 1) 
  Charge Load (0.117562)   SELECT * FROM charges WHERE (charges.`date` = '2007-05-31 00:00:00' AND charges.`id_emp` = 18) 
Completed in 15.11947 (0 reqs/sec) | Rendering: 15.11015 (99%) | DB: 0.00000 (0%) | 200 OK [http://localhost/planning]

What do you think? I can't change the schema of the DB because I use an external DB on read only. If it help you, I have three tables :

Charges (for schedule an event) :

id, id_emp, start_hour, finish_hour, date

Work_compagny (define who work for a week and when)

id, id_emp, week, mon_mo (monday morning), mon_an (monday afternoon), thu_mo, thu_an, ... sun_mo, sun_an

And that's all!!!

Whene I retrieve the data, I use algorithm who search if there is freetime.

Here is my helper :

def findFreeTime(date)

@convHalfDay = {
      0 => ["SUN_MO","SUN_AN"],
      1 => ["MON_MA","MON_AN"],
      ...
      6 => ["SAT_MO","SAT_AN"]
    }
 

    @employesDef = WorkCompagny.find(:all, :conditions => {:week => 0, :id_emp => [1,3,4,18,28], :id_comp => 1})

    @workEmp = [] #(who work)
    @wday = @convHalfDay[date.wday]

    @employesDef.each do |e|
      if e[@wday.first] == 1 or e[@wday.last] == 1
        @workEmp.push(e.ID_EMPLOYE)
      end
    end

    @employes = @workEmp
   
    @charge = {}

    @chargeSorted = {}
    @chargeSorted[date] = {}

    @employes.each do |e|
     
      @charge[e] = Charge.find_all_by_id_emp_and_date(e,date)
     
      @charge[e].each do |c|
        if @chargeSorted[date][e].nil?
          @chargeSorted[date][e] = []
        end
        @chargeSorted[date][e].push([c.start_hour, c.finish_hour])
      end   
    end
   

@freeTime = {} # define free time for eeach employe per date  (true => freetime, false no freetime)
    @freeTime[date] = {}
    @startTime = Time.local(2000,1,1,8,30)
    @finishTime = Time.local(2000,1,1,19,00)
   
   
    @chargeSorted[date].each_key do |e|
      @chargeSorted[date][e].sort
     
      unless @chargeSorted[date][e].first.include?(@startTime)
        @freeTime[date][e] = true
        break
      end
     
      @chargeSorted[date][e].each_with_index do |period,index|
       
        if index < @chargeSorted[date][e].size-1
          if period.last == @chargeSorted[date][e][index+1].first
            if @chargeSorted[date][e][index+1].last == @finishTime
              @freeTime[date][e] = false
            else
              @freeTime[date][e] = true
            end
          else
            @freeTime[date][e] = true
            break
          end
        end     
      end
    end
   
    @freeTime2 = {} # Define if there is freetime for a day
   
    @freeTime[date].each_key do |e|
      if @freeTime[date][e]
        @freeTime2[date] = true
        break
      else
        @freeTime2[date] = false
      end
    end
   
    if @interrr[date].size == 0
      @freeTime2[date] = true
    end
end


When I produce my calendar I use findFreeTime(date) for each date, then if there is freetime I add the ID freeTime for the case of the date and if there isn't freetime I putt the ID noFreeTime. I have a stylesheet who define theses id. If there is freetime the case of the date is green and if there isn't freetime the case is red. That's pretty simple I think.

What do you think?

Last edited by illmonkey (2007-05-19 08:12:51)

Re: Improve performance of my app

Well I'd suggest getting all your DB data into an array and sorting it using Ruby/Rails rather than the DB. IMO Running that many queries per request would never been practical in a production environment.

And you seem to have confused the stats wink

# Completed in 15.11947 (0 reqs/sec) | Rendering: 15.11015 (99%) | DB: 0.00000 (0%) | 200 OK [http://localhost/planning]

Re: Improve performance of my app

Well I'd suggest getting all your DB data into an array and sorting it using Ruby/Rails rather than the DB. IMO Running that many queries per request would never been practical in a production environment.

Could you give some helpfull helpers?

And you seem to have confused the stats wink

As I show the DB load is very low? Does it right?

Re: Improve performance of my app

If you are in development mode try turning on the caches.config.action_controller.perform_caching             = true
config.action_view.cache_template_extensions         = true are in the environments folder and in development.rb . In addition, if you do not need to see the sql fly by then use mongrel_rails start. I used these two items and I got alot faster.  The only caveat is that you have to control-C alot when making programming changes. I just like the speed more than the info. Hope this helps. Oh, also turn debug level to :info. It's about half way down the environment.rb file and commented out. I usually just do a copy paste with my change and leave the commented out version in-tact for future what the heck did I do.

Re: Improve performance of my app

Did you have a look at transactions?  Caching is a really good idea though in case your data does not change very often.

Last edited by rudionrails (2007-06-21 14:53:58)