Rick, I agree and am guilty of this all the time...especially when working on an indexing strategy for an remedy app having some performance problems. It's a lot quicker creating/dropping indexes while investigating than going through the admin tool...especially when using debug tools that come with the respective database tool (e.g. AUTOTRACE with SQLPLUS)
There's plenty of reasons to do it (create indexes) directly at the db level..the ardb.conf is a method really to close the gap between what the arsystem does generically across all databases (simple create index statement with no elaborate storage clauses etc.) and the bells and whistles that one can get with the modern day relational database today..the dba you mentioned could have used the ardb.conf file (if it was a version of Remedy that supported it..think it came in around 4.x time)...the reason a tweak to the statement was required in your Sybase scenario was to setup some of the replication modes (transactional, snapshot) your database table MUST have a primary key constraint on it (Remedy Request ID columns (C1) by default are simply unique not null constraints - similar to a primary key but not the full deal) hence your (or the) DBA had to intervene to add the primary key constraint. Remedy use of relational databases is quite basic, I remember the days when studying RDBMSs in college and the first thing that you were taught was to have a a primary key constraint on a table...but here we are 37+ years since Mr.Codd I think invented the RDBMSs that some applications kinda fudge it. It's one area I'd wish Remedy's install script were improved...rather than to have to mess with a ardb.conf (in advance of creating something)...I've sincerely hope the re-vamped admin tool has the capability for advanced developers to interface better with the database as well...more often then not in my experience, customers pay some bucks for support with say Oracle or SQL Server, we install Remedy one day and it's nearly also left on that DB flavour , but yet we have to work *harder* to get it (Remedy) to work with some of the more impressive features of the underlying database. Later, Kevin Quoting Rick Cook <[EMAIL PROTECTED]>: > 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" > R ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
