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: arslist@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 at www.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 at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
