Your like statements in B aren't all that bad, when you are doing a Value% you still have the possibility of using indexes, it's the query in A that will kill you every time, %Value% means that every record in the table must be examined to determine if there is a possible match, i.e. Table Scan. If it's being done automatically through workflow, you should see if you can re-design that workflow to not do that. For the items in B, the best suggestion is to index the most used search fields, in your query below, that's Submitter and Last Mod By
_____ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Tadeu Augusto Dutra Pinto Sent: Monday, April 14, 2008 8:01 AM To: arslist@ARSLIST.ORG Subject: Improve queries with LIKE and "%" expressions ** Hi All, How can I improve "automatic queries" of my database?? Analyzing queries (through AR System Log Analyzes tool) I've seen that some queries are consuming more than expected of processing on database... for example, How can I optimize a query such as: a) SELECT T107.C1,C536870927 FROM T107 WHERE ((T107.C536870927 LIKE (('%' || '02. Price') || '%')) AND ('08. By Credit Card' = T107.C536870925)) ORDER BY 1 ASC b) SELECT T137.C1,T137.C1,C4,C536870917,C536870918,C536870919,C7 FROM T137 WHERE ((T137.C536870918 LIKE 'Product%') AND (T137.C536870917 LIKE '01. Debit Card%') AND (T137.C8 LIKE 'CARTOES%') AND (T137.C4 LIKE '02. Cards%') AND (T137.C2 LIKE 't145117%')) ORDER BY 1 ASC Note: a) Field 'C536870927 ': Field Type: Character (with Menu appended) Display Type: Edit QBE Match: Leading b) Field 'C536870918': Field Type: Character (with Menu appended) Display Type: Edit QBE Match: Leading Field 'C536870917': Field Type: Character (with Menu appended) Display Type: Edit QBE Match: Leading Field 'C8': (Core Field) Field Type: Character Display Type: Edit QBE Match: Leading Field 'C4': (Core Field) Field Type: Character (with Menu appended) Display Type: Edit QBE Match: Leading Field 'C2': (Core Field) Field Type: Character Display Type: Edit QBE Match: Leading I think that's because of this "LIKE" expression that this queries are consuming more resources of database... But this queries run automatically... I really don't know a way to improve this .... someone knows if there is a possibility to improve this kind of queries ?? Regards, Tadeu Augusto Dutra Pinto ----------------------------------------------------------------- IT Web Services ATM Cinq Technologies http://www.cinq.com.br <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] Fone: 41 3018-2833 - Cinq ----------------------------------------------------------------- Confiabilidade, Inovação e Qualidade em T.I. __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
