• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Very slow query

HyperOsmar

Member
Dear Team, Good morning ...

I'm using Joomla! 3.7.2, Fabrik 3.6, PHP 7.0.15 and Apache 2.4.18.

I created a list (name "RESEARCH"). Within this list were created several groups (without repetitions).

The form part works normally, but when I want to view the report data, the system takes a long time to show the result.

This delay is generating my client's dissatisfaction. The other reports work normally.

What can I do to decrease the time for consultation?

What can I do to make my client see the results faster?

Where should I start?

If it is to help and if you want, I can create a system access account.

Thank you so much.

Osmar
 
OK, the problem is that none of your database joins or cascading dropdowns are using unique keys for the 'value'. So, for example, insteed of value 'id' and label 'email', or value 'id' and label 'nome_completo', they are all using the same element for the 'value' that they use for the 'label'. So email and email, or nome_completo and nome_completo.

You need to redesign it to use the 'id' as the value for all joins or CDD's.

As you already have existing data, that's going to be a tough task. You'll need to update all your existing data to replace those non-unique values with the ID's they should be using. So for example, if you have a join called 'email' on 'table1' for the 'email' field from 'table2', so you need to change the value of 'email' on table1 from being the actual email, to being the id of that row in table2, you'll need to do this on table1 ...

update table1 left join table2 on table1.email = table2.email set table1.email = table2.id

... and then change the 'value' in the Fabriuk element for that join to be 'id'.

The problem is, if you have any situations where the value you are currently using (like "name") is not unique in the joined table, you have no way of knowing which row it was supposed to join to. Which is why you should always use the PK of the other table (which we put "recommended" by in the dropdown) as the value of a join, so it's always unique.

-- hugh
 
Dear Cheesegrits,

I am new to database. My knowledge is very few.

I understand part of your explanation, but I do not know how to execute these modifications without losing the data recorded.

I made this system for a charity and health research, I did not charge anything for the service, but I do not want to leave them at hand.

Can you help me???...

How much do you charge for the service ??? ...

I really need your help.

Translated by Google...
 
I do not know how to execute these modifications without losing the data recorded.

You would have to execute queries like the example I gave by hand, in something like phpMyAdmin, to update the existing date from the 'label' to the unique ID of the rows, for all of your joins and CDD's.

I can help, but it would be on an hourly rate. I'm also worried that as your existing data is using non-unique foreign keys, establishing the correct row to join to is going to be difficult.

For instance, anywhere you have joined to a table where the 'name' is not unique (either multiple rows for the same person, or multiple people with the same name), there's no way of knowing which row it should have joined to. And that's what's causing your slow query. Joins that should only produce one match are producing multiple matches, and for each join this happens with, the result is an exponential increase in the size of the result set.

-- hugh
 
You would have to execute queries like the example I gave by hand, in something like phpMyAdmin, to update the existing date from the 'label' to the unique ID of the rows, for all of your joins and CDD's.

I can help, but it would be on an hourly rate. I'm also worried that as your existing data is using non-unique foreign keys, establishing the correct row to join to is going to be difficult.

For instance, anywhere you have joined to a table where the 'name' is not unique (either multiple rows for the same person, or multiple people with the same name), there's no way of knowing which row it should have joined to. And that's what's causing your slow query. Joins that should only produce one match are producing multiple matches, and for each join this happens with, the result is an exponential increase in the size of the result set.

-- hugh


Cheesegrits, good afternoon ... (at least here not Brazil ... kkkk),

"I can help, but it would be at an hourly rate. I'm also concerned that since your existing data is using non-unique foreign keys, establishing the correct row to join will be difficult."

And how much would your fees be?

Hugs ...

Ps: where are you from?
 
Hey, I gather y'all sorted out the hours, so @HyperOsmar, you'll need to add details to your My Sites for me to access phpMyAdmin.

I suggest the way I do this is to make an Akeeba backup and install it here, and work on the data here, rather than working on your server. When finished, I can push changes back up to your site.

How frequently does the data change?

-- hugh
 
Hey, I gather y'all sorted out the hours, so @HyperOsmar, you'll need to add details to your My Sites for me to access phpMyAdmin.

I suggest the way I do this is to make an Akeeba backup and install it here, and work on the data here, rather than working on your server. When finished, I can push changes back up to your site.

How frequently does the data change?

-- hugh



Cheesegrifts, good morning ...

It's no problem for you to work directly on the server, I've already done a full backup of the server.
Regarding the frequency of data, I put it in maintenance mode.
Make yourself at home, it's your house.
I just need you to guide me to the end of the process so I do not make this kind of mistake anymore, okay ????
DB data is now available on My Sites.

Hugs.
 
Dearest @Cheesegrifts, good evening ...

I'm worried about the progress of the process.

Even though it is a system for a philanthropic institution, I need it to be working, and the staff are charging me.

I await your return.

Hugs.
 
Hey - we need to discuss your site's setup.

There's some stuff I don't understand about your joins, that may need some more normalization.

For example ...

The 'area_conhecimento' element joins to the 'cad_area_conhecimento' table, using 'area_conhecimento' as the value. But there are hundreds of rows in that table with the same 'area_conhecimento', and only about half a dozen different 'area_conhecimento' names.

So is that 'area_conhecimento' element on 'cad_pesquisas' actually supposed to identify a specific row on the 'cad_area_conhecimento' table, or is it purely for selecting a 'area_conhecimento' name?

If the latter, then the way to do that would be to have a 'area_conhecimento' table with the 5 names in, and join to that from both the 'cad_pesquisas' and 'cad_area_conhecimento' tables.

But this is a good example of why your list / form is so slow. That join currently matches hundreds of rows, instead of just one.

I'm going to probably need you available on Skype so I can talk through these issues and make decisions while I repair this stuff.

-- hugh
 
@cheesegrifts,

To facilitate registration, the initial idea is for the user to select the "?rea de conhecimento" and by cascade the options of the other fields will arise conditionally (only this, facilitate for the user).
I really do not know any other way to make a conditional cascade.

As for Skype, yes we can use it.
What do you think we'll score for Saturday, around 4:00 p.m. (Alabama time) ???

Hugs.
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top