Re: COALESCE()

Depending on your needs and performance requirmentes there may be an easy solution. AFAIK the standard COALESCE function takes arguments that may have different types, an unknown number and returns accordingly. To the best of my knowledge this can't be done in a UDR, although I'd love to be proven wrong. But... If what you want is something similar, let's say... a function that takes up to N arguments of the same type (either INTEGER, SMALLINT, VARCHAR(255), CHAR, DECIMAL...) and returns the same type it's pretty easy to implement. But yes, I verified that it can cause a serious performance penalty. But the performance can be dramatically improved if you use a C UDR. In SPL I was getting something like 440% the time of nested NVLs. In C UDR I got around 130% (sometimes less). My tests were run with a 10M table of ten fields where I do something like: SELECT COALESCE(col1, col2, col3.... col10) from mytable; The table was scan first to populate cache. The thead was essentially "running". 20-30% performance hit in a query that reads a few rows would hardly be noticeable. Would this help? I may write an article for the blog around this. Regards On Thu, Mar 22, 2012 at 12:10 PM, Fernando Nunes <domusonline@gmail.com>wrote: > Apart from any potential performance hit (which I can't explain, but in > any case a call to a procedure is naturally slower than "native" function), > the problem is that you have to define the number and type of arguments. > It would be nice and allow all kinds of user side improvements if we were > able to define a function that receives an unknown number of parameter and > also of unknown type. > I can see why this can't be done (function overload etc), but it surely > would help to implement this kind of stuff... > > Any inside info from our architects? > Regards. > > > On Thu, Mar 22, 2012 at 9:17 AM, Stuart Brooks <stuart.brooks@ardenta.com>wrote: > >> There isn't a COALESCE function in Informix and nested NVL functions is >> the alternative. You could write your own COALESCE stored procedure so >> that you don't have to change the SQL. But be aware there may potentially >> be a performance hit; queries in an application that I previously worked on >> showed poorer performance using a COALESCE stored procedure than when using >> nested NVLs. >> >> Regards >> >> Stuart >> >> >> >> --- >> Ardenta Ltd is a company registered in England and Wales. Registered >> number: 4181041. Registered office: Saxon House, Downside, Sunbury on >> Thames, Middlesex, TW16 6RT. >> >> -----Original Message----- >> From: informix-list-bounces@iiug.org [mailto: >> informix-list-bounces@iiug.org] On Behalf Of davidegrove@gmail.com >> Sent: 21 March 2012 19:26 >> To: informix-list@iiug.org >> Subject: COALESCE() >> >> Isn't "COALESCE()" a SQL-92 function? >> >> Why it isn't in Informix (11.5). >> >> I suggested nested NVL functions as a work-around, but i would have >> preferred to just be able to have said, "It's in there." >> >> very happy to learn I was wrong. >> >> DG >> >> >> P.S. More whine with that cheese... I don't like continually having to be >> on defensive with our contractor. >> >> 1) Contractor: "What's the Informix equivalent to feature "X" in database >> "O" (or "SS")?" >> Me: "Sorry, Informix doesn't have it." >> >> 2) Contractor: "Why does dbexport/dbimport fail?" >> Me: "It's been a problem for a very long time, and IBM isn't going to fix >> it. There is a user-developed, non-IBM solution that is better, I suggest >> you use it." >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...