Hi Eugenio,
I think it's a bit of a shame that you are not yet engaged with this problem.
I believe it's not unreasonable to expect DaDaBik to be able to process a .csv of 200 records with 3 lookups without timing out after 60 seconds.
Your response that the solution is for me to get a faster ISP service or be prepared to wait several minutes for the report to be generated is IMO not really smart thinking. Wouldn't it be better for DaDaBik to find a way to create these reports more efficiently?
...and I have. I can now make my much larger reports of 600 records with 13 lookups in about 4 seconds. I think that this order-of-magnitude difference in performance suggests that there may be scope for you to redesign your .csv creation routine and make DaDaBik a better product in the process.
Let me explain what I did (it's working fine and my users are running it without problems).
I don't know how DaDaBik calls for data during the present .csv routine and I apologise if my ignorance offends, but to test whether the problem was simply my ISP being slow I designed a View in the database that already includes the looked up values in plain text rather than in coded values.
Because this View returns the data set to DaDaBik already including the text substitutions, the DaDaBik .csv routine doesn't need to do the thing that cripples it and everything runs quickly. As I say, the csv report now completes in a few seconds.
Apart from the effort and complexity in creating and managing additional essentially redundant Views like this there are several operational drawbacks in this approach - for example the records in this View could never be editable because we are seeing the text labels rather than the underlying coded values. So it becomes necessary for my App to have one set of meuns/tables/views/forms for data entry and another set of menus/view/forms for .csv export all of which need their designs and permissions to be maintained.
However, I think I have proved the point that DaDaBik could consider developing a different paradigm that would avoid the problem in the first place and thus make my workaround unneccesary. How would it be for you if the .csv routine worked as follows:-
CSVExportButton onClick -
1. Creates a relevant SQL query dependent on the currently displayed results grid - and, crucially embeds all the lookups into this SQL(example below).
2. Creates a temporary View in the database using that Select statement.
3. Runs the csv export using the newly created Temp View.
4. Drops the Temp View from the database.
Hope this helps someone.
Cheers, David.
[pre]
SELECT
`phpbb_users`.`user_id` AS `id`,
`phpbb_profile_fields_data`.`pf_title` AS `Title`,
`phpbb_profile_fields_data`.`pf_lastname` AS `Lastname`,
`phpbb_profile_fields_data`.`pf_firstname` AS `Firstname`,
`phpbb_profile_fields_data`.`pf_emailsalutation` AS `EmailSalutation`,
`phpbb_profile_fields_data`.`pf_postalsalutation` AS `PostalEnvelope`,
`phpbb_profile_fields_data`.`pf_address_one` AS `Address1`,
`phpbb_profile_fields_data`.`pf_address_two` AS `Address2`,
`phpbb_profile_fields_data`.`pf_address_three` AS `Address3`,
`phpbb_profile_fields_data`.`pf_city` AS `City`,
`phpbb_profile_fields_data`.`pf_county` AS `County`,
`phpbb_profile_fields_data`.`pf_postcode` AS `Postcode`,
`phpbb_profile_fields_data`.`pf_telephone` AS `Telephone`,
`user_DropDownListTextValues_1`.`fld_Boolean` AS `EmailWorks`,
`user_DropDownListTextValues_2`.`fld_userStatus` AS `UserStatus`,
`phpbb_users`.`user_email` AS `Email`,
`user_DropDownListTextValues_3`.`fld_userType` AS `AccessLevel`,
`phpbb_users`.`username` AS `Username`,
`phpbb_users`.`user_interests` AS `Interests`,
`user_DropDownListTextValues_4`.`fld_Boolean` AS `CommsByPost`,
`user_DropDownListTextValues_5`.`fld_Boolean` AS `NewsletterByPost`,
`user_DropDownListTextValues_6`.`fld_years` AS `YearJoined`,
`user_DropDownListTextValues_7`.`fld_years` AS `PaidUntil`,
`user_DropDownListTextValues_8`.`fld_paymentMethod` AS `PaymentMethod`,
`user_DropDownListTextValues_9`.`fld_Boolean` AS `ShowEmail`,
`user_DropDownListTextValues_10`.`fld_Boolean` AS `ShowPhone`,
`user_DropDownListTextValues_11`.`fld_Boolean` AS `ShowAddress`,
`phpbb_profile_fields_data`.`pf_partnerto` AS `PartnerTo`,
`user_DropDownListTextValues_12`.`fld_Boolean` AS `GDPRconsent`,
`user_DropDownListTextValues_13`.`fld_Boolean` AS `ShowInterests`
FROM ((((((((((((((`phpbb_users`
JOIN `phpbb_profile_fields_data`
ON ((`phpbb_users`.`user_id` = `phpbb_profile_fields_data`.`user_id`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_1`
ON ((`phpbb_profile_fields_data`.`pf_emailactive` = `user_DropDownListTextValues_1`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_2`
ON ((`phpbb_users`.`fld_currentMember` = `user_DropDownListTextValues_2`.`fld_userCode`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_3`
ON ((`phpbb_users`.`user_type` = `user_DropDownListTextValues_3`.`fld_userCode`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_4`
ON ((`phpbb_profile_fields_data`.`pf_commsbypost` = `user_DropDownListTextValues_4`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_5`
ON ((`phpbb_profile_fields_data`.`pf_newsletterbypost` = `user_DropDownListTextValues_5`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_6`
ON ((`phpbb_profile_fields_data`.`pf_yearjoined` = `user_DropDownListTextValues_6`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_7`
ON ((`phpbb_profile_fields_data`.`pf_paiduntil` = `user_DropDownListTextValues_7`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_8`
ON ((`phpbb_profile_fields_data`.`pf_paymentmethod` = `user_DropDownListTextValues_8`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_9`
ON ((`phpbb_profile_fields_data`.`pf_showemail` = `user_DropDownListTextValues_9`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_10`
ON ((`phpbb_profile_fields_data`.`pf_showphone` = `user_DropDownListTextValues_10`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_11`
ON ((`phpbb_profile_fields_data`.`pf_showaddress` = `user_DropDownListTextValues_11`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_12`
ON ((`phpbb_profile_fields_data`.`pf_gdpr_consent` = `user_DropDownListTextValues_12`.`fld_PrimaryKey`)))
JOIN `user_DropDownListTextValues` `user_DropDownListTextValues_13`
ON ((`phpbb_profile_fields_data`.`pf_show_interests` = `user_DropDownListTextValues_13`.`fld_PrimaryKey`)))
ORDER BY `phpbb_profile_fields_data`.`pf_lastname`
[/pre]
