Page loading issues

[davez]

Hi,

I've run into a list loading issue that I just can't resolve, and have been battling with it for quite sometime. I've brought the load time down from 1 minute to maybe 30 seconds. At times, it loads quickly just a few seconds. Oddly, this happens when the cache is turned on or off. It's hosted on SiteGround (shared), this host has always been excellent for me. I have the site on both a live site and a dev site (which are on different servers). I've worked with SiteGround and they don't see an issue with errors on their server.

The main issue is the loading of a list. Details:
- Presently it only has roughly 2000 rows.
- It has two direct joins (only one of which shows two columns in list view), two databasejoin elements and two user elements.
- The linkage to the joins keys in the database and are set up properly (it seems).
- In list view I removed the viewable elements from the joined tables.
- In the list view there is one calc element that does calculate on each load, but turning that off does not seem to help.
- I have made sure the database collation is the same.
- I have tried various cache settings, as well as turning off/on the cache in List > Advanced.
- I have turned off the loading of elements in list view that are not essential to the list search or display.
- optimized tables
- I turned on debug Joomla and Fabrik to simplify what I could find. There a number of duplicate queries. But fixing that only helped slightly.

Some of the above helped, but something else is eating up resources. I've never had this particular issue and have many Joomla/Fabrik sites.

I've added my site credentials to the Fabrik My Sites page. This is for the wic_permits table. The joined tables are wic_itinerary and wic_invoices.

What am I missing? Joomla debug is currently turned on on the DEV site.

https://scci.interactive-earth.com/admin/all-permits.html

Any hints of solutions would be greatly appreciated!

 

[troester]

Which exact Fabrik version?

 

[cheesegrits]

I noiced when I tried to save the Permits list on the backend, it barfsd with an error about auto-inc.

I'd like to look in PHPMyAdmin, but the credentials you gave don't work.

-- hugh

 

[davez]

Sorry about that the password should now work properly.

Oh, oops. I had just updated the db from the live site (always don't update the fabrik connection table). I messed up, so the auto-increments and table modifications failed to appear. I fixed that. It's running smoothly now.

It's updated to the current version (not github).

 

[cheesegrits]

OK, try now.

I added a couple of missing indexes, involving elements used in joins / your subquery. That helped a little bit, but not much.

The main problem was that the 'permit_id' field on your invoices table was a varchar. So the LEFT JOIN from that to permits.id was trying to join an INT to a VARCHAR, so "range check on each record" ensued, slowing things down horribly. I changed it to INT (after checking that it only contained integer values, nulls or empty strings), to match the 'id' it is joining to, and that seems to have greatly improved things.

That took about half an hour. It'd be much appreciated if you could take out a standard sub for a month to contribute to that time.

-- hugh

 

[cheesegrits]

BTW, the way to debug these things is to add ?fabrikdebug=1 to the URL, then copy any queries you find in the debug output - especially from "table:mergeJoinedData get ids" and "list GetDataermits" - paste them into phpMyAdmin and run them. When you find one that takes a long time, prepend "EXPLAIN " to the query, and look at that output. It's a little cryptic, but Google can help you decipher the output. For instance, in this case the mergedJoinedData query yielded that "range check on each record" for the invoices join, and a quick google for that gave a couple of Stack Exchange posts talking about mismatched field types on joins.

BTW2 - the reason J!'s cache settings don't effect it is that we run our own query cache, which you can only turn off by specifically setting "cache" to No in the list settings. We use the actual query itself as the cache id. So if you run the exact same query twice in a row (within the normal J! cache session timeout) it'll used cached results. That'll stay cached until it times out, or until you edit or add a row in the list, when we'll invalidate the cache for it.

-- hugh

 

[davez]

Nice. It seems to be working (but I've said that before with this particular list). Thank you...and I just did a monthly subscription. I thought I had fixed those permit_id data type VARCHAR to INT issues. That's the first place I usually go when this kind of issue pops up. Maybe I missed one or maybe it reverted when I saved the element from within Fabrik??? I recreated on the live site and it's working great. Thanks for the heads up on the fabrikdebug and "explain" (never used that). I do use fabrikdebug, but I couldn't get this one cleared up.

 

[cheesegrits]

If Fabrik did change the type, it would have asked you, and you would have had to say "Yes" before we did it. That's solid code in the element save() method, that I'm 100% confident about. We check the existing type in the table, and the type it's being saved as, if different we prompt for approval.

-- hugh

 

[cheesegrits]

Thanks for the sub, btw.

-- hugh