Thanks for all the replies. It does give me more insight on how this works.
I was talking about the Run If qualification and now I realize a != won't do a full table scan from that. So I'll have to look at the custom filters and see how the push / set fields are set. Thanks On Sep 17, 1:57 pm, Guillaume Rheault <guilla...@dcshq.com> wrote: > Some of these recommendations/rules of thumbs are many many years old, and > don't necessarily apply with the latest versions of databases. For instance: > > >* Do not index fields holding 500 or fewer tickets. ARS forms with > >fewer than 500 tickets will ignore the index. > > Whether an index is used or not depends on the optimizer. Oracle 10g and > newer only use the cost based optimize (the CBO); the rules based optimizer > (RBO) has been deprecated in 10g. If the block size of the database is large > (32k the max for 10G), then the optimizer may choose a full table scan > instead of the index scan for a table that is 1000 rows. > > >* Index a maximum of 5 fields per form. Speed is gained on searches > >by using indexed fields, but creations, >modifications, and deletions are > >slowed by the indexing process. > > Again, this was true with old databases. This does not apply for newer > databases. The ITSM 7.5 app suite has more indexes than five in many forms, > how about that? According to this rule, BMC's indexing of the ITSM > applications is bad. Of course the design is not bad, it simply happens that > this rule does not apply anymore for newer databases. > > here is good FAQ primer for Oracle: > > http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ > > Check also this article from the northern california oracle users group: > > http://www.nocoug.org/download/2007-08/SystematicOracletuning.pdf > > >Finally, remove unused indices. Use Developer Plus's analyze function to > >assess index usage. > > I would never ever rely on developer plus for index usage analysis!! Oracle > has has it's own ways to determien if an index is used. Check out the FAQ and > white paper above. > > This conversation is focused on Oracle 10g, but I am sure SQL Server 2005 and > the latest version of DB2 have similar features. > > -Guillaume > > > > -----Original Message----- > From: Action Request System discussion list(ARSList) on behalf of Meyer, > Jennifer L > Sent: Wed 09/16/09 1:37 PM > To: arsl...@arslist.org > Subject: Re: Filter qualification using != > > Lee, > > When you make your qualification statement, try to make the comparison as > concise as possible early in the statement. The order of precedence is > equal, greater than, !=, like. "Like" and != will cause a table scan, but if > you have narrowed down your search before you get to that point, it will scan > through fewer records. > > The following "Use Indices Appropriately" article used to be posted on a SQL > developer page on the internet, but I don't see the original anymore. I've > been maintaining my own version in my documentation since I started working > with Remedy. I'm not the original author, I just like it so much I've kept a > personalized version around as a reminder and modified it to send portions to > my users as the need arises. > > There may be some answers for your larger issues here. > > Any search against the database is a performance hit. > This includes table refreshes, FlashBoards, automatic reports, search menus, > anything that uses a qualifier to find data in the database. Thus, limit the > number of searches your users will perform and speed their performance by > indexing fields, chunking data, and disabling unqualified searches. > > Use the Analyze Forms feature of Developer Plus to check for database hits-be > sure only to use this feature during off hours! > > Sync Search Database after every migration. Do this every time the > production configuration changes. Development is pointless if the > development server is out of sync with the production server. > > Use Indices Appropriately > One of the best ways to improve ARS performance is to implement indices on > frequently searched fields. When an ARS search is submitted, either a Table > Scan or an Index will be used. A Table Scan searches through all of the > pages within a database. However, if an indexed field is selected, only the > pages associated to the index are scanned. > > For example, given a database with one million (1,000,000) entries, a Table > Scan will search approximately 55,000 pages, while an index field will scan > about 8 pages. > In another example, a search on 50,000 tickets took nearly 40 seconds. When > the field was indexed and the search resubmitted, the search took only 2 to 5 > seconds. > > When to use an index: > * Do not index fields holding 500 or fewer tickets. ARS forms with > fewer than 500 tickets will ignore the index. > * Index a maximum of 5 fields per form. Speed is gained on searches by > using indexed fields, but creations, modifications, and deletions are slowed > by the indexing process. > * Do not place more than 3 fields within an index. (Keep clustered > index keys as small as possible and place the most restrictive field first.) > An index allows up to 16 fields to be included in the index, so common > searches can be used within the index, but if the user does not use the first > field in the index, the index will be ignored and a table scan will begin. > * Use caution when creating Unique indexes. Unique implies exactly > that-no other ticket with the same value can be entered into that table. > Thus Request ID is unique, but Status is not. > * Make searches easy for users. Provide the users with Menu choices or > pre-defined reports. > * Set the Search By Example (SBE) selection to "Equal" or "Leading." > Indexes don't work if SBE is set to "Anywhere." Wildcards may be used, but > if they are at the beginning of the keyword, then the index is ignored and a > Table Scan is used. > * Update Index Statistics on a regular basis. Ask your DBA. > * Do not allow unqualified searches. When a user submits an > unqualified search, a Table Scan is launched. > * Use guides to help users search. Guides can lead users to indexed > fields, thus speeding up the search process. > * Know when Table Scans will fire. > o ARS forms with fewer than 500 tickets. > o More than 20% of entries in a table are requested. > o Unqualified searches. > o Searches on non-indexed fields. > o Searches that begin with a wildcard symbol on indexed fields. > o Searches on indexed fields that perform an arithmetic operation on > the field. > o Searches on indexed fields that perform a NOT. > o Searches on indexed fields that are non-selective e.g. Indexed Last > Name, First Name, Phone, but search request was for First Name and Phone only. > > Finally, remove unused indices. Use Developer Plus's analyze function to > assess index usage. > > Jennifer Meyer > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] On Behalf Of lee > Sent: Wednesday, September 16, 2009 12:52 PM > To: arsl...@arslist.org > Subject: Filter qualification using != > > Hi, > > I have some filters that use the qualification != $NULL$. > In the documentation it says to avoid using != as it will do a full > table scan. > > My question is 'does having != $NULL$ AND Field1 = Organization' make > the full table scan smaller? > > Or > Is it better to do a != $NULL$ or LIKE 'CU%" > > When I try the LIKE qualification, it seems a lot slower (timeout > message) compared to the != $NULL$ > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > > E-mail correspondence to and from this address may be subject to the North > Carolina Public Records Law and may be disclosed to third parties by an > authorized state official. > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"- > Hide quoted text - > > - Show quoted text - _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
