kiryl hakhovich Thu Mar 20 17:16:24 -0400 2014

Subject: interaction with DB

hey guys,

i may be doing something wrong, we have used active-record framework to build a part in our tool that allow us create specific records and manipulate them.
It seems easy and works well with one exception, at some point when we have x-amount of records, resulting page become slower and slower with every new record, i did a debugging here and here is what i found:

- when we load a page with records, php generate a lot of select *, totaling with 5674 selects
cat /usr/local/mysql/var/general-query.log |grep "fields" -wc
5674

and they look like this:
460184 Query SELECT * FROM `fields` WHERE `id`='103' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1
460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1

Obviously my approach to this is incorrect and i applied this framework in an ass-backwards way.

can you help with any pointers?

the goal that we are looking for is to have rows or records, where each column is a drop-down list with 10-30 records, and row has up2 10 of the columns.

Is there a better way for me to utilize this framework to achieve this ?

thanks!!


kiryl hakhovich Thu Mar 20 17:35:05 -0400 2014

Update: i did a little more debugging, and this seems NOT related to activerecord, rather tied into another famework which is a part of process and is called slim.

Apologize for pointing finger at activerecord originally :)

João Mário Nedeff Menegaz Tue Mar 25 09:07:36 -0400 2014

kiryl hakhovich wrote:

hey guys,

i may be doing something wrong, we have used active-record framework to build a part in our tool that allow us create specific records and manipulate them. It seems easy and works well with one exception, at some point when we have x-amount of records, resulting page become slower and slower with every new record, i did a debugging here and here is what i found:

- when we load a page with records, php generate a lot of select *, totaling with 5674 selects cat /usr/local/mysql/var/general-query.log |grep "fields" -wc 5674

and they look like this: 460184 Query SELECT * FROM `fields` WHERE `id`='103' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1 460184 Query SELECT * FROM `fields` WHERE `id`='95' LIMIT 0,1

Obviously my approach to this is incorrect and i applied this framework in an ass-backwards way.

can you help with any pointers?

the goal that we are looking for is to have rows or records, where each column is a drop-down list with 10-30 records, and row has up2 10 of the columns.

Is there a better way for me to utilize this framework to achieve this ?

thanks!!

Take a look at this link:

http://www.phpactiverecord.org/projects/main/wiki/Finders#eager-loading

(1-2/2)