在Access中选择优化 [英] Select optimization in Access

问题描述

我遇到了严重的麻烦，我有一个巨大的细微查询，需要大量时间来执行.实际上，它冻结了Access，有时我不得不杀死它，查询看起来像这样:

I'm in serious trouble, I've a huge subtle query that takes huge time to execute. Actually it freezes Access and sometimes I have to kill it the query looks like:

SELECT
    ITEM.*,
    ERA.*,
    ORDR.*,
ITEM.COnTY1,
(SELECT TOP 1 New FROM MAPPING WHERE Old = ITEM.COnTY1) AS NewConTy1,
ITEM.COnValue1,
(SELECT TOP 1 KBETR FROM GEN_KUMV WHERE KNUMV = ERA.DOCCOND AND KSCHL = (SELECT TOP 1     New FROM MAPPING WHERE Old = ITEM.COnTY1)) AS NewCOnValue1
--... etc: this continues until ConTy40

FROM
GEN_ITEMS AS ITEM,
GEN_ORDERS AS ORDR,
GEN_ERASALES AS ERA

WHERE
ORDR.ORDER_NUM = ITEM.ORDER_NUM AND  -- link between ITEM and ORDR
ERA.concat = ITEM.concat -- link between ERA and ITEM

由于查询有效，因此我不会为您提供表模式，我想知道的是，是否有一种方法可以使用另一种技术来添加NewConTy1和NewConValue1，以使其效率更高.问题是Con *字段从1到40，所以我必须将它们对齐(ConTy1旁边的NewConTy1和New ConValue2旁边的NewConValue1 ...等等，直到40). ConTy#和ConTyValue#在ITEMS中(每个都在一个字段中) NewConty#和NewConValue#在ERA中(每个都在记录中)

I won't provide you with the tables schema since the query works, what I'd like to know is if there's a way to add the NewConTy1 and NewConValue1 using another technique to make it more efficient. The thing is that the Con* fields goes from 1 to 40 so I've to align them along (NewConTy1 next to ConTy1 with NewConValue1 next to new ConValue2... etc until 40). ConTy# and ConTyValue# are in ITEMS (each in a field) NewConty# and NewConValue# are in ERA (each in a record)

我真的希望我的解释足以弄清楚我的问题， 期待收到您的来信

I really hope my explanation is enough to figure out my issue, Looking forward to hearing from you guys

忽略SELECTS中的TOP 1，这是因为当前的数据转储不准确，它将在以后删除

Ignore the TOP 1 in the SELECTS, it's because current dumps of data I have aren't accurate it's going to be removed later

我的查询还返回了最多230个字段的大声笑

Another thing my query returns up to 230 fields also lol

谢谢 响亮

推荐答案

您是否考虑过使用联合查询对项目进行规范化?

Have you considered a union query to normalize items?

SELECT "ConTy1" As CTName, Conty1 As CTVal, 
       "ConTyValue1" As CTVName,  ConTyValue1" As CTVVal
       FROM ITEMS 
UNION ALL 
SELECT "ConTy2" As CTName, Conty2 As CTVal, 
       "ConTyValue2" As CTVName,  ConTyValue2" As CTVVal
       FROM ITEMS
<...>
UNION ALL 
SELECT "ConTy40" As CTName, Conty40 As CTVal, 
       "ConTyValue40" As CTVName,  ConTyValue40" As CTVVal
       FROM ITEMS

这可以是链接到您的主查询的单独查询，也可以是主查询的子查询(如果这样更方便).然后，应该很容易在ERA中绘制与NewConty#和NewConValue#的关系.

This can either be a separate query that links in to your main query, or a sub query of your main query, if that is more convenient. It should then be easy enough to draw in the relationship to the NewConty# and NewConValue# in ERA.

这篇关于在Access中选择优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！