[SOLVED] List loading to slow and filters not working correctly

[gnavarro]

Hi,

My config:
Fabrik 3.3.2
Joomla! 3.4.3
Template - Protostar
Updated from github
Access to site admin in my Fabrik account

Since some days ago that I've notice one of my lists opening really slow (+/- 30 sec.). This list have 33 elements and about 4200 records.

when I change "add DISTICT" to no in list settings "Advanced" tab the list stops loading!!

List link: http://www.fisiolar.pt/index.php/pt/sessoes-de-tratamento-secretariado

Also I have configured in other different list (http://www.fisiolar.pt/index.php/pt/tratamentos-fisioterapia) a "related data" to this same list, but when I click the button to filter and show me the related records it does not filter and instead is disabling the list pre-filters showing unwanted records to the users.

All my other lists and related data are loading/filtering as intended.

Please advise.

 

[gnavarro]

Friendly Bump... Sorry I have urgency resolving this issue because some of my users are having access to non authorized records

 

[troester]

1. can you turn off "Mouse over Edit icons" in GlobalConfig/Site? This will break the dropdown elements in your form in the module if an admin is logged in.

What do you mean with "List filters are not working...", seems ok to me.
I can see a console message
"observer not found fb_sessoes_fisioterapia___id_tratamentovalue"
Do you have a CDD element in your filters?

Yes, there seems to be something wrong with your filters, CDD etc.

Clicking the related data link Chrome is throwing
VM335 12?fb_sessoes_fisioterapia___id_tratamento_raw=584&limitstart12=0&resetfilters=1&fabrik_incse?:1399 Uncaught ReferenceError: CascadeFilter is not defined

is id_tratamento published? does it have an index added in your DB?

 

[gnavarro]

Hi Troester,
Thanks in advance for your support.... My comments are bellow.

1. can you turn off "Mouse over Edit icons" in GlobalConfig/Site? This will break the dropdown elements in your form in the module if an admin is logged in.

DONE. But don't understand what will do and if I should turn it on later

What do you mean with "List filters are not working...", seems ok to me. Forget this one... already deleted from my previous post.
I can see a console message
"observer not found fb_sessoes_fisioterapia___id_tratamentovalue"
Do you have a CDD element in your filters?
Yes... I have 4 ccd:
-user_id
-id_pac
-id_local_tratamento
-user_id_fisiot

Yes, there seems to be something wrong with your filters, CDD etc.

Clicking the related data link Chrome is throwing
VM335 12?fb_sessoes_fisioterapia___id_tratamento_raw=584&limitstart12=0&resetfilters=1&fabrik_incse?:1399 Uncaught ReferenceError: CascadeFilter is not defined

is id_tratamento published? does it have an index added in your DB?

Don't know if I understand what you mean but in the elements list id_tratamento is published. The index you refer is in mysql? How can I check that? Please notice that this value can be repeated in this table.

Please advise

 

[troester]

There's no list prefilter on Sess?es de Tratamento/fb_sessoes_fisioterapia so I can't see what you mean with
"Sorry I have urgency resolving this issue because some of my users are having access to non authorized records"

 

[troester]

Check your CDD elements. I just opened Colaborator and it has nothing set at all in data.

And as long as you don't set a prefilter on Sess?es de Tratamento all registered users can see all records.

 

[gnavarro]

There's no list prefilter on Sess?es de Tratamento/fb_sessoes_fisioterapia so I can't see what you mean with
"Sorry I have urgency resolving this issue because some of my users are having access to non authorized records"

Troester,

Yes there are... please see the image.

 

[gnavarro]

Check your CDD elements. I just opened Colaborator and it has nothing set at all in data.

And as long as you don't set a prefilter on Sess?es de Tratamento all registered users can see all records.

Strange!!! why can not you see my configurations! as you can see in the image the cdd colaborador is configured correctly

 

[troester]

Just found that it is in my Firefox, in Chrome I can see the prefilter.

 

[troester]

In your CDDs you don't use the id[recommended] as id/Value which may slow because of missing indexes.

The watched element Tratamento is not in your filters, I really don't know if this is working.

 

[gnavarro]

I've just noticed that only for the usergroup "secretariado" that the list is loading slowly for all the other usergroups the list loads normaly ...

 

[gnavarro]

Also the problem with the related data in the list "Tratamentos | fb_tratamento_fisiot" to " Sess?es de Tratamento | fb_sessoes_fisioterapia" when clicking to see the related records, is showing instead all table records passing through the list pre-filters, is also just occurring with the usergroup "secretariado". The problem I believe is some how connected to this user group.

Troester, can you please tell me your thoughts about this... does it make any sense?

 

[gnavarro]

In your CDDs you don't use the id[recommended] as id/Value which may slow because of missing indexes.

The watched element Tratamento is not in your filters, I really don't know if this is working.

Well I don't use the id[recommended] because I'm not getting the data from the main tables but insted from the table "Tratamentos | fb_tratamento_fisiot" where I'm crossing the necessary ids for the ccd to know what to retrieve.

But like I previous said all the problems seam to be connected with the usergroup "secretariado" where the fabrik support account is also associated. If you edit the fabrik user and remove the user group "secretariado" and log in again you will notice that the problems seam to be over. Strange!

 

[gnavarro]

Well about the problem with the related data not filtering the connected table it's not true that the problem is solved when removing the user group "secretariado", instead you have to assign the user group "Ger?ncia" for the filters to work.

So for the two following issues:
Issue 1- List "Sess?es de Tratamento | fb_sessoes_fisioterapia" loading to slow
Issue 2- Related data on "Tratamentos | fb_tratamento_fisiot" not filtering "Sess?es de Tratamento | fb_sessoes_fisioterapia"

It seams that they work for some user groups and not for others, as follows:
Issue 1 - Seams to be related with the user group "secretariado", for all other groups the list loads fast
Issue 2- Seams to work only for the users that are also in the user group "Ger?ncia"

This is what I've manage to come up with and is hard for me to understand why and what to do to correct the issues. In the past with those same ccds/db everithing worked correctly.

Please once again I request your help to have this issues corrected with the most urgency possible.

Thanks in advance

 

[troester]

Two of the CDD filters are applied to secretariado only and it may become really slow with missing indexes on your "lookup" columns and the fact that you are using the same table for the dbjoin + multiple CDDs. I don't know how many internal joins this will create.

The prefilter is applied, but you've only set the "last two month" for secretarado.

I've enabled fabrikdebug in options.
Maybe Rob or Hugh have an idea (but it's weekend).

I would disable all filtering on the CDD elements, also the public one, and see if this will change anything.

 

[cheesegrits]

I get a 404 on the site.

-- hugh

 

[gnavarro]

I get a 404 on the site.

-- hugh

Hi Hugh,

404!? strange... try to follow this link http://www.fisiolar.pt/index.php/pt/sessoes-de-tratamento-secretariado

 

[cheesegrits]

I think you have the link in your My Sites wrong for the backend.

-- hugh

 

[gnavarro]

I think you have the link in your My Sites wrong for the backend.

-- hugh

Yes... true, but I don't know how to edit those.

The correct link is http://www.fisiolar.pt/administrator/

 

[cheesegrits]

OK, well, as Troester said, it's going to be slow because you are using non-normalized data for your CDD's. So your join clause is:

LEFT JOIN `fb_tratamento_fisiot` AS `fb_tratamento_fisiot` ON `fb_tratamento_fisiot`.`id` = `fb_sessoes_fisioterapia`.`id_tratamento`
LEFT JOIN `fb_tratamento_fisiot` AS `fb_tratamento_fisiot_0` ON `fb_tratamento_fisiot_0`.`user_id` = `fb_sessoes_fisioterapia`.`user_id`
LEFT JOIN `fb_tratamento_fisiot` AS `fb_tratamento_fisiot_1` ON `fb_tratamento_fisiot_1`.`id_pac` = `fb_sessoes_fisioterapia`.`id_pac`
LEFT JOIN `fb_tratamento_fisiot` AS `fb_tratamento_fisiot_2` ON `fb_tratamento_fisiot_2`.`id_local_tratamento` = `fb_sessoes_fisioterapia`.`id_local_tratamento`
LEFT JOIN `#__users` AS `flar_users` ON `flar_users`.`id` = `fb_sessoes_fisioterapia`.`lasteditedby`
LEFT JOIN `fb_tratamento_fisiot` AS `fb_tratamento_fisiot_3` ON `fb_tratamento_fisiot_3`.`user_id_fisiot` = `fb_sessoes_fisioterapia`.`user_id_fisiot`

As you can see, that's LEFT JOIN'ing the same table 6 times. So, as an example, let's say each join produces 10 rows. That'll be 10 * 10 * 10 * 10 * 10 * 10 rows in the SELECT's result set. A million rows. Which then get whittled down by the DISTINCT.

There's no way we can improve the performance on that. The CDD element was not designed to work on non-normalized data. It expect to be working with normalized tables, so the CDD is a join to a different table, which has an FK (foreign key) to the "observed". Like the traditional "states and cities" example. A states table, and a cities table, with the cities table having an FK to the cities table to identify which state they are in (like state_id). So when you select a state, the CDD then displays from the cities table "WHERE state_id = XX".

-- hugh