[rt-devel] Users::WhoHaveRight and Sybase

[Audley, Christopher]

We have been pushing with all our might to get RT3 deployed on Sybase.  We have had it up and running for a while but the performance just didn't cut it.  The most glaring problem was trying to create a new ticket.  The ticket submission form would take 5-10 minutes to come up.  All of this time was spent in the Users::WhoHaveRight, even after upgrading to rt 3.0.3.

The bulk of this problem has been caused by our inability to find a solution to the distinct problem.  Like Oracle, Sybase won't take a distinct on main.* for Users because of the TEXT fields.  Unlike Oracle, there doesn't seem to be a cute trick to get around the problem.  We have compensated by modifying SearchBuild::_DoQuery to discard duplicates in the query results.  We are forced to consider the distinct clause is a nice but unnecessary optimization.

However, this kills performance in Users::WhoHaveRight.  If I take the generated query and limit the select to main.Name and add distinct, the query time drops to 20 seconds.  However, even twenty seconds is too slow, users won't use the system if thats the best we can do.

The solution we found is to break WhoHaveRight into two steps.  First, get the groups that have the right, via a new method Groups::WithRight, followed by a query for the members of a set of groups, Users::WhoBelongToGroups.

Using this split query, we get access times down to less than 300ms.  I've attached the local/lib/RT/Users_Local.pm, local/lib/RT/Groups_Local.pm and local/html/Elements/SelectOwner that we are using, I'm curious if this method improves access times with other database engines.

Chris
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Groups_Local.pm
Type: application/octet-stream
Size: 2766 bytes
Desc: Groups_Local.pm
Url : http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20030625/522f5f88/Groups_Local.obj
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Users_Local.pm
Type: application/octet-stream
Size: 1340 bytes
Desc: Users_Local.pm
Url : http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20030625/522f5f88/Users_Local.obj
-------------- next part --------------
A non-text attachment was scrubbed...
Name: SelectOwner
Type: application/octet-stream
Size: 2001 bytes
Desc: SelectOwner
Url : http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20030625/522f5f88/SelectOwner.obj