Doug, Generally speaking, you want to construct your query to limit the results as much as possible in your first table. Doing that you build fewer links and get fewer results in the second table, which then may be further refined before linking to a third table, and so on.
The R:BASE optimizer is usually pretty good about figuring out the best way to optimize, but it relies upon index statistics that are only updated when you create or pack the index. I often find that MANOPT ON will give better results, especially with complex queries and many rows. Emmitt Dove Converting Systems Architect Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Doug Hamilton Sent: Thursday, December 09, 2010 15:09 To: RBASE-L Mailing List Subject: [RBASE-L] - RE: LIKE vs CONTAINS on indexed column Thanks Emmitt & Dennis. CustPO is text 12, fully indexed. PK is on CusPnbr (text 15) in JobData and FK is on TxnHist - does that make any difference Dennis? How does switching the tables help? Should T1 be the PK table instead of the one being updated? Or point me to documentation if you don't have time for an explanation. Here's the whole original command: UPDATE TxnHist + SET StatCode = 'E' + FROM TxnHist T1, JobData T2 + WHERE + T1.CusPnbr = T2.CusPnbr AND + T1.StatCode = 'T' AND + T2.CUSTPO LIKE 'MP0000*' Thanks much (The '*' in the CONTAINS in my original post was a copy/paste typo), Doug Dennis McGrath wrote: > Switch the tables around and > > WHERE + > T1.CUSTPO LIKE 'MP0000*' + > AND T1.CusPnbr = T2.CusPnbr + > AND T2.StatCode = 'T' > > Dennis > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Emmitt Dove > Sent: Thursday, December 09, 2010 12:33 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: LIKE vs CONTAINS on indexed column > > Doug, > > You can use the (ISTAT('TOTALREADS')) function to experiment and note the > results. > > Emmitt Dove (Not Bill) > Converting Systems Architect > Evergreen Packaging, Inc. > [email protected] > (203) 214-5683 m > (203) 643-8022 o > (203) 643-8086 f > [email protected] > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Doug > Hamilton > Sent: Thursday, December 09, 2010 13:08 > To: RBASE-L Mailing List > Subject: [RBASE-L] - LIKE vs CONTAINS on indexed column > > List, please refresh my memory. > I have an indexed column named CustPO. When updating, which will be faster: > WHERE CustPO LIKE 'MP000*' or > WHERE CustPO CONTAINS 'MP000*' or > something else? > MP000 is at the beginning of the text in the column. > > The full clause is: > WHERE + > T1.CusPnbr = T2.CusPnbr AND + > T1.StatCode = 'T' AND + > T2.CUSTPO LIKE 'MP0000*' > > CusPnbr are PK & FK; I assume that should be the first condition in the > WHERE clause. > StatCode can have one of 5or 6 values, so it is not indexed. > Would parenthesis help anywhere? > > Now that I've thought this through, this might be an improvement, > assuming the conditions are evaluated in the order listed, but there > were caveats to that also (I think): > WHERE + > T1.CusPnbr = T2.CusPnbr AND + > T2.CUSTPO LIKE 'MP0000*' AND + > T1.StatCode = 'T' > > I know this question comes up every year or so - I apologize for the > redundancy. > > TIA (Bill), > Doug > > > >
