调用函数的最快方法 [英] fastest way to call a function
问题描述
嗨大师
当我有一个查询，其中我使用一个小函数，例如：
SELECT A03_FILES.ID，A03_FILES .D，hasvt（[ID]）AS hsvVT
来自A03_FILES;
其中HasVT定义如下：
--------------------
公共函数hasVT（ID As Long）作为布尔值
Dim rst记录集
Set rst = CurrentDb.OpenRecordset（" SELECT A03_FILES.ID FROM A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D WHERE
A03_FILES.ID ="& ID&" ;;"）
hasVT = rst.RecordCount
Set rst =没什么
结束功能
--------------------
然后它似乎真的减慢了查询速度。有没有更快的方法来做到这一点而不改变查询的概念（即我知道我可以把它全部变成SQL，这肯定会让它更快）。
在我看来，它减速的原因是，对于查询中的每一行，它必须打开和关闭该功能（运行它实际上非常快，实际上非常快） 。
谢谢
- Nicolaas
" windandwaves" <无线********* @ coldmail.com>在消息中写道
新闻：Ir ******************* @ news.xtra.co.nz ...嗨大师
当我有一个查询，其中我使用一个小函数，例如：
SELECT A03_FILES.ID，A03_FILES.D，hasvt（[ID]） AS hsvVT
来自A03_FILES;
其中HasVT定义如下：
------------------- -
公共函数hasVT（ID As Long）作为布尔值
Dim rst作为记录集
设置rst = CurrentDb.OpenRecordset（" SELECT A03_FILES.ID FROM A03_FILES
INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D WHERE A03_FILES.ID ="& ID&
" ;;"）
hasVT = rst.RecordCount
设置rst = Nothing
结束功能
--------------------
然后它似乎真的减慢了查询速度。有没有更快的方式来做这个而不改变查询的概念（即我知道我可以将它全部转化为SQL，这肯定会使它更快）。
在我看来，它减慢的原因是，对于
查询中的每一行，它必须打开和关闭该功能（当然，运行它实际上非常快）。
谢谢
- Nicolaas
您似乎很清楚导致问题的原因以及解决方法。
在这个特定的例子中，当你可以使用像Count（*）之类的标准SQL获得所需的数据时，你会很生气地调用外部函数
。我认为你必须有一些理由提出这样的问题，所以也许如果我们不能改变''查询的概念''也许你应该看一下
你正在打开的记录集 - 如果你只是使用：
设置rst = dbs.OpenRecordset（strSQL）你将打开一个记录集你
可以编辑。
设置rst = dbs.OpenRecordset（strSQL，dbOpenForwardOnly）打开一个更有效的，
只读记录集。
另外，将SQL更改为
SELECT COUNT（*）FROM A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D
WHERE ...
但最重要的是，使用标准SQL做到这一切。
blockquote>
然后它似乎真的减慢了查询速度。有没有更快的方式来做
而不改变查询的概念（即我知道我可以将它全部转化为SQL，这肯定会使它
更快） 。
为什么要保留这个功能？为什么不直接使用SQL？听起来你已经知道
需要做什么...... :)
这不是函数调用减慢它的速度，这就是你在
函数中做的事情。
我不会重复Stefan和Deko所说的，因为我认为''这是
最好的方法，但出于兴趣，如果你使用我的域名功能
替换
（ http://easyweb.easynet.co.uk/~trevor.../baslookup.zip ）你可以
达到相同的结果，例如
选择a，b，c，tCount（" *"，" A03_FILES INNER JOIN A65_vts ON A03_FILES.D =
A65_vts.D"，" A03_FILES.ID ="& ID）
不确定标准DCount（）会支持这个，但是我的只是
从你发送它的位创建一个SQL语句...
-
这个信号留下了意图空白的
Hi Gurus
When I have a query in which I use a small function, e.g.:
SELECT A03_FILES.ID, A03_FILES.D, hasvt([ID]) AS hsvVT
FROM A03_FILES;
where HasVT is defined below:
--------------------
Public Function hasVT(ID As Long) As Boolean
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT A03_FILES.ID FROM A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D WHERE
A03_FILES.ID= " & ID & ";")
hasVT = rst.RecordCount
Set rst = Nothing
End Function
--------------------
Then it seems to really slow down the query. Is there a faster way to do this without changing the concept of the query (i.e. I
know that I can make it all into SQL which would definitely make it faster).
It seems to me that the reason it slows down is that for each row in the query, it has to open and close the function (running it is
actually very fast of course).
Thank you
- Nicolaas解决方案
"windandwaves" <wi*********@coldmail.com> wrote in message
news:Ir*******************@news.xtra.co.nz...Hi Gurus
When I have a query in which I use a small function, e.g.:
SELECT A03_FILES.ID, A03_FILES.D, hasvt([ID]) AS hsvVT
FROM A03_FILES;
where HasVT is defined below:
--------------------
Public Function hasVT(ID As Long) As Boolean
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT A03_FILES.ID FROM A03_FILES
INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D WHERE A03_FILES.ID= " & ID &
";")
hasVT = rst.RecordCount
Set rst = Nothing
End Function
--------------------
Then it seems to really slow down the query. Is there a faster way to do
this without changing the concept of the query (i.e. I know that I can
make it all into SQL which would definitely make it faster).
It seems to me that the reason it slows down is that for each row in the
query, it has to open and close the function (running it is actually very
fast of course).
Thank you
- Nicolaas
You seem understand very well what is causing the problem and how to fix it.
In this particular example, you would be mad to call an external function,
when you could get the data you need by using standard SQL like Count(*). I
suppose you must have some reason for asking such a question, so perhaps if
we cannot change the ''concept of the query'' perhaps you should look at the
sort of recordset you are opening - if you simply use:
Set rst=dbs.OpenRecordset(strSQL) you will be opening a recordset which you
can edit.
Set rst=dbs.OpenRecordset(strSQL,dbOpenForwardOnly) opens a more efficient,
read-only recordset.
Also, change the SQL to
SELECT COUNT(*) FROM A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D
WHERE...
But best of all, do it all using standard SQL.
Then it seems to really slow down the query. Is there a faster way to do this without changing the concept of the query (i.e. I know that I can make it all into SQL which would definitely make it
faster).
Why keep the function? Why not just use SQL? Sounds like you already know
what needs to be done... :)
It''s not the function call that slows it down, it''s what you do in the
function.
I won''t repeat what Stefan and Deko have stated as I think that''s the
best method but out of interest, if you used my domain function
replacements
(http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) you could
achieve the same result, e.g.
Select a,b,c,tCount("*","A03_FILES INNER JOIN A65_vts ON A03_FILES.D =
A65_vts.D","A03_FILES.ID= " & ID)
Not sure that the standard DCount() would support that but as mine just
creates a SQL statement from the bits you send it...
--
This sig left intentionally blank
这篇关于调用函数的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
