Keep in mind that using LIKE can actually sometimes force queries on large tables to be MORE efficient - IF you are using Oracle. Case in point - not that long ago we changed a query in an old system. The query was saying something to the effect of: 'Status' < "Resolved" AND "Group+" = "SomeGroupName" There were 1.6 million records in the table. Both of these fields were indexed. Oracle evalutes this and does a table scan anyway instead of using the Indexes because a very large ( > 30 %) of the cases matched the Group name. This query took a couple minutes. This query was on a table field that was on the main technician console and consequently was extremely aggravating for people to wait for. We changed the query to: 'Status' < "Resolved" AND 'Group+' LIKE "SomeGroupName%" This changed Oracle's behavior. It saw one index for this query instead of two because Oracle doesn't use indexes for LIKE (not always true, but in many cases). The query time went down to < 1 second. Before anyone jumps all over me - this was on a system that we knew was being retired and no more groups were going to be added. All group names were unique. I wouldn't advise this exact change on a system that wasn't being replaced BUT it did work here. And since we knew the customers had to live with this system for a while until the new one was in place it was a change worth making. William Rentfrow Principal Consultant, StrataCom [EMAIL PROTECTED] O 952-432-0227 C 701-306-6157
________________________________ From: Action Request System discussion list(ARSList) on behalf of Axton Sent: Mon 4/14/2008 9:29 AM To: [email protected] Subject: Re: Improve queries with LIKE and "%" expressions Not sure what you mean by 'automatic'. The query comes from a menu, set fields, push fields, or some other action that is in your workflow. Find the source of the query and evaluate your options. Axton On Mon, Apr 14, 2008 at 10:00 AM, Tadeu Augusto Dutra Pinto <[EMAIL PROTECTED]> wrote: > ** > > > > 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 <http://www.cinq.com.br/> > [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" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
