Query runs extremely slow because of simple user defined function

[Mrs Howl]

I have a query that just reads one table and appends to an output
table, one-for-one. No criteria. It's not a Total query (i.e. no
group by). It normally run run in minutes, but gets horribly slowed
down because five of my output fields are text fields and the
expression that gets appended is a user-defined function I wrote which
is very simple (just a few lines long). Here's the code of my
function:

Public Function tformat(num As Double, xdec As Byte) As String
If xdec = 0 Then
tformat = Right(Space(12) + Format(num), 12)
Else
tformat = Right(Space(12) + Format(num, "0." + String(xdec,
"0")), 12)
End If
End Function

In each of the 5 expressions in my query grid, the parameters I pass
are:
num: a numeric field from the query's input table
xdec: a numeric literal constant.


The above function looks fairly simple, but it causes my query which
otherwise would run in a few minutes to take many times that long to
finish (maybe an hour). Do any of you understand why, or maybe have a
helpful suggestion about a trick I could use to speed up my query?

[Ben Eaton]

Re: Query runs extremely slow because of simple user defined function

I'd be interested to see the SQL behind your query, that may have something
to do with it.

Assuming however that this is a VBA problem - given what your function
actually does I think the best way to do this would be to do away with the
custom function altogether and do the whole thing in SQL using an IIf
statement.


IIf(xdec=0,Righ t(Space(12)+For mat([num]),12),Right(Spa ce(12)+Format([num],"0
.."+String(xdec ,"0")),12))

(remembering of course to replace the xdec with your literal)

May work, may not work, suck it and see...

"Mrs Howl" <timckelley@yah oo.com> wrote in message
news:5bfe86c.03 11180928.6a6be0 36@posting.goog le.com...[color=blue]
> I have a query that just reads one table and appends to an output
> table, one-for-one. No criteria. It's not a Total query (i.e. no
> group by). It normally run run in minutes, but gets horribly slowed
> down because five of my output fields are text fields and the
> expression that gets appended is a user-defined function I wrote which
> is very simple (just a few lines long). Here's the code of my
> function:
>
> Public Function tformat(num As Double, xdec As Byte) As String
> If xdec = 0 Then
> tformat = Right(Space(12) + Format(num), 12)
> Else
> tformat = Right(Space(12) + Format(num, "0." + String(xdec,
> "0")), 12)
> End If
> End Function
>
> In each of the 5 expressions in my query grid, the parameters I pass
> are:
> num: a numeric field from the query's input table
> xdec: a numeric literal constant.
>
>
> The above function looks fairly simple, but it causes my query which
> otherwise would run in a few minutes to take many times that long to
> finish (maybe an hour). Do any of you understand why, or maybe have a
> helpful suggestion about a trick I could use to speed up my query?[/color]

[Mrs Howl]

Re: Query runs extremely slow because of simple user defined function

False alarm... I've figured it out. If the visual basic window is
open while the query is running, it drastically slows down the query.
I aborted the query, close the visual basic window and reran. It ran
much faster. Why this should make a difference, I don't know.