Thanks, Kevin. I remember a discussion with a Sybase DBA once who said that the way he could create the indexes at the DB level was more efficient than those created via Remedy, so he recreated the Remedy defined ones directly in the DB. It was also necessary for his DB replication process. I'm sure that some Oracle DBAs might have the same claim.
Rick On 8/17/07, Kevin Murray <[EMAIL PROTECTED]> wrote: > > 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:* [email protected] > > > *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:* [email protected] > > > *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"
