Guys, -The ARSystem does not have a built-in Query Optimizer, but one can change server configuration settings that could influence the respective database query optimizer that interprets the SQL (passed to it from Remedy, many of these config seetings can be manually set through the ar.conf file directly such as removing the order by clause from most of remedy generated sql...most statements by default are order by 1 (which is the request id column), most times there is no need to mess with with them (Direct SQL statements are passed as is)
-Each supported database does have a query optimizer (e.g. Oracle - Rule-Based (<=9i supported), Cost-Based Optimizer) -The database optimizes the statement (or thinks it optimizes a statement - the optimiser isn't always right...lots of variables at play) -If an index is created through the admin tool, the arsystem application server issues the respective create index statement, whilst also updating a number of Remedy data dictionary tables (e.g. schema_index) so that Remedy knows about the index when you view form properties in the admin tool or for when you export a form and import (in place) or to another remedy server - this ensures the index travels with the form -If you create an index at the database level independent of the admin tool (through for example - sqlplus), the index exists and is noted in the relevant database dictionary table (such as dba_indexes, user_indexes in Oracle). Obviously Remedy knows nothing about it, so if one was to view the indexes in the admin tool for the form, it wouldn't show up, nor would the index be created on another server it the form was exported/imported..BUT -Yes, absolutely the database optimizer will evaluate all appropriate query plans, which may or may not involve the index created at the database layer by the DBA/Developer (which Remedy may or may not know about) etc..so an index created at the database level unknown to remedy (arsystem) can be used by the respective database optimizer(either negatively or positively - optimizer not always right, but one expects it is more often than not!) I've seen the odd occassionally where people are in twist having completed a remedy migration from one server to another, and it has turned out that the dba created an index at the db level at some point that wasn't brought across..this situation happens for a variety of reasons...dba knows nothing about remedy, dba wants to improve the parameters passed to a standard remedy generated create index statement (and isn't aware of the ardb.conf feature or couldn't be bother with it) -Remedy (BMC's) recommendation is always through the admin tool (supported method), it you need to leverage the ardb.conf do so re: leafs...but it's your call...if you db it at the db level just make sure you've documentated it for future reference (by you or your peers) HTH, Kevin Quoting Rick Cook <[EMAIL PROTECTED]>: > True - sort of. ARS does not have its own query optimizer - it uses the one > in the attached DB. So while it's technically accurate to say that Remedy > doesn't optimize queries, in practice that's not as true, because the DB > does it for Remedy, so the queries are optimized. > > If you meant to say that ARS is oblivious to indexes created directly in the > DB, that's correct. Would the DB use indexes that only it knew about to > optimize queries sent it by ARS? That's the 64-bit question. > > Rick > > On 8/17/07, Robert Molenda <[EMAIL PROTECTED]> wrote: > > > > ** > > > > From what I have noticed, ARServer is oblivious to indexes and does not > > optimization any SQL statements for usage of indexes. > > > > > > > > That is why it is up to the developer(s) to use queries appropriately > > > > > > > > > > > > > > > > *Thanks-n-advance*; > > > > *HDT Platform Incident / Problem Manager & Architect* > > Robert Molenda > > *IT OS PA* > > Tel: +1 408 503 2701 > > Fax: +1 408 503 2912 > > Mobile: +1 408 472 8097 > > [EMAIL PROTECTED] > > > > Quality begins with your actions. > > > > > > ------------------------------ > > > > *From:* Action Request System discussion list(ARSList) [mailto: > > [EMAIL PROTECTED] *On Behalf Of *Joe D'Souza > > *Sent:* Friday, August 17, 2007 10:55 AM > > *To:* arslist@ARSLIST.ORG > > *Subject:* Re: ITSM 7 indexes > > > > > > > > Hi John, > > > > > > > > The ARS has no way of knowing what fields are indexed if you do not do it > > from the Admin Tool and hence the indexes created from the database level > > will not get utilized from the application except if you run a Direct SQL > > from ARS.. Direct SQL's are not really evaluated from the ARS and the ARS > > throws whatever SQL statement you type directly to the database and fetches > > results if any. So the database would use those indexes. > > > > > > > > Joe > > > > -----Original Message----- > > *From:* Action Request System discussion list(ARSList) [mailto: > > [EMAIL PROTECTED] Behalf Of *john rosquist > > *Sent:* Friday, August 17, 2007 10:27 AM > > *To:* arslist@ARSLIST.ORG > > *Subject:* ITSM 7 indexes > > > > ** > > > > I am looking at adding indexes to my ITSM 7 application (ARS7.02) on SQL > > 2005. Does anyone know if it is still recommended to added the indexes via > > the admin tool, or can you use the underlying database to add indexes. I > > remember from the tuning class that you should use the admin tool, but I > > want to see if this is still the case. > > > > > > > > Based on the SQL profiler result, adding more information to the leaf, > > should improve performance. > > > > > > > > Note( T688 is CTM:People, 1,18,19,56 are company, ln, fn, Phone). > > > > > > > > CREATE NONCLUSTERED INDEX > > > [_dta_index_T688_5_19947593__K46_K52_K53_K88_1_4_7_15_16_18_28_50_51_54_56_59_69_87_106_111] > > ON [dbo].[T688] > > ( [C1000000001] ASC, [C1000000018] ASC, [C1000000019] ASC, [C1000000056] > > ASC) > > INCLUDE ( > > > [C1],[C4],[C7],[C179],[C200000006],[C200000012],[C260000001],[C1000000010],[C1000000017], > > > > > [C1000000020],[C1000000022],[C1000000025],[C1000000035],[C1000000054],[C1000000346], > > [C1000000846]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, > > IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] > > > > > > > > Any ideas? > > > > > > > > Thanks, > > > > John Rosquist > > > > Senior Consultant > > > > Windward Consulting Group > > > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the > Answers Are" > ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
