rtrauer
Member
Dear Fabrik Support Team,
We are using Fabrik on our website for a couple years with no problems, and we are growing in user accounts and data that is stored.
One of our application build with fabrik is for home-automation, where we generate a customized template, for smartphone or tablet, that is generated based on the choice of the manufacturer and model of TV, BD, Receiver, Air Conditioning, and other typical home appliances controlled by IR codes.
Everything was doing fine, until the database is becoming larger, with more customers and appliances models being added to our database.
But the numbers of records in the DB are relatively small, and we are getting response time from the server over 15 seconds to render a list or to edit a stored configuration (one record in the fb_ir_template_config table).
To separate this application from the joomla website, all template-generator related tables are stored in a separate database, that contains the following tables:
fb_ir_template_config - stores the configuration options of the template - 389 records
fb_ir_model - stores the manufacturer, device model of the IR database for each appliance - 176 records
fb_ir_codes - stores the IR commands for each appliance model - 3642 records
cb_ir_manufacturers - stores the name of appliance manufacturers - 63 records
fb_ir_tv_chanels - stores cable TV channels and image file - 120 records
... others
The Search query that populates the form is huge, due to the database join elements that we use and is causing a long response time from the server, reaching 20 to 26 seconds to process this single query.
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fb_ir_template_config`.`id` AS `fb_ir_template_config___id`, `fb_ir_template_config`.`id` AS `fb_ir_template_config___id_raw`, `fb_ir_template_config`.`ir_config_tv_manuf` AS `fb_ir_template_config___ir_config_tv_manuf_raw`, `fb_ir_model_4`.`manufacturer` AS `fb_ir_template_config___ir_config_tv_manuf`, `fb_ir_template_config`.`ir_config_tv_model` AS `fb_ir_template_config___ir_config_tv_model_raw`,
...
a long long list
...
`fb_ir_template_config`.`id` = '306'AND (( fb_ir_template_config.user_id = '1045' )) ORDER BY `fb_ir_template_config`.`id` ASC
This query takes 20.8 sec to process using PHPMyAdmin to access the MySQL server directly.
Yesterday, this query was generating alerts in our ISP with more than 1000 seconds processing time.
This was improved by optimizing all MySQL tables and by setting some elements to include in list query to yes.
I noticed that when this option is set to NO then the list is generated much faster, but the form editing gets slower. I don?t know how I can optimize this further.
I found out that a much better result could be obtained with a 2 modifications
eliminating the 'DISTICT' and adding 'LIMIT 1' at the end of the query.
"SELECT SQL_CALC_FOUND_ROWS `fb_ir_template_config`.`id` AS
....
`fb_ir_template_config`.`id` ASC LIMIT 1"
This would greatly improve the server response time, since the modified query takes only 0.5896 sec
to process, with the same huge amount of elements and joins. Thats 34 x faster !
So my questions are:
Can this be hacked into the fabrik code that we use in our server ? I have no idea if this would result in other problems along other fabrik lists and forms that we use in our website.
And have no idea how to make this modification either.
Is there any other idea how to improve performance in our fabrik applications ?
Currently, we are using Joomla 2.5.27
Fabrik 3.0.9???
Thanks in advance for your time.
Regards
We are using Fabrik on our website for a couple years with no problems, and we are growing in user accounts and data that is stored.
One of our application build with fabrik is for home-automation, where we generate a customized template, for smartphone or tablet, that is generated based on the choice of the manufacturer and model of TV, BD, Receiver, Air Conditioning, and other typical home appliances controlled by IR codes.
Everything was doing fine, until the database is becoming larger, with more customers and appliances models being added to our database.
But the numbers of records in the DB are relatively small, and we are getting response time from the server over 15 seconds to render a list or to edit a stored configuration (one record in the fb_ir_template_config table).
To separate this application from the joomla website, all template-generator related tables are stored in a separate database, that contains the following tables:
fb_ir_template_config - stores the configuration options of the template - 389 records
fb_ir_model - stores the manufacturer, device model of the IR database for each appliance - 176 records
fb_ir_codes - stores the IR commands for each appliance model - 3642 records
cb_ir_manufacturers - stores the name of appliance manufacturers - 63 records
fb_ir_tv_chanels - stores cable TV channels and image file - 120 records
... others
The Search query that populates the form is huge, due to the database join elements that we use and is causing a long response time from the server, reaching 20 to 26 seconds to process this single query.
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fb_ir_template_config`.`id` AS `fb_ir_template_config___id`, `fb_ir_template_config`.`id` AS `fb_ir_template_config___id_raw`, `fb_ir_template_config`.`ir_config_tv_manuf` AS `fb_ir_template_config___ir_config_tv_manuf_raw`, `fb_ir_model_4`.`manufacturer` AS `fb_ir_template_config___ir_config_tv_manuf`, `fb_ir_template_config`.`ir_config_tv_model` AS `fb_ir_template_config___ir_config_tv_model_raw`,
...
a long long list
...
`fb_ir_template_config`.`id` = '306'AND (( fb_ir_template_config.user_id = '1045' )) ORDER BY `fb_ir_template_config`.`id` ASC
This query takes 20.8 sec to process using PHPMyAdmin to access the MySQL server directly.
Yesterday, this query was generating alerts in our ISP with more than 1000 seconds processing time.
This was improved by optimizing all MySQL tables and by setting some elements to include in list query to yes.
- Include in list queryNoYes
I noticed that when this option is set to NO then the list is generated much faster, but the form editing gets slower. I don?t know how I can optimize this further.
I found out that a much better result could be obtained with a 2 modifications
eliminating the 'DISTICT' and adding 'LIMIT 1' at the end of the query.
"SELECT SQL_CALC_FOUND_ROWS `fb_ir_template_config`.`id` AS
....
`fb_ir_template_config`.`id` ASC LIMIT 1"
This would greatly improve the server response time, since the modified query takes only 0.5896 sec
to process, with the same huge amount of elements and joins. Thats 34 x faster !
So my questions are:
Can this be hacked into the fabrik code that we use in our server ? I have no idea if this would result in other problems along other fabrik lists and forms that we use in our website.
And have no idea how to make this modification either.
Is there any other idea how to improve performance in our fabrik applications ?
Currently, we are using Joomla 2.5.27
Fabrik 3.0.9???
Thanks in advance for your time.
Regards