Function call in query slowing it down

[sneuberg]

We have a database which has an attribute we call Line Number, but it’s not really a number as it can have a suffix. So we have Line Numbers like, 1, 2, 3, 3N, 4N, 52, 199, 199N, etc. Since this is a text field it doesn’t sort nicely so for sorting I want to prefix these with zeros so that they look like 001, 002, 003, 003N, 004N, 052, 199, 199N, etc. Because of the suffixes I found that the format function doesn’t work well on these so I wrote my own function which is:

Public Function PrefixZeros(str As String, lng As Integer) As String
If Right(str, 1) = "N" Then
lng = lng + 1
End If
PrefixZeros = str
Do While Len(PrefixZeros) < lng
PrefixZeros = "0" & PrefixZeros
Loop


End Function

I put this in a module and call it in a query and it works fine, but it’s amazing slow. A query with 9496 records takes about two seconds longer to run because of this function in the query. I don’t get it. Just for fun I commented out the code in this function and ran the query. It still runs slow. Apparently just the call to the function slows it down. Anybody know why this is and whether anything can be done about it?

[ranman256]

1. custom functions can be slow
2. loops in custom functions can REALLY be slow.


Try:

Public Function PrefixZeros(pStr As String)
PrefixZeros = string(4-len(pStr ),"0") & pStr
end function

[sneuberg]

1. custom functions can be slow
2. loops in custom functions can REALLY be slow.


Try:

Public Function PrefixZeros(pStr As String)
PrefixZeros = string(4-len(pStr ),"0") & pStr
end function

Thanks but that doesn't do what I want. For example I want 001, 002, 003, 003N, 004N, 052, 199, 199N. Your function gives me 001, 002, 003, 052, 199, 003N, 004N, 199N, but that doesn't matter. The execution time is virtually the same. The long pole in the tent here is the function call itself. A query of 9496 records take about two seconds longer even with an empty function. I have Intel Core 7 CPU, 2.67 Ghz. That seems to work out to about half a million clock cycles per function call. What the hell could it be doing?