Re: Performance of upper()

If you could also test: select name from org where upper(name) = "SOMENAME" it would show how fast the two solutions where in this case. It should still lead to a sequential scan of the whole table, but the 7.3 upper should be substantially faster. Using upper in the where clause will probably be the most common usage, but that also shows how usefull it would have been to be able to create an index on it like you can with the Universal Data Option. On Wed, 19 Aug 1998 09:26:15 -0400, Steve Romankiw <sromankiw@execrisk.com> wrote: > >I did a test between SPL Upper vs. Upper() and my results were: > >Query >==== >select upper(name) from org > >The org table had about 200k rows. With the SPL Upper, it took 7m34s. >With the Upper(), it took 0m51s. > >I ran this on a Sun e3000, two-cpus, 1gig mem. The org table is >non-fragmented. > >I also noticed that the Upper() to took precedence over SPL Upper,i.e., our >SPL Upper was called upper. > >HTH. > >Steve Romankiw > >Marty Allred wrote: > >> We're having some real problems with the horrible performance of using >> the SPL version of upper(). In comparing our options on how to work >> around this, we're considering just upgrading to 7.3x so we can use the >> built-in upper() function. >> >> What kind of performance improvement is there in using the built-in >> function as compared to the klunky SPL version? Is it strictly a matter >> of convenience or is it really faster? >> >> -- >> Marty Allred >> Informix DBA, Orchestrate.com > > > Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)