从查询中调用的简单函数需要很长时间 [英] Simple function called from query takes extremely long time

问题描述

大家好，


我正在编写几个需要进行各种字符串格式化的查询，

包括更改电话号码[phone]。在数据不匹配的一些问题之后，我终于让它工作只看到

运行查询需要30-60秒，而不是通常的.5
当我使用没有函数的查询时
秒。


这是函数的代码。我打电话给它使用


HomePhone：GetPhone（[Master]。[MainPhone]）


在我的一个查询字段中。 MainPhone是文本类型，但我必须设置

它作为变体或我收到错误。


公共函数GetPhone（inputPhone As Variant）As String


Dim temp As String

temp = Mid（inputPhone，6,9）

temp =替换（temp，" ; - "，""）

GetPhone = temp


结束功能


我在做什么有什么不对劲，或者VBA只需要这么久？我有一个相当快的系统（Athlon XP 2500），虽然它有大约6500

的记录，但它必须通过。


我问的主要原因是因为我在

实际查询中使用了一个Replace（）函数，它在几秒钟内就可以工作了。但是，当我在查询中放入以下

代码时，我得到数据不匹配错误：


HomePhone：Replace（Mid（[Master]。[MainPhone] ，6,9）， - ，，


有什么建议吗？ MainPhone是Text类型。

解决方案

strict9写道：

大家好，
<我正在写几个需要进行各种字符串格式化的查询，
包括更改电话号码[phone]。在数据不匹配的一些问题之后，我终于让它工作只看到运行查询需要30-60秒，而不是通常的.5
秒我使用没有函数的查询。


如果你能想象Access会为每一行调用你的函数一次。

这是函数的代码。我在其中一个查询字段中使用

HomePhone：GetPhone（[Master]。[MainPhone]）

。 MainPhone是文本类型，但我必须将它设置为变体或我收到错误。

公共函数GetPhone（inputPhone As Variant）As String

Dim temp As String
temp = Mid（inputPhone，6,9）
temp =替换（temp，" - "，""）
GetPhone = temp

结束职能

我做错了什么，或者VBA是否只需要这么久？我有一个相当快的系统（Athlon XP 2500），虽然有大约6500
记录它必须通过。


<呼吸急促>你需要至少一个XP2600 + :-)

我问的主要原因是因为我在
实际查询中使用了一个Replace（）函数，它可以在几秒钟内完成。但是当我把这个
代码放在查询中时，我得到数据不匹配错误：

HomePhone：Replace（Mid（[Master]。[MainPhone]，6,9），" - ，，，

有什么建议吗？ MainPhone的类型为Text。

您的错误最有可能是将空值传递给替换函数，

Replace需要一个字符串参数和一个strinbg不能为空。


尝试类似的事情：

更新mytable set phoneno =替换（MainPhone， - "，"） phoneno是

不为空;

-

[Oo = w = oO]

" strict9" <乔************** @ gmail.com>写在

新闻：11 ********************** @ o13g2000cwo.googlegr psps.com：

我问的主要原因是因为我在实际查询中使用了一个Replace（）函数，它在几秒钟内就可以工作了。但是，当我将下面的代码放在查询中时，我得到数据不匹配错误：

HomePhone：Replace（Mid（[Master]。[MainPhone]，6,9），" - ，，，

有什么建议吗？ MainPhone是Text的类型。

正如Trevor建议的那样，这是因为MainPhone中的Null值。


那里有两种方法可以做到这一点：


1. Trevor的建议是设置仅返回行的标准

不是空的，或者


2.将字段与零长度字符串连接：


HomePhone：替换（中（[Master]。[MainPhone]& ;""，6,9），" - "，""）


那样你就不会通过任何Null。


这应该更快，因为你只调用两行函数

每行（而不是三行，你的UDF加上两个Access函数），
因为内置函数的执行速度往往比用户定义的函数更快。


-

David W. Fenton http://www.bway.net/~dfenton <无线电通信bway dot net的
dfenton http://www.bway.net/ ~dfassoc

David W. Fenton写道：

" strict9" <乔************** @ gmail.com>在
新闻中写道：11 ********************** @ o13g2000cwo.googlegr psps.com：

我问的主要原因是因为我在实际查询中使用了一个Replace（）函数，它在几秒钟内就可以工作了。但是，当我将下面的代码放在查询中时，我得到数据不匹配错误：

HomePhone：Replace（Mid（[Master]。[MainPhone]，6,9），" - ，，，

有什么建议吗？ MainPhone是Text的类型。

正如Trevor建议的那样，这是因为MainPhone中的Null值。

有两种方法可以做到：

1. Trevor建议放入只返回行的标准
不是空的，或者

2.将字段连接到零-length string：

HomePhone：Replace（Mid（[Master]。[MainPhone]&""，6,9），" - "，""）

那样你就不会通过任何Null。

2的问题是它会不必要地处理空值，这不是t $ / b $ b b需要更新，并且还会尝试使用零

长度字符串更新这些空值，默认情况下不允许这样做。即使ZLS被允许

，它也会改变数据丢失值的含义。 （或我们

不知道电话号码是多少）以他们没有电话。


-

[Oo = w = oO]

Hello all,

I''m writing several queries which need to do various string formating,
including changing a phone number from [phone]. 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.

解决方案

strict9 wrote:

Hello all,

I''m writing several queries which need to do various string formating,
including changing a phone number from [phone]. 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]

"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

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]

这篇关于从查询中调用的简单函数需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！