It's a little tricky to describe this one, but it's actually an old issue I reported way back in 2010:
http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_item_id=20950&start=0

Working with the SQL Optimisation Working Group this week reminded me about it and I decided to take a fresh look at it again and see if I could get it updated for Joomla 3.

So I spent the better part of the last two days going through it and optimizing it quite a bit over what I had previously and I hope the results can speak for themselves (I don't have any larger sites than the example one I've been working with this week which has about 4500 articles).

Issue it Tries to Solve (Mainly: Speed/Efficiency)

Back in 2010, Joomla 1.6 was still in progress I believe but it was close to being complete and so I had started using it to develop the website for the college I work for (I had been waiting for the new ACL system to be able to do that effectively).

One thing that seemed to be missed during the development process of JAccess that I noticed at the time, was that the number of queries / speed ended up dropping dramatically when the user logged into the backend was not a Super Admin...if the user was an Administrator or any other User Group which has its permissions thoroughly checked by JAccess the number of queries and slowdown in speed was fairly apparent. This issue also affects the frontend too.

In that early investigation I found out that the queries used by JAccess were pretty slow and there was quite a bit of repetition in data that was being retrieved from the database so extra time was required when JAccess had to do any sort of significant work.

I ended up crafting a set of additional methods that could be used to preload two sets of data:
1. Component Rules (for any components a user may have installed). This list is usually fairly small and doesn't grow too much so preloading it is a great idea and saves a lot of queries by itself.
2. Asset Rules for "Types" for example, if I'm going to be using JAccess to check 'com_content.article.1', 'com_content.article.2', etc. it makes sense to go ahead and load these up in bulk all at once from the database rather than querying for each set of data individually.

I believe the first one is fairly straightforward.

The second one though can be a bit more complicated and is what I ended up spending the bulk of my time on.

In my initial version from 2010, I ended up retrieving all of the assets in bulk and at the same time I went ahead and created the JAccessRule instances for each of them.

Looking at things more closely here in 2015 I could see that approach would definitely cause issues quickly because during my profiling I could see that creating all of the JAccessRule instances up front ate up a lot more memory than I was expecting. So I ended up changing things around a bit and made the JAccessRule loading more just-in-time than the previous approach.

Preloading still takes a bit more memory (particularly with smaller Joomla installations) but I believe overall the zippier response times more than make up for the extra memory that would be used.

What it's not for (Yet) - Super Large Sites (with > 10,000 Content Items for a single extension)

I don't know exactly what the distribution in usage is for Joomla sites (in terms of size).

Preloading should work fairly well for a large majority of sites I feel (particularly any that have a decent amount of content). This would be in the several thousand content items range.

At the moment I'm not sure how this would perform on a super large site with a few million articles.

One of the drawbacks with my current approach is that I'm essentially doing a flat inclusion of all rules for an extension once an individual asset is attempted to be checked in JAccess (so for example, if we try and check "com_content.article.1" this will cause JAccess to load up all asset information for "com_content.article." (all articles) and "com_content.category." (all content categories). For a super large site with a few million articles this is probably not the best approach to take...essentially what we'd need to do is make each extension (or JAccess somehow) aware of exactly all of the items that are going to be displayed on the current page and allow the preload mechanism to retrieve only the information for just those items from the database in bulk (rather than everything for that particular extension).

However, if I'm correct in assuming that probably the majority of sites have less than a few thousand content items, the current solution will save a whole bunch of processing time for a lot of people.

How to Test

Testing this should be relatively easy.

First I would turn on debugging in the backend (that way you can see some of the profling information and can see the before/after difference for the number of queries executed).

If you don't already have an Administrator user then you'll need to create one (a Super Admin will bypass JAccess completely and you won't notice any difference in performance).

Go ahead and logout and then login as the Administrator user.

First take a look at a few pages in the administration area (below I used the Default Dashboard Page after logging on, the Add New Article screen and the Article List View). With the profiling information you can note the timing, memory and the number of SQL queries that were run and save that somewhere in a text file on your end.

Now with the Patch Tester component installed you would just apply the patch for this Pull Request and then go ahead and run through testing the same pages you had just tested prior to applying the patch.

Note the differences...and hopefully everything is positively affected by the patch :-).

Example Results:

The timing info from the next few sections are from PhpEd's Profiler Tool.

Add a New Article with Current JAccess (with about 1100 categories):

When I had Joomla debugging enabled, initially with all of the example categories I had added in I could no longer create a new article (or category). With debugging turned off the amount of time it took to load the page was still fairly high, but at least it loaded.

Timing Summary: 10.66 sec

Memory Summary: 13.90 MB

SQL Queries: 9.634 sec

Add a New Article with JAccess Preloading (with about 1100 categories):

With preloading you can see it's much faster.

Timing Summary: 772.0 ms

Memory Summary: 15.87 MB

SQL Queries: 79.48 ms

Normal JAccess (Loading 100 Articles in the Backend):

Timing Summary: 1.907 sec

Memory Summary: 10.42 MB

SQL Queries: 1.324 sec

New JAccess with Preloading (Loading Same 100 Articles in the Backend):

This one here illustrates how in the example site I had on my end (with 4500 articles) can use up a little more memory even when only 100 articles are being displayed.

Timing Summary: 1.032 sec

Memory Summary: 16.57 MB

SQL Queries: 281.3 ms

Joomla Profiling Examples:

Default JAccess (Admin Dashboard Page):
Time: 472.6 ms
Memory: 9.50 MB

Database queries total: 171.7 ms
55 Queries Logged 171.7 ms

JAccess with Preloading (Admin Dashboard Page):

Time: 475.7 ms
Memory: 9.27 MB

Database queries total: 58.9 ms
36 Queries Logged 58.9 ms

Default JAccess (Articles List View w/1 Article):

Time: 722.0 ms

Memory: 9.06 MB

Database queries total: 188.5 ms
52 Queries Logged 188.5 ms

JAccess with Preloading (Articles List View w/1 Article):

Time: 378.1 ms
Memory: 8.91 MB

Database queries total: 27.7 ms
34 Queries Logged 27.7 ms