* Andreas Nolte (And...@Be...) [011119 03:12]:
> Hey!
>
> now that we have our first larger OI application running for a month
> or so ( a trouble ticket system ), we now look into optimizing
> it. What we found is, that checking security of a larger result set
> takes a significant amount of time - which is no suprize, really,
> but lead to looking into the SQL used.
>
> Right now, fetch_group works like this:
>
> - read all records controlled by the where clause and the limit ( 1
> query )
> - check security on each record ( 1 query for each record, so maybe
> thousands )
> - return only those records, for which security fits ( return maybe
> only 10.. )
>
> What I would like to discuss here, is if a join with sys_security
> wouldn`t make sense here.
It might not be a bad idea to make that option available. Similar to
how we generate the ID WHERE snippet with SPOPS::DBI->id_clause(), we
could generate a security clause as well. It would only work for
DBI-stored objects.
That said, I think the best thing to do (since it would work for all
SPOPS objects) is to cache the retrieved security values
instead. (This has been on my TODO list for a LONG time.)
This could work in a couple of ways. First, we could cache all results
of a security query for a particular object. Second, we could cache
the calculated level for a particular object and user.
These could actually run in tandem -- the first time an object is
requested we fetch *all* of its security information and put it in the
cache. Then we just use the cache to determine the security level for
a particular user and cache *that* value as well.
Queries for an object used in every request (like themes or groups)
would have a *huge* benefit, and objects viewed often (top ten news
items, whatever) would as well.
We'd have to have a way to expire entries from the cache when security
is changed for a particular object as well. (This is important but
probably not critical since in most environments security would be
changed rarely on a particular object.)
I need to get the next versions of everything out and work on the
modified object relationship specifications. But caching is moving up
the TODO list.
Later,
Chris
--
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.
|
