RE: [Bodington-developers] Performance of ACL Display

[Peter C. <Pet...@me...>]

> From: Matthew Buckett=20
> SELECT * FROM objects WHERE id IS NULL
>=20
> This is the query that is run when a connection is validated from the=20
> connection pool. On PostgreSQL you cannot have an index=20
> 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.

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.

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

Given the lack of bitwise operators in HSQLDB and the inability of
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?

> NB: Changing the connection check and making sure special groups are=20
> never expanded changes the acl display page load time from around 4=20
> 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.

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.

		- Peter