全局变量正在减慢查询速度 [英] Global variables are slowing down the query
问题描述
您好,
我将以下查询设置为测试,运行正常:
SELECT STATUSHISTORIE。 *
来自STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID =
PROBLEM_DE.PROBLEMNR
WHERE(((STATUSHISTORIE.STATUSDATUM)<#1 / 1/2005#)和
((PROBLEM_DE.DATENBEREICH)=''SPMO'')和
((左(([问题解答]。[MODULZUORDNUNG] ),InStr([PROBLEM_DE]。[MODULZUORDNUNG]," - ") - 2)))='''K29'')
AND((PROBLEM_DE.ERLSTAND)<>" ; WEIF"))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;
然后我设置了两个全局变量(一个字符串和一个日期)和
各自的函数返回它们 - ReturnE()和ReturnKW()。现在
我的查询看起来像这样,但需要很长时间才能运行:
SELECT STATUSHISTORIE。*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON
[STATUSHISTORIE]。[PROBLEM_ID] = [PROBLEM_DE]。[问题]
WHERE(((STATUSHISTORIE.STATUSDATUM)< ReturnKW())AND
((PROBLEM_DE.DATENBEREICH)=''SPMO'')和
((左(([问题解答]。[MODULZUORDNUNG]),InStr([问题解答]。[MODULZUORDNUNG] ]," - ") - 2)))= ReturnE(
))AND((PROBLEM_DE.ERLSTAND)<>" WEIF"))
ORDER BY [STATUSHISTORIE]。[PROBLEM_ID],[STATUSHISTORIE]。[STATUSDATUM];
这些表实际上是从ODBC数据源设置的视图。可以
有人请告诉我为什么这些全局变量导致
交通拥堵? :-)
提前致谢
J
< blockquote>我再次...应该只是添加我的公共函数返回
全局变量
Public gstrE As String''全局变量:包含E用于查询
Public gdatKW As Date
公共函数ReturnE()
ReturnE = gstrE
结束功能
公共函数返回KW()
ReturnKW = gdatKW
>
结束功能
这是真的。在Access 2000及更高版本中,任何用户定义的VBA函数都像
一样锚。
你能否通过读取控件上的值来避免这种情况表格
而不是使用变量?
-
Allen Browne - 微软MVP。西澳大利亚州珀斯。
访问用户提示 - http:// allenbrowne.com/tips.html
回复群组,而不是mvps dot org的allenbrowne。
" Jean" < JE ********** @ hotmail.com>在消息中写道
news:11 ******************** @ g43g2000cwa.googlegrou ps.com ...您好,
我有以下查询,我设置为测试,运行正常:
SELECT STATUSHISTORIE。*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID =
PROBLEM_DE.PROBLEMNR
WHERE(((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#)AND
((PROBLEM_DE.DATENBEREICH)=''SPMO '')AND
(((左(([问题解答]。[MODULZUORDNUNG]),InStr([问题解答]。[MODULZUORDNUNG], - ") - 2)))='''K29'' )
AND((PROBLEM_DE.ERLSTAND)<>"WEIF))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;
我接着设置两个全局变量(一个字符串和一个日期)和
各自的函数来返回它们 - ReturnE()和ReturnKW()。现在
我的查询看起来像这样,但需要很长时间才能运行:
SELECT STATUSHISTORIE。*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON
[STATUSHISTORIE]。[问题ID] = [PROBLEM_DE]。[问题]
在哪里(((STATUSHISTORIE.STATUSDATUM)< ReturnKW())和
((问题解答:DATENBEREICH)=''SPMO'')和
( ((左(([PROBLEM_DE]。[MODULZUORDNUNG]),InStr([PROBLEM_DE]。[MODULZUORDNUNG]," - ") - 2)))= ReturnE(
))AND((PROBLEM_DE.ERLSTAND )<>" WEIF")
ORDER BY [STATUSHISTORIE]。[PROBLEM_ID],[STATUSHISTORIE]。[STATUSDATUM];
表格实际上是从ODBC数据源。可以
任何人请告诉我为什么这些全局变量导致交通拥堵? :-)
提前致谢
J
是的,我想我可以使用这种方法。 />
最初,我让用户在ComboBox中选择字符串,并从ListBox的第二列中选择
日期。我必须在日期ListBox中迭代
,因为它处于扩展模式,用户可以选择
多个条目。
但重点是:如何直接在SQL中引用表单's control-
的值?或者在QBE网格中做到了吗?我从未见过
这样做,如果你能告诉我,我将不胜感激。
感谢您的支持!
Hello,
I have the following query that I set up as a test, and it runs fine:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID =
PROBLEM_DE.PROBLEMNR
WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND
((PROBLEM_DE.DATENBEREICH)=''SPMO'') AND
(((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=''K29'')
AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;
I then set up two global variables ( a String and a Date) and
respective functions to return them - ReturnE( ) and ReturnKW( ). Now
my query looks like this, but takes ages to run:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON
[STATUSHISTORIE].[PROBLEM_ID]=[ PROBLEM_DE].[PROBLEMNR]
WHERE (((STATUSHISTORIE.STATUSDATUM)<ReturnKW( ) ) AND
((PROBLEM_DE.DATENBEREICH)=''SPMO'') AND
(((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE(
) ) AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY [STATUSHISTORIE].[PROBLEM_ID], [STATUSHISTORIE].[STATUSDATUM];
The tables are actually Views set up from an ODBC Data source. Can
anyone please tell me why these global variables are causing the
traffic jam? :-)
Thanks in advance
J
Me again...should maybe just add my Public functions that return the
global variables
Public gstrE As String ''global variable: contains E used for query
Public gdatKW As Date
Public Function ReturnE()
ReturnE = gstrE
End Function
Public Function ReturnKW()
ReturnKW = gdatKW
End Function
It''s true. In Access 2000 and later, any user-defined VBA function acts like
an anchor.
Could you avoid this situation by reading the values from controls on a form
instead of using variables?
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jean" <je**********@hotmail.com> wrote in message
news:11********************@g43g2000cwa.googlegrou ps.com...Hello,
I have the following query that I set up as a test, and it runs fine:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID =
PROBLEM_DE.PROBLEMNR
WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND
((PROBLEM_DE.DATENBEREICH)=''SPMO'') AND
(((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=''K29'')
AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;
I then set up two global variables ( a String and a Date) and
respective functions to return them - ReturnE( ) and ReturnKW( ). Now
my query looks like this, but takes ages to run:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON
[STATUSHISTORIE].[PROBLEM_ID]=[ PROBLEM_DE].[PROBLEMNR]
WHERE (((STATUSHISTORIE.STATUSDATUM)<ReturnKW( ) ) AND
((PROBLEM_DE.DATENBEREICH)=''SPMO'') AND
(((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE(
) ) AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY [STATUSHISTORIE].[PROBLEM_ID], [STATUSHISTORIE].[STATUSDATUM];
The tables are actually Views set up from an ODBC Data source. Can
anyone please tell me why these global variables are causing the
traffic jam? :-)
Thanks in advance
J
Yes, I guess I could use that method.
Originally, I have the user select the string in a ComboBox, and the
date(s) from the second column in a ListBox. I have to iterate through
the date ListBox, as it is in Extended mode and a user can select
multiple entries.
But the point is: How do I reference the value of a form''s control -
directly in SQL? Or does one do it in the QBE grid? I have never seen
this done so I would appreciate it if you could tell me.
Thanks for your support so far!
这篇关于全局变量正在减慢查询速度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!