I've got a query here which does what it is supposed to but I'm wondering if it is possible to make it more efficient without splitting it into 2 separate queries.
我在这里有一个查询它应该做什么,但我想知道是否有可能使它更高效,而不将它分成2个单独的查询。
SELECT [Full UO Code] FROM HPP_MD
WHERE LEFT([Full UO Code],5) In
(SELECT IIF([Building]="Site" AND
([Function Name_Usage Type]<>"Property" AND
[Function Name_Usage Type]<>"Undeveloped Land"),[Business Entity],"") As Test
FROM HPP_MD);
This is just a simple Access query that I'm playing with. It is easy to get the results I require if I run the subquery as a 'create table' query and then run a second query against that new table to get the required data.
这只是我正在玩的一个简单的Access查询。如果我将子查询作为“创建表”查询运行,然后针对该新表运行第二个查询以获取所需数据,则很容易获得我需要的结果。
I created the query/subquery out of curiosity to see how it worked but it is pretty slow and I'm wondering if anything can be done to increase efficiency. Is creating a temporary column the correct thing to do here? I couldn't quite conceive of another way of doing this.
我出于好奇创建了查询/子查询,看看它是如何工作的,但它很慢,我想知道是否可以采取任何措施来提高效率。创建一个临时列是正确的事情吗?我无法想到另一种方法。
Any thoughts? I know the column headers are pretty atrocious but this was just a quick table I imported into Access and I couldn't be buggered to change the names in this case.
有什么想法吗?我知道列标题非常糟糕,但这只是我导入Access的一个快速表,在这种情况下我无法改变名称。
1 个解决方案
#1
2
It looks as if the IN always fails if the big AND comes out false. That's assuming [Full UO Code] is never the empty string, which may be wrong. But if I'm correct, you can get rid of the IIF, which basically can't be optimized, into a WHERE
如果大的AND出错,看起来IN总是会失败。假设[Full UO Code]永远不是空字符串,这可能是错误的。但是,如果我是正确的,你可以摆脱基本上无法优化的IIF到WHERE
/* ... IN */
(SELECT [Business Entity] FROM HPP_MD
WHERE
[Building]="Site" AND
([Function Name_Usage Type]<>"Property" AND
[Function Name_Usage Type]<>"Undeveloped Land")
If this is correct so far, you may be able to rewrite the subquery into a self-join. That may or may not be faster. In any case, [Building] and [Function Name_Usage Type] should be indexed.
如果到目前为止这是正确的,您可以将子查询重写为自联接。这可能会也可能不会更快。在任何情况下,都应该为[Building]和[Function Name_Usage Type]编制索引。
#1
2
It looks as if the IN always fails if the big AND comes out false. That's assuming [Full UO Code] is never the empty string, which may be wrong. But if I'm correct, you can get rid of the IIF, which basically can't be optimized, into a WHERE
如果大的AND出错,看起来IN总是会失败。假设[Full UO Code]永远不是空字符串,这可能是错误的。但是,如果我是正确的,你可以摆脱基本上无法优化的IIF到WHERE
/* ... IN */
(SELECT [Business Entity] FROM HPP_MD
WHERE
[Building]="Site" AND
([Function Name_Usage Type]<>"Property" AND
[Function Name_Usage Type]<>"Undeveloped Land")
If this is correct so far, you may be able to rewrite the subquery into a self-join. That may or may not be faster. In any case, [Building] and [Function Name_Usage Type] should be indexed.
如果到目前为止这是正确的,您可以将子查询重写为自联接。这可能会也可能不会更快。在任何情况下,都应该为[Building]和[Function Name_Usage Type]编制索引。