List loading very slow

[itsdipak]

filtered List loading very slowly. records not more than 300.

Now, Instead loading got the below error.
Fatal error: Maximum execution time of 60 seconds exceeded in C:\xampp\htdocs\pvipl\plugins\fabrik_element\thumbs\thumbs.php on line 12

after reloading, its loaded but still slow loading problem is there.
If you have any guide or suggestion to take care while building list, kindly suggest.

 

[itsdipak]

filtered List loading very slowly. records not more than 300.

More details :
Generated query string is as below

SELECT SQL_CALC_FOUND_ROWS DISTINCT `turbineinfo`.`srno` AS `turbineinfo___srno`, `turbineinfo`.`srno` AS `turbineinfo___srno_raw`, `turbineinfo_0`.`customer_id` AS `turbineinfo___customer_id_raw`, CONCAT_WS('', `customerinfo`.customerGroup, ' - ', `customerinfo`.customerCode) AS `turbineinfo___customer_id`, `turbineinfo_0`.`site_id` AS `turbineinfo___site_id_raw`, `siteinfo`.`siteName` AS `turbineinfo___site_id`, `turbineinfo`.`sr_no` AS `turbineinfo___sr_no`, `turbineinfo`.`sr_no` AS `turbineinfo___sr_no_raw`, `wtg_dgr_data`.`id` AS `wtg_dgr_data___id`, `wtg_dgr_data`.`id` AS `wtg_dgr_data___id_raw`, `wtg_dgr_data`.`turbine_id` AS `wtg_dgr_data___turbine_id_raw`, `turbineinfo`.`turbineCode` AS `wtg_dgr_data___turbine_id`, `wtg_dgr_data`.`logDate` AS `wtg_dgr_data___logDate`, `wtg_dgr_data`.`logDate` AS `wtg_dgr_data___logDate_raw`, `wtg_dgr_data`.`generatedUnits` AS `wtg_dgr_data___generatedUnits`, `wtg_dgr_data`.`generatedUnits` AS `wtg_dgr_data___generatedUnits_raw`, `wtg_dgr_data`.`lullHours` AS `wtg_dgr_data___lullHours`, `wtg_dgr_data`.`lullHours` AS `wtg_dgr_data___lullHours_raw`, `wtg_dgr_data`.`operatingHours` AS `wtg_dgr_data___operatingHours`, `wtg_dgr_data`.`operatingHours` AS `wtg_dgr_data___operatingHours_raw`, `wtg_dgr_data`.`machineAvail` AS `wtg_dgr_data___machineAvail`, `wtg_dgr_data`.`machineAvail` AS `wtg_dgr_data___machineAvail_raw`, `wtg_dgr_data`.`capacityFactor` AS `wtg_dgr_data___capacityFactor`, `wtg_dgr_data`.`capacityFactor` AS `wtg_dgr_data___capacityFactor_raw`, `wtg_dgr_data`.`InternalGridAval` AS `wtg_dgr_data___InternalGridAval`, `wtg_dgr_data`.`InternalGridAval` AS `wtg_dgr_data___InternalGridAval_raw`, `wtg_dgr_data`.`gridAvail` AS `wtg_dgr_data___gridAvail`, `wtg_dgr_data`.`gridAvail` AS `wtg_dgr_data___gridAvail_raw`, `wtg_dgr_data`.`cumGenUnits` AS `wtg_dgr_data___cumGenUnits`, `wtg_dgr_data`.`cumGenUnits` AS `wtg_dgr_data___cumGenUnits_raw`, `wtg_dgr_data`.`cumOperHr` AS `wtg_dgr_data___cumOperHr`, `wtg_dgr_data`.`cumOperHr` AS `wtg_dgr_data___cumOperHr_raw`, `wtg_dgr_data`.`referencefaultHr` AS `wtg_dgr_data___referencefaultHr`, `wtg_dgr_data`.`referencefaultHr` AS `wtg_dgr_data___referencefaultHr_raw`, `wtg_dgr_data`.`faultHrDaily` AS `wtg_dgr_data___faultHrDaily`, `wtg_dgr_data`.`faultHrDaily` AS `wtg_dgr_data___faultHrDaily_raw`, `wtg_dgr_data`.`gfHrInternal` AS `wtg_dgr_data___gfHrInternal`, `wtg_dgr_data`.`gfHrInternal` AS `wtg_dgr_data___gfHrInternal_raw`, `wtg_dgr_data`.`gfHrExternal` AS `wtg_dgr_data___gfHrExternal`, `wtg_dgr_data`.`gfHrExternal` AS `wtg_dgr_data___gfHrExternal_raw`, `wtg_dgr_data`.`addedby` AS `wtg_dgr_data___addedby_raw`, `wtg_dgr_data`.`addedby` AS `wtg_dgr_data___addedby`, `wtg_dgr_data`.`verifiedby` AS `wtg_dgr_data___verifiedby_raw`, `wtg_dgr_data`.`verifiedby` AS `wtg_dgr_data___verifiedby`, `wtg_dgr_data`.`addedtimestamp` AS `wtg_dgr_data___addedtimestamp`, `wtg_dgr_data`.`addedtimestamp` AS `wtg_dgr_data___addedtimestamp_raw`, `wtg_dgr_data`.`varifytimestamp` AS `wtg_dgr_data___varifytimestamp`, `wtg_dgr_data`.`varifytimestamp` AS `wtg_dgr_data___varifytimestamp_raw`, `wtg_dgr_data`.`wtgMeterReading` AS `wtg_dgr_data___wtgMeterReading`, `wtg_dgr_data`.`wtgMeterReading` AS `wtg_dgr_data___wtgMeterReading_raw`, `wtg_dgr_data`.`remark` AS `wtg_dgr_data___remark`, `wtg_dgr_data`.`remark` AS `wtg_dgr_data___remark_raw`, `wtg_dgr_data`.`extra01` AS `wtg_dgr_data___extra01`, `wtg_dgr_data`.`extra01` AS `wtg_dgr_data___extra01_raw`, `wtg_dgr_data`.`sr_no` AS `wtg_dgr_data___sr_no`, `wtg_dgr_data`.`sr_no` AS `wtg_dgr_data___sr_no_raw`, `wtg_dgr_data`.`id` AS slug , `wtg_dgr_data`.`id` AS `__pk_val` FROM `wtg_dgr_data` LEFT JOIN `turbineinfo` AS `turbineinfo` ON `turbineinfo`.`id` = `wtg_dgr_data`.`turbine_id` LEFT JOIN `turbineinfo` AS `turbineinfo_0` ON `turbineinfo_0`.`id` = `wtg_dgr_data`.`turbine_id` LEFT JOIN `siteinfo` AS `siteinfo_0` ON `siteinfo_0`.`id` = `wtg_dgr_data`.`siteinfo_id` LEFT JOIN `customerinfo` AS `customerinfo` ON `customerinfo`.`id` = `turbineinfo_0`.`customer_id` LEFT JOIN `siteinfo` AS `siteinfo` ON `siteinfo`.`id` = `turbineinfo_0`.`site_id` WHERE ( `wtg_dgr_data`.`logDate` BETWEEN '2017-04-01 00:00:00' AND '2017-04-01 23:59:59' ) ORDER BY `turbineinfo`.`site_id` ASC

 

[troester]

If you run this query in phpMyAdmin how many records do you get? (you have several joins)

Do you have calc elements?
Element filters set to "recorded data"?

 

[itsdipak]

If you run this query in phpMyAdmin how many records do you get? (you have several joins)

I get 113 records for the above query. No Calc element.
"Element filters set to 'recorded data'? " What does it mean. Can you please elaborate?

I also dont understand why two times JOIN for wtg_dgr_data`.`turbine_id on turbineinfo
(LEFT JOIN `turbineinfo` AS `turbineinfo` ON `turbineinfo`.`id` = `wtg_dgr_data`.`turbine_id` LEFT JOIN `turbineinfo` AS `turbineinfo_0` ON `turbineinfo_0`.`id` = `wtg_dgr_data`.`turbine_id`)

also repeating JOIN "siteinfo" Dont understand why.

Can we remove"_raw columns" from list query?

 

[cheesegrits]

"Element filters set to 'recorded data'? " What does it mean. Can you please elaborate?

If you have element filters on join elements, there are two modes - 'recorded data', which will only show options that have been selected in your forms, and 'all data', which will show all the options from the joined table. The latter is quicker. The former has to do an extra query to figure out which options have been used in your existing data.

I can't tell you why the two joins without looking at your setup. Fill in http://fabrikar.com/you/my-sites with a backend account (superadmin so I can enable debug).

-- hugh

 

[itsdipak]

My application is not on public domain. Will try to put on public ip and share details