Re: [Openinteract-dev] fetch_group and security

[Chris W. <ch...@cw...>]

* 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.