The system is performing an anywhere LIKE search (T1908.C800021100 LIKE '%KSR000000304374%') because you have the field that holds the KSR set for QBE Match = "Anywhere" (in the Database section of the Field's definition).
Changing that to "Leading" should change the SQL to: T1908.C800021100 LIKE 'KSR000000304374%' which will use the index, but that will mean that you can no longer just enter the number 304374 to find the data. If you want to just enter the number you would have to enter %304374 to find the data. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of [email protected] Sent: Monday, November 21, 2011 9:40 AM To: [email protected] Subject: Trying to find out what workflow make the server do a search on HPD:Help Desk form ** When I search on a field the holds the Kinetic number (KSR) the action takes a long time to complete compared to the time it takes to search on the ICN number Below is what my DBA sent me. When searching by KSR it runs this sql which ignores the index: SELECT * FROM ( SELECT T1908.C1,C1000000161,C1000000018,C1000000019,C1000000000,C303497300,C1000000164,C7,C1000000217,C1000000218,C1000005261,C1000003009 FROM T1908 WHERE (T1908.C800021100 LIKE '%KSR000000304374%') ORDER BY C1000000161 DESC, 1 ASC ) WHERE ROWNUM <= 9001 When searching by Incident Num it runs this sql which returns in seconds: SELECT * FROM ( SELECT T1908.C1,C1000000161,C1000000018,C1000000019,C1000000000,C303497300,C1000000164,C7,C1000000217,C1000000218,C1000005261,C1000003009 FROM T1908 WHERE (T1908.C1000000161 = 'INC000000239686') ORDER BY C1000000161 DESC, 1 ASC ) WHERE ROWNUM <= 9001 Notice the difference in the clause after WHERE . When I run the first sql at the db level it takes between 60-90 seconds. When I run the modified sql below it returns in seconds just like the INC search: SELECT * FROM ( SELECT T1908.C1,C1000000161,C1000000018,C1000000019,C1000000000,C303497300,C1000000164,C7,C1000000217,C1000000218,C1000005261,C1000003009 FROM T1908 WHERE (T1908.C800021100 = 'KSR000000304374') ORDER BY C1000000161 DESC, 1 ASC ) WHERE ROWNUM <= 9001 Where would this SQL statement live in Developer Studio. 1. Ran client side logs on AL, Filters and the other boxes checked off and nothing show up in my client side logs 2. I have search and read several Active Links but none of them have the SQL statement 3. Did the same for filters 4. Searched menu guides Currently the DBA is out for the Holiday and can not contact him. Remedy 7.6 Oracle back end _____________________________________________________________________________________ John Atherly | APC by Schneider Electric | Information, Process & Organization (IPO) | Remedy Administrator / Developer Phone: +305-266-5005 ext. 237 | Email: [email protected] | Site: www.apc.com/ | Address: 703 Waterford Way, Suit 850, Miami, FL 33126 USA _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
