Hi David, It might be just easier to ask the DBAs to look into this. Assuming you are on Oracle, the SQL Tuning Advisor can be used exactly for this purpose. BTW, Oracle 10g has automatic SQL tuning features that should be taken advantage to make performance tuning much easier:
You can view an introduction to automatic SQL tuning features here: http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i34782 Guillaume From: David Sanders Sent: Tue 04/17/07 12:32 PM To: [email protected] Subject: Re: Permissions and Database Performance ** Hi Guillaume As I said in my message, all queries also contain WHERE clauses referencing indexed fields. What I am looking for is information about how the way permissions are implemented affects the SQL generated and system performance. and if anyone has benchmarked this. Thanks 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 Guillaume Rheault Sent: Tuesday, April 17, 2007 3:35 PM To: [email protected] Subject: Re: Permissions and Database Performance 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: [email protected] 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: [email protected] 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 __20060125_______________________This posting was submitted with HTML in it___ __20060125_______________________This posting was submitted with HTML in it___ __20060125_______________________This posting was submitted with HTML in it___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
