Re: Performance Questions 7.x

This is a multi-part message in MIME format. --------------5BB17852615C Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Carol Stimmel wrote: > Sorry to followup my earlier post, but I thought I'd > mention that we had great success in increasing our > performance by, basically, dumping the whole thing > and starting over. ;-) We re-worked the SELECT > statements to NEVER, EVER use the word MATCH and > the sucker is a screamer. This or course, required > an entire rethinking of the database and how items > were selected. > > Let this server as a warning to anyone who has > a performance issue....avoid avoid MATCH!!!! > IMNSHO. I have to say that that is an overly drastic statement. It really depends on how you are using match. For example, if you are using a MATCHES with a leading wildcard, e.g. col1 MATCHES "*ones", then that table cannot do an index search to find the rows satisfying your condition, since all index lookup must be anchored (in case anyone doesn't understand the concept of anchoring, consider looking in the phone book for all people with the first name Dave as opposed to a last name). SO you could wind up doing a sequential scan, or using another index that is not very selective (based on other clauses in your query). Using MATCHES with an anchored value is much more efficient, as partial index searches can be done. The more unique the part that you know, the faster the search (e.g. where col1 MATCHES "Jo*" is good, where col1 MATCHES "Jone*" is better). If you are NOT using wildcards, then you are better off using an "=". -- Dave Kosenko, Informix Professional Services ****************************************************************************"I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney --------------5BB17852615C Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="DISCLAIM" ************************************************************************* Disclaimer: All opinions expressed in this message are well-reasoned and insightful; needless to say, they are not those of Informix Software, its partners or lackeys. Anyone who says otherwise is itching for a fight. --------------5BB17852615C--