SPL variant / not variant - performance

Hi, I like and use with some frequency the stackexchange "world" and few Informix questions are made there. This question in special stirred up my curiosity: http://stackoverflow.com/questions/20362107/informix-spl-function-somewhat-varia nt Basically the question is : A SQL with function variant called at where clause took few minutes. changing it to non-variant take less of one second. Why? (ifx version 10) (is a timezone function, so, they can have different results when sometime have daylight savings or not) Anyone able to answer the user question? (the reason of the discrepancy at the performance) Please, write/copy the answer at the stackoverflow site too... Cesar --089e013d19ccef8fdd04ecab0207

Depending on the real situation it's likely that the non-variant procedure is only being executed one or a few times. I can imagine a situation where the procedure arguments are fields from a table and where those fiels repeat their valus heavily... At first glance I'd say the only relevant issue is why SPL calling is so slow... I'll take a look at the site later.... Regards On Dec 4, 2013 1:06 AM, "Cesar Martins" <cesar.inacio.martins@gmail.com> wrote: > Hi, > I like and use with some frequency the stackexchange "world" and few > Informix questions are made there. > > This question in special stirred up my curiosity: > > > http://stackoverflow.com/questions/20362107/informix-spl-function-somewhat-varia nt > > Basically the question is : > A SQL with function variant called at where clause took few minutes. > changing it to non-variant take less of one second. > Why? (ifx version 10) > (is a timezone function, so, they can have different results when > sometime have daylight savings or not) > > Anyone able to answer the user question? > (the reason of the discrepancy at the performance) > > Please, write/copy the answer at the stackoverflow site too... > > Cesar > > --089e013d19ccef8fdd04ecab0207 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b6721c405f5a004ecb0ff8f

You might be better off having two functions, one that returns the timezone offset and one that returns the daylight saving adjustment - both of those can be non-variant . Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Cesar Martins > Sent: Tuesday, December 03, 2013 7:06 PM > To: ids@iiug.org > Subject: SPL variant / not variant - performance [32084] > > Hi, > I like and use with some frequency the stackexchange "world" and few > Informix questions are made there. > > This question in special stirred up my curiosity: > > http://stackoverflow.com/questions/20362107/informix-spl-function- > somewhat-variant > > Basically the question is : > A SQL with function variant called at where clause took few minutes. > changing it to non-variant take less of one second. > Why? (ifx version 10) > (is a timezone function, so, they can have different results when > sometime have daylight savings or not) > > Anyone able to answer the user question? > (the reason of the discrepancy at the performance) > > Please, write/copy the answer at the stackoverflow site too... > > Cesar > > --089e013d19ccef8fdd04ecab0207 > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum.

Well, Paul already replied. But the situation is simple and I think the person asking the question already knows the answer. In fact the difference between using a VARIANT function vs a NOT VARIANT one is between calling it ~1M times or 1 time. The function will only change it's result two times per year and as far as I know (I can be wrong) the engine does not cache NOT VARIANT functions results between queries. Regards On Wed, Dec 4, 2013 at 8:14 AM, Fernando Nunes <domusonline@gmail.com>wrote: > Depending on the real situation it's likely that the non-variant procedure > is only being executed one or a few times. I can imagine a situation where > the procedure arguments are fields from a table and where those fiels > repeat their valus heavily... > At first glance I'd say the only relevant issue is why SPL calling is so > slow... > I'll take a look at the site later.... > > Regards > On Dec 4, 2013 1:06 AM, "Cesar Martins" <cesar.inacio.martins@gmail.com> > wrote: > >> Hi, >> I like and use with some frequency the stackexchange "world" and few >> Informix questions are made there. >> >> This question in special stirred up my curiosity: >> >> >> http://stackoverflow.com/questions/20362107/informix-spl-function-somewhat-varia nt >> >> Basically the question is : >> A SQL with function variant called at where clause took few minutes. >> changing it to non-variant take less of one second. >> Why? (ifx version 10) >> (is a timezone function, so, they can have different results when >> sometime have daylight savings or not) >> >> Anyone able to answer the user question? >> (the reason of the discrepancy at the performance) >> >> Please, write/copy the answer at the stackoverflow site too... >> >> Cesar >> >> --089e013d19ccef8fdd04ecab0207 >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c1e7fc8e647104ecbd0ab0