Re: [Bodington-developers] Performance of ACL Display

[Matthew B. <mat...@co...>]

Peter Crowther wrote:
>>From: Matthew Buckett 
>>SELECT * FROM objects WHERE id IS NULL
>>
>>This is the query that is run when a connection is validated from the 
>>connection pool. On PostgreSQL you cannot have an index 
>>containing NULLs and so this results in a full table scan:
> 
> That's a pretty poor optimiser - the id column doesn't even *allow*
> nulls.  Presumably this clause was chosen as static analysis can reveal
> that it will return no rows.  The SQL Server optimiser doesn't even need
> to go to the index statistics; SQL Server 2000 presents it as a
> 'constant scan' in the optimiser, with zero CPU and zero page reads.

Is this a SQL Server optimization? If I were connected to an Oracle 
database I would do:

SELECT 1 FROM dual;

To test the connection (dual is a virtual table that always has one row).

If we used commons database pooling then it supports the idea that 
different databases having difference queries for checking that the 
connection is alive (rather than a hard coded one as we have at the moment).

The PostgreSQL people would probably argue it's a stupid query and 
shouldn't be run in the first place ;-)

> Out of interest, why PostgreSQL?  There are other RDBMSs these days that
> provide the required capabilities and are less... er... niche.  The cost
> to Oxford of tuning this code to work well with PostgreSQL may well be
> higher than the cost of switching to an alternative RDBMS.

For an open source product working well with other open source products 
is normally seen as a good thing (if we exclude Java for a sec). So I'd 
say that even if it isn't the best thing for Oxford at the moment it 
benefits the Bodington community in the long run. And it's never a bad 
thing to have the ability to run on multiple databases.

Although buying an expensive database (like Oracle/MS SQL) and then not 
using any of it's clever bits (functional indexes, etc) seems like a bit 
of a waste.

>>The other big killer is the calculation of the size of the 
>>"special" groups:
>>
>>SELECT count(*) FROM objects, users WHERE type = { } AND id = user_id 
>>AND ( special_groups_a & ({ }) <> { } )
>>
>>One option would be to special case the ACL code of that it always 
>>assumes the special groups are too big (easy and quick).
>>
>>The other option is to replace the special groups with normal ones.
> 
> 
> Given the lack of bitwise operators in HSQLDB and the inability of

I have a patch in the HSQLDB queue that fixes this.

> SoftCache to handle cached results where bitfields are involved, it may
> make sense to replace the special groups entirely.  It rather depends on
> the queries involved - do we know what they are?

The special groups are allusers, allstaff, allstudents, and maybe more. 
It's the groups that normally have 1000s of users and provides a very 
fast way of checking if the user is a member. It does this check without 
a database hit as the group information is stored in the user row. But 
maybe this is premature optimization?


>>NB: Changing the connection check and making sure special groups are 
>>never expanded changes the acl display page load time from around 4 
>>seconds to less than half a second.
> 
> Amending the connection check is a no-brainer, I agree; the trick is to
> amend it so that performance on other RDBMSs with different optimisers
> is not affected.

commons database pooling is probably the better answer as we also get 
the ability to recover from database restarts.

> By the way... it's great to see someone profiling execution times and
> tracking down the performance bottlenecks.  Assuming the tools in
> Bodington are going to use the same database schema for some time to
> come, it would be well worth the community doing this for the
> frequently-used or unacceptably slow tools in order to optimise them in
> a similar way.  I don't believe that Bodington is inherently slow; I
> *do* believe there are a small number of hot-spots that would make a big
> difference if optimised.

This is where having a nice database with good support for profiling 
makes things easier (Oracle, MS SQL). As turning on statement logging on 
our live server would probably give an unacceptable performance hit (but 
I'll look into this).

-- 
+--Matthew Buckett-----------------------------------------+
| VLE Developer, Learning Technologies Group               |
| Tel: +44 (0) 1865 283660 http://www.oucs.ox.ac.uk/       |
+------------Computing Services, University of Oxford------+