Functions - Not Always

[JohnZaleski]

I've never taken much advantage of UDF's. I recently started putting much more often used logic into functions. Curious as to the difference in using these functions in large batch scripts or Alpha operations with many records as opposed to keeping the expression at the execution point, I ran a little test. I used a function posted by Ira for finding the last day of a month. Here it is.

''XBasic
dim datevalue as d
datevalue = {01/01/01}

'rem function test
t1 = toseconds(time())
For x = 1 to 50000
lastday = monthend(datevalue)
Next x
t2 = toseconds(time())
trace.writeln("Function Test for " + dtoc(lastday) + " - Seconds elapsed:"+STR(t2-t1))

'rem non function test
t1 = toseconds(time())
For x = 1 to 50000
lastday = if(DateValue={},{},Addmonths(DateValue-Day(DateValue)+1,1)-1)
Next x
t2 = toseconds(time())
trace.writeln("Non Function Test for " + dtoc(lastday) + " - Seconds elapsed:"+STR(t2-t1))
end

'rem code for monthend function
'function MonthEnd as D(DateVal as D)
' MonthEnd=iif(DateVal={},{},Addmonths(DateVal-Day(DateVal)+1,1)-1)
'end function


From the trace window:
Function Test for 01/01/2001 - Seconds elapsed: 41
Non Function Test for 01/01/2001 - Seconds elapsed: 10

The only difference I can see is that caused by calling the function. I realize that the time difference between the two processes divided by 50000 is small (6 ten thousandths of a second), but my conclusion is that I will still use functions much more frequently, just not in large batch processes or queries where the function has to be called in every iteration.

John

[Geoffrey Hollander]

RE: Functions - Not Always

John,

Here here! I've just finished doing the same thing and have found basically the same results as you would expect.

I'm just wrapping up a function now that changes a series of user-selected field names, with any combination of disparate field types (C, N, D, L), into a single character expression for adhoc report sortation.

Creating a function to convert up to 5 field names/types into a compound sort string now allows me to send grouping and sortation specs to my report template on the fly, but the real benefit I've discovered is the ability to reuse UDF's over and over again in other scripts. Who knew?

Best regards,

Geoff Hollander

[csda1]

RE: Functions - Not Always

Hi John,

You did not try it with an embedded function, which is a little faster than an external function. The code would look like this


''XBasic
dim datevalue as d
datevalue = {01/01/01}

'rem function test
t1 = toseconds(time())
For x=1 to 50000
lastday=monthend(datevalue)
Next x
t2 = toseconds(time())
trace.writeln("Function Test for "+dtoc(lastday)+" - Seconds elapsed:"+STR(t2-t1))

'rem function test
t1 = toseconds(time())
For x = 1 to 50000
lastday = monthendi(datevalue)
Next x
t2 = toseconds(time())
trace.writeln("Imbedded Function Test for " + dtoc(lastday) + " - Seconds elapsed:"+STR(t2-t1))

'rem non function test
t1 = toseconds(time())
For x = 1 to 50000
lastday = if(DateValue={},{},Addmonths(DateValue-Day(DateValue)+1,1)-1)
Next x
t2 = toseconds(time())
trace.writeln("Non Function Test for " + dtoc(lastday) + " - Seconds elapsed:"+STR(t2-t1))

'rem loop only test
t1 = toseconds(time())
For x = 1 to 50000
' lastday = if(DateValue={},{},Addmonths(DateValue-Day(DateValue)+1,1)-1)
Next x
t2 = toseconds(time())
trace.writeln("Loop Only Test for " + dtoc(lastday) + " - Seconds elapsed:"+STR(t2-t1))
end



'***************************
'rem code for monthendi function
function MonthEndi as D(DateVal as D)
MonthEndi=iif(DateVal={},{},Addmonths(DateVal-Day(DateVal)+1,1)-1)
end function

'***************************
'rem code for monthend function place in an external function
'function MonthEnd as D(DateVal as D)
'MonthEnd=iif(DateVal={},{},Addmonths(DateVal-Day(DateVal)+1,1)-1)
'end function


My times were:

Function Test for 01/31/2001 - Seconds elapsed: 12
Imbedded Function Test for 01/31/2001 - Seconds elapsed: 11
Non Function Test for 01/31/2001 - Seconds elapsed: 3
Loop Only Test for 01/31/2001 - Seconds elapsed: 1


However, your test shows what the overhead for a simple function is. The more code it is, the less the overhead matters. It may possibly faster in version 5 with precompiled add-ins.

It would be nice if Alpha could have a macro substition (includes) so that code could be expanded inline. It may also be possible for Alpha to possibly improve function overhead by caching and other tricks, although these may already be done.

Nevertheless, the real issue, that you illustrate is that for a simplistic function that is in the middle of a loop that is called many times, you are better to embed the code than have a function call. Outside a loop, the overhead is so trivial, that the simplification of the code by using function calls is the preferred method.


Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[csda1]

RE: Functions - Not Always

John,

One other thing though.

Suppose you changed the code to something like this,


'rem function test
t1 = toseconds(time())
lastday=monthend(datevalue,50000)
t2 = toseconds(time())
trace.writeln("Function Test for "+dtoc(lastday)+" - Seconds elapsed:"+STR(t2-t1))
end

'***************************
'rem code for monthend function place in an external function
'function MonthEnd as D(DateVal as D,RepeatCount as N)
'For x=1 to RepeatCount
'MonthEnd=iif(DateVal={},{},Addmonths(DateVal-Day(DateVal)+1,1)-1)
'Next
'end function


What you have done, and in general should do, is move code that has to be tightly written together into the function. Now the overhead of the function call will have little importance.

This is essentially what Alpha has done with their product. Make the major database operations a primitive (and thus very efficient, like indexing, updates, exports etc) and make the front end (user interface) more interpretive where speed is not particularly important.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]