By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,264 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

Simple function called from query takes extremely long time

P: n/a
Hello all,

I'm writing several queries which need to do various string formating,
including changing a phone number from (123) 456-7890. After some
problem with data mismatches, I finally got it to work only to see that
it takes 30-60 seconds to run the query, instead of the usual .5
seconds when I use a query without a function.

Here is the code for the function. I call it using

HomePhone: GetPhone([Master].[MainPhone])

in one of my query fields. MainPhone is of type text, but I had to set
it as a variant or I got an error.

Public Function GetPhone(inputPhone As Variant) As String

Dim temp As String
temp = Mid(inputPhone, 6, 9)
temp = Replace(temp, "-", "")
GetPhone = temp

End Function

Am I doing something wrong, or does VBA just take this long? I have a
fairly fast system (Athlon XP 2500), though there are about 6500
records it has to go through.

The main reason I ask is because I use a Replace() function inside an
actual query and it works in a few seconds. However when I put this
code below inside the query I get the data mismatch error:

HomePhone: Replace(Mid([Master].[MainPhone],6,9),"-","")

Any suggestions? MainPhone is of type Text.

Nov 13 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
strict9 wrote:
Hello all,

I'm writing several queries which need to do various string formating,
including changing a phone number from (123) 456-7890. After some
problem with data mismatches, I finally got it to work only to see that
it takes 30-60 seconds to run the query, instead of the usual .5
seconds when I use a query without a function.
If you can imagine Access will call your function once for each row.
Here is the code for the function. I call it using

HomePhone: GetPhone([Master].[MainPhone])

in one of my query fields. MainPhone is of type text, but I had to set
it as a variant or I got an error.

Public Function GetPhone(inputPhone As Variant) As String

Dim temp As String
temp = Mid(inputPhone, 6, 9)
temp = Replace(temp, "-", "")
GetPhone = temp

End Function

Am I doing something wrong, or does VBA just take this long? I have a
fairly fast system (Athlon XP 2500), though there are about 6500
records it has to go through.
<sharp intake of breath> You need at least a XP2600+ for that :-)
The main reason I ask is because I use a Replace() function inside an
actual query and it works in a few seconds. However when I put this
code below inside the query I get the data mismatch error:

HomePhone: Replace(Mid([Master].[MainPhone],6,9),"-","")

Any suggestions? MainPhone is of type Text.


Your errors are most likely from passing nulls to the replace function,
Replace takes a string parameter and a strinbg cannot be null.

Try something like:
update mytable set phoneno = Replace(MainPhone,"-","") where phoneno is
not null;
--
[Oo=w=oO]

Nov 13 '05 #2

P: n/a
"strict9" <jo**************@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:
The main reason I ask is because I use a Replace() function inside
an actual query and it works in a few seconds. However when I put
this code below inside the query I get the data mismatch error:

HomePhone: Replace(Mid([Master].[MainPhone],6,9),"-","")

Any suggestions? MainPhone is of type Text.


As Trevor suggests, it's because of Null values in MainPhone.

There are two ways to do it:

1. Trevor's suggestion of putting in criteria that return only rows
that aren't Null, OR

2. concatenate the field with a zero-length string:

HomePhone: Replace(Mid([Master].[MainPhone] & "",6,9),"-","")

That way you won't be passing any Nulls.

And this should be faster, because you're calling only two functions
per row (instead of three, your UDF plus the two Access functions),
and because built-in functions tend to execute faster than
user-defined functions.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #3

P: n/a
David W. Fenton wrote:
"strict9" <jo**************@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

The main reason I ask is because I use a Replace() function inside
an actual query and it works in a few seconds. However when I put
this code below inside the query I get the data mismatch error:

HomePhone: Replace(Mid([Master].[MainPhone],6,9),"-","")

Any suggestions? MainPhone is of type Text.

As Trevor suggests, it's because of Null values in MainPhone.

There are two ways to do it:

1. Trevor's suggestion of putting in criteria that return only rows
that aren't Null, OR

2. concatenate the field with a zero-length string:

HomePhone: Replace(Mid([Master].[MainPhone] & "",6,9),"-","")

That way you won't be passing any Nulls.


The problem with 2 is it will needlessly process the nulls, which don't
need updating and will also attempt to update those nulls with a zero
length string that by default is not allowed. Even if a ZLS was allowed
it would change the meaning of the value from "data missing" (or "we
don't know what the phone number is") to "they have no phone".

--
[Oo=w=oO]

Nov 13 '05 #4

P: n/a
Trevor Best <no****@besty.org.uk> wrote in
news:42**********************@news.zen.co.uk:
David W. Fenton wrote:
"strict9" <jo**************@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

The main reason I ask is because I use a Replace() function
inside an actual query and it works in a few seconds. However
when I put this code below inside the query I get the data
mismatch error:

HomePhone: Replace(Mid([Master].[MainPhone],6,9),"-","")

Any suggestions? MainPhone is of type Text.

As Trevor suggests, it's because of Null values in MainPhone.

There are two ways to do it:

1. Trevor's suggestion of putting in criteria that return only
rows that aren't Null, OR

2. concatenate the field with a zero-length string:

HomePhone: Replace(Mid([Master].[MainPhone] & "",6,9),"-","")

That way you won't be passing any Nulls.


The problem with 2 is it will needlessly process the nulls, which
don't need updating and will also attempt to update those nulls
with a zero length string that by default is not allowed. Even if
a ZLS was allowed it would change the meaning of the value from
"data missing" (or "we don't know what the phone number is") to
"they have no phone".


I missed anywhere in the original post where he said he was doing an
update. Just referring back to it, no, I don't see any indication
that it's anything than a SELECT query, in which case, he may want
to display the records with no phone numbers.

Secondly, if he's processing multiple columns, using WHERE criteria
won't work for an update, as it will exclude records that may have
data in other columns.

There are two solutions to the update problem with multiple columns:

1. run multiple updates with your criteria, one column at a time.

2. change the formula for the update to:

IIf(Not IsNull(Master.MainPhone),Replace(Mid(Master.MainPh one &
"",6,9),"-",""))

Again, though, I don't see that your assumption of an update is
warranted by the original post at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #5

P: n/a
David W. Fenton wrote:
Again, though, I don't see that your assumption of an update is
warranted by the original post at all.


You're probably right, perhaps the OP could clarify.

--
[Oo=w=oO]

Nov 13 '05 #6

P: n/a
Hey all,

Thank you very much for the replies. Unfortunately this project took
over my life for the past week and I wasn't able to check back to this
thread after I found a workaround.

This is indeed just a standard SELECT query. You were right about
checking for nulls fixing the error.

I will try to convert the user definied functions I have into the
built-in functions in the query. However there are several functions
which have multiple line of replacing (for instance, removing th, st,
rd, and nd from a grade number). I don't see it being feasible to place
this entire function in the query.

However having to call two or three user defined functions is really
killing me when I need to run this query over 20,000+ records. One
query I have takes a full 3 minutes, and while that doesn't sound too
bad I'm constantly making slight adjustments which I need to test.

One thing I did is change make several of my sub-queries into make
table queries, and then replaced those queries with the tables they
made inside my main query.

Are there any other ways of possibly speeding up this query? Thanks
again.

Nov 13 '05 #7

P: n/a
"strict9" <jo**************@gmail.com> wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:
Hey all,

Thank you very much for the replies. Unfortunately this project took
over my life for the past week and I wasn't able to check back to this
thread after I found a workaround.

This is indeed just a standard SELECT query. You were right about
checking for nulls fixing the error.

I will try to convert the user definied functions I have into the
built-in functions in the query. However there are several functions
which have multiple line of replacing (for instance, removing th, st,
rd, and nd from a grade number). I don't see it being feasible to place
this entire function in the query.

However having to call two or three user defined functions is really
killing me when I need to run this query over 20,000+ records. One
query I have takes a full 3 minutes, and while that doesn't sound too
bad I'm constantly making slight adjustments which I need to test.

One thing I did is change make several of my sub-queries into make
table queries, and then replaced those queries with the tables they
made inside my main query.

Are there any other ways of possibly speeding up this query? Thanks
again.


Show us the query or queries.

Post so that the prvious post can be found or include enough so we know
what you're talking about.

Tell us what version of Access you are using.

State your problem clearly. I am guessing that it is:
I want to be able to update twenty-thousnd records, replacing or removing
strings, quickly.

Show us the table structure.

I have never seen a situation where temporary tables were needed or where
they made things faster.

--
Lyle

"The aim of those who try to control thought is always the same. They find
one single explanation of the world, one system of thought and action that
will (they believe) cover everything; and then they try to impose that on
all thinking people."
- Gilbert Highet
Nov 13 '05 #8

P: n/a
strict9 wrote:
Are there any other ways of possibly speeding up this query? Thanks
again.


Things to look at would be to minimise the number of functions called so
try to get everything done within the one function if possible. If it's
just the phone number you're working on and you can elimiate the nulls
then a small increase in speed may be had by passing the variable as a
string rather than a variant.

There will be a trade off with error trapping too, it'd best to have
some but it does introduce some overhead.
--
1f u c4n r34d th1s u r34lly
n33d t0 g37 l41d

Nov 13 '05 #9

P: n/a
Lyle Fairfield wrote:
Show us the query or queries.
It's a pretty huge query, which is why I have neglected to paste it
here. So I've been trying to describe it by other means.
Post so that the prvious post can be found or include enough so we know what you're talking about.

Tell us what version of Access you are using.
Access 2003.
State your problem clearly. I am guessing that it is:
I want to be able to update twenty-thousnd records, replacing or removing strings, quickly.
This company is transferring their database to a new online database,
which has strict import rules, such as phone numbers in a specific
format, no dashes in SSN, etc.
Show us the table structure.
Here's a snapshot of the query, just to give you an idea of how crazy
it is: http://exrt.net/images/query.png
I have never seen a situation where temporary tables were needed or where they made things faster.


In writing this response I believe I may have found a solution to help
me. Since I receive new copies of the database every week (with new
data in it), I thought it wouldn't be possible to simply go through and
change the data once. Instead each time I do an import I've run a
select query which performs all the formatting functions.

However, now that I am at the point where I know my functions work
correctly, I can apply them to the data without fear of losing
important information such as accidently truncating a phone number.

So what seems to be a good idea at this point is to create a new query
which performs the time intensive formatting functions and make it an
update query, so it will change the actual data. Then I will adjust my
select queries to do just that: select data, instead of format it. In
the future when I receive new database revisions, I will just run those
update queries again before running the select queries.

Does this sound like a good idea? I became so set in the thought that
since the database changed each week I shouldn't bother changing the
actual data, but now it looks as though this would be much more
efficient then running the formatting functions every single time.

Nov 13 '05 #10

P: n/a
"strict9" <jo**************@gmail.com> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:
So what seems to be a good idea at this point is to create a new
query which performs the time intensive formatting functions and
make it an update query, so it will change the actual data. Then I
will adjust my select queries to do just that: select data,
instead of format it. In the future when I receive new database
revisions, I will just run those update queries again before
running the select queries.

Does this sound like a good idea? I became so set in the thought
that since the database changed each week I shouldn't bother
changing the actual data, but now it looks as though this would be
much more efficient then running the formatting functions every
single time.


It sounds like a good idea to me.

Do you have any say over the application that's producing the data?
Surely that's the place that validation on data entry should be
running to regularize the format on fields, instead of you having to
massage it every time it arrives.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11

P: n/a
David W. Fenton wrote:
It sounds like a good idea to me.

Do you have any say over the application that's producing the data?
Surely that's the place that validation on data entry should be
running to regularize the format on fields, instead of you having to
massage it every time it arrives.


The lack of data validation is one of the many reasons I am currently
exporting all this data to a new, much stricter database system :) I
just have to keep getting new copies of the database for a few weeks
while they make the transition.

Nov 13 '05 #12

P: n/a
"strict9" <jo**************@gmail.com> wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:
David W. Fenton wrote:
It sounds like a good idea to me.

Do you have any say over the application that's producing the
data? Surely that's the place that validation on data entry
should be running to regularize the format on fields, instead of
you having to massage it every time it arrives.


The lack of data validation is one of the many reasons I am
currently exporting all this data to a new, much stricter database
system :) I just have to keep getting new copies of the database
for a few weeks while they make the transition.


I hear ya! Been there, done that!

I hope you're being paid by the hour for the data conversion.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.