David, As Axton explained, the point is to have, as much as possible, a query with a field that is indexed and making sure that index is used, so no table scan is performed. One way to make sure an indexed field is used in a query, is to create an active link (or multiple) that fires on search to check whether an indexed field has been set (or a combination of fields). Or you could even default fields when doing a search, such as status, assignee fields, categorization, etc, with other active links firing on search.
Guillaume From: David Sanders Sent: Tue 04/17/07 9:17 AM To: arslist@ARSLIST.ORG Subject: Re: Permissions and Database Performance ** Hi Axton Thanks for the quick response, and putting my mind at rest about the positioning of the various clauses in the SQL query. I assume positioning should only then be relevant if compound indexes are defined, and I should have more faith in my DBMS to 'do the right thing'. So in the example I gave, I assume that indexes on the Submitter and Assignee fields would help (selective data), but an index on the Assignee Group field would probably not help as the data is not very selective. All queries do contain other WHERE clauses that should reference indexed fields. Again, if anyone has done any benchmarking on the effects of permissions on performance, I would be interested to learn the results. TIA David Sanders Remedy Solution Architect Enterprise Service Suite @ Work ========================== ARS List Award Winner 2005 Best 3rd party Remedy Application See the ESS Concepts Guide tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk/ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Axton Sent: Tuesday, April 17, 2007 12:13 PM To: arslist@ARSLIST.ORG Subject: Re: Permissions and Database Performance The relative positioning of the pieces of the where clause should have no impact on how the query is parsed by the database. The db will take the query, parse it, then decide the best way to execute it based on what it (the db) considers the cheapest alternative. The like comparison will introduce scans (range and or table), but if the query also includes where clause pieces that use index fields, they will (should) use the index (depends on what the db thinks is cheapest). Think of it like this (scenario 1): - (w/o row level locking) user performs an unqualified search, resulting in a table scan - (w row level locking-multiple) user performs an unqualified search, resulting in a table scan - (w row level locking-single) user performs an unqualified search, resulting in an index scan if the assignee/dynamic group fields are indexed Think of it like this (scenario 2): - (w/o row level locking) user performs a search on an indexed field, resulting in an index scan - (w row level locking-multiple) user performs a search on an indexed field, resulting in an index scan, further qualified by a range scan (against the rowset returned by the index) - (w row level locking-single) user performs a search on an indexed field, resulting in an index scan, further qualified by an additional index scan (against the rowset returned by first the index) To summarize: - will row level locking make unqualified searches more expensive - a little, due to additional parsing requirements (cpu) - will row level locking make qualified searches more expensive - a little, due to additional parsing requirements (cpu) - if the app is well designed, and unqualified searches are not performed against large data sets, the impact should be negligable - if you are using oracle, you can alleviate a lot of the hard parsing by setting the instance parameter CURSOR_SHARING to a value of similar or force - reducing hard parsing will reduce cpu overhead and forcing the use of bind variables will reduce the memory requirements (due to a smaller shared sql area) Axton Grams On 4/17/07, David Sanders <[EMAIL PROTECTED]> wrote: > ** > > > > 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,C > 700031513,C3 > FROM T167 > > WHERE (((T167.C2 = 'fred') OR (T167.C4 = 'fred') OR > > > > (T167.C112 IN > (';73402;',';73401;',';73400;',';73419;',';73416;',';73801;',';73415;' > ,';73420;',';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 ESS Concepts Guide > > > > tel +44 1494 468980 > > mobile +44 7710 377761 > > email [EMAIL PROTECTED] > > > > web http://www.westoverconsulting.co.uk/ > > > > __20060125_______________________This posting was submitted with > HTML in it___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" __20060125_______________________This posting was submitted with HTML in it___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
