469,313 Members | 2,536 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,313 developers. It's quick & easy.

Query runs extremely slow because of simple user defined function

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?
Nov 12 '05 #1
2 7895
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,Right(Space(12)+Format([num]),12),Right(Space(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" <ti********@yahoo.com> wrote in message
news:5b*************************@posting.google.co m...
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?

Nov 12 '05 #2
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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Andy_Khosravi | last post: by
29 posts views Thread by wizofaus | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.