Hi List
I'm looking for any advice or comments from you database performance gurus
out there on the way ARS enforces access to data through permissions and the
effect on performance.
Take an example of a ticket form using row level access where the
permissions on the Request ID field are set to Assignee, Assignee Group and
Submitter. For an Admin user there is no problem, but for non-Admin users
the systems adds SQL similar to the following to the beginning of the SQL to
enforce permissions:
SELECT T201.C1,C8,C700037500,C700037000,C700034003,C700003000 FROM T201
WHERE
(((T201.C2 = 'fred') OR (T201.C4 = 'fred')) OR
((T201.C112 LIKE '%;''fred";%'') OR ((T201.C112 LIKE '%;0;%') OR ((T201.C112
LIKE '%;73421;%') OR
((T201.C112 LIKE '%;73419;%') OR ((T201.C112 LIKE '%;73404;%') OR (T201.C112
LIKE '%;73401;%')))))))
AND. rest of query.
That's checking whether the user (fred) is the assignee or submitter, then
doing a series of LIKE comparisons against the Assignee Group field for
every group that fred's a member of. This SQL is for a server config where
the Allow Multiple Assignee Groups is checked (the default). Does the
positioning of these blocks of query at the start of the SQL mean that
indexes on fields defined later in the query are less likely to be used, and
that table scans will be used? Would performance be improved if this extra
SQL were placed at the end of the query (after the clauses defined by
workflow using indexed fields)?
If the Allow Multiple Assignee Groups configuration setting is turned off,
the SQL generated to enforce permissions is changed to something like:
SELECT
T167.C1,C2,C3,C8,C700048508,C700019515,C700019565,C700500133,T167.C1,C700031
513,C3 FROM T167
WHERE (((T167.C2 = 'fred') OR (T167.C4 = 'fred') OR
(T167.C112 IN
(';73402;',';73401;',';73400;',';73419;',';73416;',';73801;',';73415;',';734
20;',';72502;',';72501;',';0;',';''fred'';')))
AND. rest of query.
Assuming that only a single Assignee Group is used and this setting can be
used, how much of an improvement in performance would you expect this to
give? Indexes on the Assignee, and Submitter fields should help, but what
if the data in the Assignee Group field is not very selective - will
indexing that field help? Should every field used to define access
permissions to a ticket be indexed?
Any comments from anyone who has investigated these performance issues or
done benchmarking would be appreciated.
TIA
David Sanders
Remedy Solution Architect
Enterprise Service Suite @ Work
==========================
ARS List Award Winner 2005
Best 3rd party Remedy Application
See the
<http://www.westoverconsulting.co.uk/downloads/ESS_Concepts_Guide.pdf> ESS
Concepts Guide
tel +44 1494 468980
mobile +44 7710 377761
email [EMAIL PROTECTED]
web http://www.westoverconsulting.co.uk
<http://www.westoverconsulting.co.uk/>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
