473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Simple function called from query takes extremely long time

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
12 3383
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
"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
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
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
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
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
"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
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
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
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

27
by: Brian Sabbey | last post by:
Here is a first draft of a PEP for thunks. Please let me know what you think. If there is a positive response, I will create a real PEP. I made a patch that implements thunks as described here....
2
by: Shailan | last post by:
Hi Im having trouble with the following code that seems to be behave differently when called from the browser as opposed to the command line. The calling script is a cgi that forks, with the...
11
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows...
2
by: Mrs Howl | last post by:
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...
3
by: Janross | last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's network, it takes at least 5 minutes to run. ...
51
by: Alan | last post by:
hi all, I want to define a constant length string, say 4 then in a function at some time, I want to set the string to a constant value, say a below is my code but it fails what is the correct...
2
by: Baller4lifeII | last post by:
Hey there, I've been chasing a problem for the project that I'm working on and was able to narrow it down to the ORDER BY clause in my query. The algorithm looks like this, I take a range of...
30
by: galiorenye | last post by:
Hi, Given this code: A** ppA = new A*; A *pA = NULL; for(int i = 0; i < 10; ++i) { pA = ppA; //do something with pA
1
by: giovannino | last post by:
Dear all, I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !). 1) Given that I'm not a programmer I can't understand why...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.