pastvne
Bruce Decker
Hi:
I've built a Fabrik list and I'm starting to populate it with data. Thinks looked like it was working well but I've run into a snag that I think has to do with my params in Fabrik but I'm not able to figure out what is causing this issue:
This list has about 400K records. It's normal stuff except for one medium text field that I use to store xml. I have some db-join elements and a couple of joins.
I've noticed performance slowing down dramatically as I've approached 200K records. Finally, in Fabrik, my unfiltered list view threw a error indicating that getData lost the connection to the database followed by the query string. The odd thing is that the query string includes a 'IN' clause where Fabrik is attempting to build every key in the list into a literal 'IN' constraint as shown below:
SELECT DISTINCT `bpi_edoc_document_control`.`id` AS `bpi_edoc_document_control___id`, `bpi_edoc_document_control`.`id` AS `bpi_edoc_document_control___id_raw`, `bpi_edoc_document_control`.`doc_reference` AS `bpi_edoc_document_control___doc_reference`, `bpi_edoc_document_control`.`doc_reference` AS `bpi_edoc_document_control___doc_reference_raw`, `bpi_edoc_document_control`.`expiration` AS `bpi_edoc_document_control___expiration`, `bpi_edoc_document_control`.`expiration` AS `bpi_edoc_document_control___expiration_raw`, `bpi_edoc_document_control`.`last_access_id` AS `bpi_edoc_document_control___last_access_id`, `bpi_edoc_document_control`.`last_access_id` AS `bpi_edoc_document_control___last_access_id_raw`, `bpi_edoc_document_control`.`doc_type` AS `bpi_edoc_document_control___doc_type_raw`, `bpi_edoc_doctypes`.`document_id` AS `bpi_edoc_document_control___doc_type`, `bpi_edoc_document_control`.`doc_type` AS `bpi_edoc_document_control___doc_type_raw`, `bpi_edoc_doctypes`.`document_id` AS `bpi_edoc_document_control___doc_type`, `bpi_edoc_document_control`.`doc_version` AS `bpi_edoc_document_control___doc_version`, `bpi_edoc_document_control`.`doc_version` AS `bpi_edoc_document_control___doc_version_raw`, `bpi_edoc_document_control`.`cust_id` AS `bpi_edoc_document_control___cust_id`, `bpi_edoc_document_control`.`cust_id` AS `bpi_edoc_document_control___cust_id_raw`, `bpi_edoc_document_control`.`doc_status` AS `bpi_edoc_document_control___doc_status`, `bpi_edoc_document_control`.`doc_status` AS `bpi_edoc_document_control___doc_status_raw`, `bpi_edoc_document_control`.`doc_parser` AS `bpi_edoc_document_control___doc_parser`, `bpi_edoc_document_control`.`doc_parser` AS `bpi_edoc_document_control___doc_parser_raw`, `bpi_edoc_document_control`.`trans_date` AS `bpi_edoc_document_control___trans_date`, `bpi_edoc_document_control`.`trans_date` AS `bpi_edoc_document_control___trans_date_raw`, `bpi_edoc_document_control`.`rep_ref` AS `bpi_edoc_document_control___rep_ref`, `bpi_edoc_document_control`.`rep_ref` AS `bpi_edoc_document_control___rep_ref_raw`, `bpi_edoc_document_control`.`reference` AS `bpi_edoc_document_control___reference`, `bpi_edoc_document_control`.`reference` AS `bpi_edoc_document_control___reference_raw`, `bpi_edoc_document_control`.`cust_rep_ref` AS `bpi_edoc_document_control___cust_rep_ref`, `bpi_edoc_document_control`.`cust_rep_ref` AS `bpi_edoc_document_control___cust_rep_ref_raw`, `bpi_edoc_document_control`.`cust_ref` AS `bpi_edoc_document_control___cust_ref`, `bpi_edoc_document_control`.`cust_ref` AS `bpi_edoc_document_control___cust_ref_raw`, `bpi_edoc_document_control`.`total_value` AS `bpi_edoc_document_control___total_value`, `bpi_edoc_document_control`.`total_value` AS `bpi_edoc_document_control___total_value_raw`, `bpi_edoc_document_control`.`doc_xml` AS `bpi_edoc_document_control___doc_xml`, `bpi_edoc_document_control`.`doc_xml` AS `bpi_edoc_document_control___doc_xml_raw`, `bpi_edoc_document_control`.`assigned_to` AS `bpi_edoc_document_control___assigned_to`, `bpi_edoc_document_control`.`assigned_to` AS `bpi_edoc_document_control___assigned_to_raw`, `bpi_edoc_document_control`.`stage` AS `bpi_edoc_document_control___stage`, `bpi_edoc_document_control`.`stage` AS `bpi_edoc_document_control___stage_raw`, `bpi_edoc_document_control`.`operation` AS `bpi_edoc_document_control___operation`, `bpi_edoc_document_control`.`operation` AS `bpi_edoc_document_control___operation_raw`, `bpi_edoc_document_control`.`tags` AS `bpi_edoc_document_control___tags`, `bpi_edoc_document_control`.`tags` AS `bpi_edoc_document_control___tags_raw`, `bpi_edoc_document_control`.`last_access_date` AS `bpi_edoc_document_control___last_access_date`, `bpi_edoc_document_control`.`last_access_date` AS `bpi_edoc_document_control___last_access_date_raw`, `bpi_edoc_document_control`.`date_time` AS `bpi_edoc_document_control___date_time`, `bpi_edoc_document_control`.`date_time` AS `bpi_edoc_document_control___date_time_raw`, `bpi_edoc_doctypes`.`id` AS `bpi_edoc_doctypes___id`, `bpi_edoc_doctypes`.`id` AS `bpi_edoc_doctypes___id_raw`, `bpi_edoc_doctypes`.`document_id` AS `bpi_edoc_doctypes___document_id`, `bpi_edoc_doctypes`.`document_id` AS `bpi_edoc_doctypes___document_id_raw`, `bpi_edoc_doctypes`.`status` AS `bpi_edoc_doctypes___status`, `bpi_edoc_doctypes`.`status` AS `bpi_edoc_doctypes___status_raw`, `bpi_edoc_doctypes_0`.`role` AS `bpi_edoc_doctypes___role_raw`, `bpi_edoc_roles`.`description` AS `bpi_edoc_doctypes___role`, `bpi_edoc_doctypes_0`.`template` AS `bpi_edoc_doctypes___template_raw`, `jos_content`.`title` AS `bpi_edoc_doctypes___template`, `bpi_edoc_doctypes`.`date_time` AS `bpi_edoc_doctypes___date_time`, `bpi_edoc_doctypes`.`date_time` AS `bpi_edoc_doctypes___date_time_raw`, `bpi_edoc_document_control`.`id` AS slug , `bpi_edoc_document_control`.`id` AS `__pk_val` FROM `bpi_edoc_document_control` LEFT JOIN `bpi_edoc_doctypes` AS `bpi_edoc_doctypes` ON `bpi_edoc_doctypes`.`id` = `bpi_edoc_document_control`.`doc_type` LEFT JOIN `bpi_edoc_doctypes` AS `bpi_edoc_doctypes_0` ON `bpi_edoc_doctypes_0`.`id` = `bpi_edoc_document_control`.`doc_type` LEFT JOIN `bpi_edoc_roles` AS `bpi_edoc_roles` ON `bpi_edoc_roles`.`id` = `bpi_edoc_doctypes_0`.`role` LEFT JOIN `jos_content` AS `jos_content` ON `jos_content`.`id` = `bpi_edoc_doctypes_0`.`template` WHERE `bpi_edoc_document_control`.`id` IN ('348468','348467','348466','348465','348464','348463','348462','348461','348460','348459','348458','348457','348456','348455','348454','348453','348452','348451','348450','348449','348448','348447','348446','348445','348444','348443','348442','348441','348440','348439','348438','348437','348436','348435','348434','348433','348432','348431','348430','348429','348428','348427','348426','348425','348424','348423','348422','348421','348420','348419','348418','348417','348416','348415','348414','348413','348412','348411','348410','348409','348408','348407','348406','348405','348404','348403','348402','348401','348400','348399','348398','348397','348396','348395','348394','348393','348392','348391','348390','348389','348388','348387','348386','348385','348384','348383','348382','348381','348380','348379','348378','348377','348376','348375','348374','348373','348372','348371','348370','348369','348368','348367','348366','348365','348364','348363','348362','348361','348360','348359','348358','348357','348356','348355','348354','348353','348352','348351','348350','34834
and on and on and on and then followed by:
AND `bpi_edoc_document_control`.`id` IN ('348468','348467','348466','348465','348464','348463','348462','348461','348460','348459') ORDER BY `bpi_edoc_document_control`.`id` DESC.
The list is pointing to the table bpi_edoc_docuement_control and 'id' is the key. But I'm wondering why fabrik, on a unfiltered list needs to build such a large list of ids as literals in the (array) that follows the IN. I think this is part of the cause of my performance issue. I've tried trying to remove joins but I've not been able to avoid this behavior so I'm thinking this is perhaps proper for Fabrik? Also, I've been able to 'limp by' by setting the List param to require filtering to reduce the size of the key array above but I consider that a temporary work around that is likely to fail at some point as the database size increases.
Any advice appreciated.
Regards,
Bruce Decker
I've built a Fabrik list and I'm starting to populate it with data. Thinks looked like it was working well but I've run into a snag that I think has to do with my params in Fabrik but I'm not able to figure out what is causing this issue:
This list has about 400K records. It's normal stuff except for one medium text field that I use to store xml. I have some db-join elements and a couple of joins.
I've noticed performance slowing down dramatically as I've approached 200K records. Finally, in Fabrik, my unfiltered list view threw a error indicating that getData lost the connection to the database followed by the query string. The odd thing is that the query string includes a 'IN' clause where Fabrik is attempting to build every key in the list into a literal 'IN' constraint as shown below:
SELECT DISTINCT `bpi_edoc_document_control`.`id` AS `bpi_edoc_document_control___id`, `bpi_edoc_document_control`.`id` AS `bpi_edoc_document_control___id_raw`, `bpi_edoc_document_control`.`doc_reference` AS `bpi_edoc_document_control___doc_reference`, `bpi_edoc_document_control`.`doc_reference` AS `bpi_edoc_document_control___doc_reference_raw`, `bpi_edoc_document_control`.`expiration` AS `bpi_edoc_document_control___expiration`, `bpi_edoc_document_control`.`expiration` AS `bpi_edoc_document_control___expiration_raw`, `bpi_edoc_document_control`.`last_access_id` AS `bpi_edoc_document_control___last_access_id`, `bpi_edoc_document_control`.`last_access_id` AS `bpi_edoc_document_control___last_access_id_raw`, `bpi_edoc_document_control`.`doc_type` AS `bpi_edoc_document_control___doc_type_raw`, `bpi_edoc_doctypes`.`document_id` AS `bpi_edoc_document_control___doc_type`, `bpi_edoc_document_control`.`doc_type` AS `bpi_edoc_document_control___doc_type_raw`, `bpi_edoc_doctypes`.`document_id` AS `bpi_edoc_document_control___doc_type`, `bpi_edoc_document_control`.`doc_version` AS `bpi_edoc_document_control___doc_version`, `bpi_edoc_document_control`.`doc_version` AS `bpi_edoc_document_control___doc_version_raw`, `bpi_edoc_document_control`.`cust_id` AS `bpi_edoc_document_control___cust_id`, `bpi_edoc_document_control`.`cust_id` AS `bpi_edoc_document_control___cust_id_raw`, `bpi_edoc_document_control`.`doc_status` AS `bpi_edoc_document_control___doc_status`, `bpi_edoc_document_control`.`doc_status` AS `bpi_edoc_document_control___doc_status_raw`, `bpi_edoc_document_control`.`doc_parser` AS `bpi_edoc_document_control___doc_parser`, `bpi_edoc_document_control`.`doc_parser` AS `bpi_edoc_document_control___doc_parser_raw`, `bpi_edoc_document_control`.`trans_date` AS `bpi_edoc_document_control___trans_date`, `bpi_edoc_document_control`.`trans_date` AS `bpi_edoc_document_control___trans_date_raw`, `bpi_edoc_document_control`.`rep_ref` AS `bpi_edoc_document_control___rep_ref`, `bpi_edoc_document_control`.`rep_ref` AS `bpi_edoc_document_control___rep_ref_raw`, `bpi_edoc_document_control`.`reference` AS `bpi_edoc_document_control___reference`, `bpi_edoc_document_control`.`reference` AS `bpi_edoc_document_control___reference_raw`, `bpi_edoc_document_control`.`cust_rep_ref` AS `bpi_edoc_document_control___cust_rep_ref`, `bpi_edoc_document_control`.`cust_rep_ref` AS `bpi_edoc_document_control___cust_rep_ref_raw`, `bpi_edoc_document_control`.`cust_ref` AS `bpi_edoc_document_control___cust_ref`, `bpi_edoc_document_control`.`cust_ref` AS `bpi_edoc_document_control___cust_ref_raw`, `bpi_edoc_document_control`.`total_value` AS `bpi_edoc_document_control___total_value`, `bpi_edoc_document_control`.`total_value` AS `bpi_edoc_document_control___total_value_raw`, `bpi_edoc_document_control`.`doc_xml` AS `bpi_edoc_document_control___doc_xml`, `bpi_edoc_document_control`.`doc_xml` AS `bpi_edoc_document_control___doc_xml_raw`, `bpi_edoc_document_control`.`assigned_to` AS `bpi_edoc_document_control___assigned_to`, `bpi_edoc_document_control`.`assigned_to` AS `bpi_edoc_document_control___assigned_to_raw`, `bpi_edoc_document_control`.`stage` AS `bpi_edoc_document_control___stage`, `bpi_edoc_document_control`.`stage` AS `bpi_edoc_document_control___stage_raw`, `bpi_edoc_document_control`.`operation` AS `bpi_edoc_document_control___operation`, `bpi_edoc_document_control`.`operation` AS `bpi_edoc_document_control___operation_raw`, `bpi_edoc_document_control`.`tags` AS `bpi_edoc_document_control___tags`, `bpi_edoc_document_control`.`tags` AS `bpi_edoc_document_control___tags_raw`, `bpi_edoc_document_control`.`last_access_date` AS `bpi_edoc_document_control___last_access_date`, `bpi_edoc_document_control`.`last_access_date` AS `bpi_edoc_document_control___last_access_date_raw`, `bpi_edoc_document_control`.`date_time` AS `bpi_edoc_document_control___date_time`, `bpi_edoc_document_control`.`date_time` AS `bpi_edoc_document_control___date_time_raw`, `bpi_edoc_doctypes`.`id` AS `bpi_edoc_doctypes___id`, `bpi_edoc_doctypes`.`id` AS `bpi_edoc_doctypes___id_raw`, `bpi_edoc_doctypes`.`document_id` AS `bpi_edoc_doctypes___document_id`, `bpi_edoc_doctypes`.`document_id` AS `bpi_edoc_doctypes___document_id_raw`, `bpi_edoc_doctypes`.`status` AS `bpi_edoc_doctypes___status`, `bpi_edoc_doctypes`.`status` AS `bpi_edoc_doctypes___status_raw`, `bpi_edoc_doctypes_0`.`role` AS `bpi_edoc_doctypes___role_raw`, `bpi_edoc_roles`.`description` AS `bpi_edoc_doctypes___role`, `bpi_edoc_doctypes_0`.`template` AS `bpi_edoc_doctypes___template_raw`, `jos_content`.`title` AS `bpi_edoc_doctypes___template`, `bpi_edoc_doctypes`.`date_time` AS `bpi_edoc_doctypes___date_time`, `bpi_edoc_doctypes`.`date_time` AS `bpi_edoc_doctypes___date_time_raw`, `bpi_edoc_document_control`.`id` AS slug , `bpi_edoc_document_control`.`id` AS `__pk_val` FROM `bpi_edoc_document_control` LEFT JOIN `bpi_edoc_doctypes` AS `bpi_edoc_doctypes` ON `bpi_edoc_doctypes`.`id` = `bpi_edoc_document_control`.`doc_type` LEFT JOIN `bpi_edoc_doctypes` AS `bpi_edoc_doctypes_0` ON `bpi_edoc_doctypes_0`.`id` = `bpi_edoc_document_control`.`doc_type` LEFT JOIN `bpi_edoc_roles` AS `bpi_edoc_roles` ON `bpi_edoc_roles`.`id` = `bpi_edoc_doctypes_0`.`role` LEFT JOIN `jos_content` AS `jos_content` ON `jos_content`.`id` = `bpi_edoc_doctypes_0`.`template` WHERE `bpi_edoc_document_control`.`id` IN ('348468','348467','348466','348465','348464','348463','348462','348461','348460','348459','348458','348457','348456','348455','348454','348453','348452','348451','348450','348449','348448','348447','348446','348445','348444','348443','348442','348441','348440','348439','348438','348437','348436','348435','348434','348433','348432','348431','348430','348429','348428','348427','348426','348425','348424','348423','348422','348421','348420','348419','348418','348417','348416','348415','348414','348413','348412','348411','348410','348409','348408','348407','348406','348405','348404','348403','348402','348401','348400','348399','348398','348397','348396','348395','348394','348393','348392','348391','348390','348389','348388','348387','348386','348385','348384','348383','348382','348381','348380','348379','348378','348377','348376','348375','348374','348373','348372','348371','348370','348369','348368','348367','348366','348365','348364','348363','348362','348361','348360','348359','348358','348357','348356','348355','348354','348353','348352','348351','348350','34834
and on and on and on and then followed by:
AND `bpi_edoc_document_control`.`id` IN ('348468','348467','348466','348465','348464','348463','348462','348461','348460','348459') ORDER BY `bpi_edoc_document_control`.`id` DESC.
The list is pointing to the table bpi_edoc_docuement_control and 'id' is the key. But I'm wondering why fabrik, on a unfiltered list needs to build such a large list of ids as literals in the (array) that follows the IN. I think this is part of the cause of my performance issue. I've tried trying to remove joins but I've not been able to avoid this behavior so I'm thinking this is perhaps proper for Fabrik? Also, I've been able to 'limp by' by setting the List param to require filtering to reduce the size of the key array above but I consider that a temporary work around that is likely to fail at some point as the database size increases.
Any advice appreciated.
Regards,
Bruce Decker