List loading extremely slow when with join applied

gnavarro

gnavarro

Member
Hi,
I've Joomla! 3.8.1 Stable and Fabrik 3.8.

I have a list (pacientes) that when with a join to the list fb_locais_tratamento loads extremely slow!

You can test the list loading time in https://www.fisiolar.pt/development/index.php/pt/pacientes

Please go to my sites to get access to protected directory and administration if needed

Can you please help me mitigate what is going wrong?

Thanks in advance
 
I had a look for any obvious reasons, but nothing jumps out. The only way I can proceed with it would be to (re)install your site here from an Akeeba backup, and run through the list building in PHp Storm, see where the bottleneck is. Which is outside the scope of subscription support, I'd need to bill about an hour.

-- hugh
 
Yup, it's on my list. You are currently third in line. "Please hold, an available operator will take your call as soon as possible".

And just to be clear - you are prepared to pay the billable time for this?

-- hugh
 
Installed, but any attempt to access the front end gives me a fatal error ...

Call to undefined method JchOptimizeLinkBuilder::getEndHeadTag()

I'm trying to figure out why.

-- hugh
 
I had to turn off the JCH Optimizer plugin, no idea how it works on your site, as that getEndHeadTag() definitely isn't defined.

Working on the issue now.

-- hugh
 
OK, the problem is that the parent_id field on the fb_locais_tratamento table is a VARCHAR, not an INT. So when we run any queries that join that to any other table, no indexing happens even though there's an index on it (MySQL is picky about applying indexes between fields of different data types), which is like running in to a brick wall as far as database performance goes.

After changing that to an int, the total query time (time spent within MySQL itself) for that page drops from about 28 seconds to under a second on my server.

I logged into your live site, and set the "Format" for that parent_id field element to be INT, which causes Fabrik to change the underlying table field type to INT. That page now loads considerably faster.

You might want to check any other fields you created and use in list joins, and make sure you set those to INT as well.

(Database join elements will automatically have the underlying field type set by Fabrik to match the type of the field they are joining to, but when you use a simple field element as part of a list join, you have to be careful to set the type correctly yourself)

-- hugh
 
In the other list, canditaturas, the problem seems to be that the table you are joining to (6 times), my_concelhos, has no PK (Primary Key) designated, so again, no indexing.

I fixed that here by using a mysql client and just designating 'id' as the auto-inc PK.

I can't fix your site, as it doesn't have a Fabrik list on it. Use phpMyAdmin (or whatever) to set the PK.

-- hugh
 
Hugh,

Thanks... the list 'pacientes' is ok now ;)

But the list 'candidaturas' continues to take some time to load :( I've made the requested changes (attached image).

Please advise.
 

Attachments

  • Capturar.PNG
    Capturar.PNG
    383.4 KB · Views: 45
Dunno, it loads in a couple of seconds here after making that change.

The other thing you could try is create indexes for all the fields you join to:

Code: 
FROM `fb_candidaturas`
LEFT JOIN  `my_concelhos` AS `my_concelhos` ON `my_concelhos`.`id` = `fb_candidaturas`.`concelho`
LEFT JOIN  `my_concelhos` AS `my_concelhos_0` ON `my_concelhos_0`.`id` = `fb_candidaturas`.`disp_1`
LEFT JOIN  `my_concelhos` AS `my_concelhos_1` ON `my_concelhos_1`.`id` = `fb_candidaturas`.`disp_2`
LEFT JOIN  `my_concelhos` AS `my_concelhos_2` ON `my_concelhos_2`.`id` = `fb_candidaturas`.`disp_3`
LEFT JOIN  `my_concelhos` AS `my_concelhos_3` ON `my_concelhos_3`.`id` = `fb_candidaturas`.`disp_4`
LEFT JOIN  `my_concelhos` AS `my_concelhos_4` ON `my_concelhos_4`.`id` = `fb_candidaturas`.`disp_5`
LEFT JOIN  `my_concelhos` AS `my_concelhos_5` ON `my_concelhos_5`.`id` = `fb_candidaturas`.`disp_6`
LEFT JOIN  `flar_users` AS `flar_users` ON `flar_users`.`id` = `fb_candidaturas`.`lasteditedby`

So the fields on the right. I think there is already one for disp_1, as there's a filter on it.

To be honest I thought we already automagically added indexes on the foreign key fields for joins, but apparently we only do that for list joins. And thinking about it, I think we decided on not doing it for join elements because you might be joining to "someone else's" table, nothing to do with Fabrik, and we don't want to mess with them.

Doesn't matter what you call the index, but our convention would be fb_join_tablename_fieldname_INDEX. Type "normal", method "btree".

-- hugh
 
Hugh,

Just done that and loading time is about 10 sec! :(

If you could enter in my administration and use the component VJ Database Tool you can make all the necessary changes to the table structure to accelerate the list loading time. I'm probably missing something
 
The queries themselves are running very quickly in the database tool. So I need to put the site in J! debug mode to see a profile, but when I do that, it runs out of memory (J!'s profiling output can grow very large). Can you bump the memory_limit up on your server (php.ini)? Double it for now, to 1024M.

-- hugh
 
Done!

Also just notice that in my main site suddenly I have a duplicated list of "tratamentos" (Eliminated already) and that the related data from list "pacientes" to "tratamentos" is not appearing any more!

Can you please give an eye to what is generating this miss function?
 
Last edited:
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    No members online now.
    Total: 152 (members: 1, guests: 151)
    Back
    Top