MS Access 2010所需的SQL查询优化 [英] Sql query optimisation needed for MS access 2010

问题描述

我有一个表MyTable，其中的项目存储有各自的参考代码，ReferenceCode，但每个ReferenceCode都有不同的版本，因为项目已经过一段时间更新，因此我们可以回顾每个ReferenceCode的历史记录。



我写了一些SQL，它提取出版本号最高的条目，以便提取每个ReferenceCode的最新版本并给我一个实时表。然而，似乎需要永远（5.5分钟！）将数据提取到一个表中 - 这就在Access本身！



表中大约有16,000个条目，其中超过一半是当前的实时版本。该表有大约20个字段，是的，我需要SELECT *。



在单个SQL语句中有更快的方法吗？



我的尝试：

I have a table, MyTable, in which items are stored with their individual reference code, ReferenceCode but there are different versions of each ReferenceCode as the items have been updated over time so we can look back to historical records for each ReferenceCode.

I've written some SQL which pulls out the entries with the highest version number to as to extract the newest version of each ReferenceCode and give me a ‘live’ table. However, it seems to take forever (5.5 MINUTES!) to extract the data into a table - and that's in Access itself!

There are about 16,000 entries in the table, and just over half of those are the current live versions. The table has about 20 fields and yes, I do need to SELECT *.

Is there a quicker way of doing this in a single SQL statement?

What I have tried:

select * from [mytable] where ((([mytable].version)=(select max(x.version) from [mytable] as x where x.referencecode = [mytable].referencecode))) order by referencecode;

推荐答案

OP已确认以下查询更快（在他们纠正我的错误之后：0）

OP has confirmed that the following query is much faster (after they corrected my mistake :0)

select A.* from [myTable] A
INNER JOIN (select referencecode, max(myTable.[version]) as [version] from [myTable] group by referencecode) B
	ON B.referencecode = A.referencecode AND A.[version]=B.[version]
order by A.referencecode

我在评论中说连接总是比...子查询更快 - 这并非严格意义上说。在这种情况下是正确的，因为原始查询使用了相关的子查询 - 即它引用了外部查询...

I said in my comment that "Joins are always faster than ... sub-queries" - that is not strictly true. It's true in this case because the original query was using a correlated sub-query - i.e. it was referencing the outer query ...

select * from [mytable] where ((([mytable].version)=(select max(x.version) from [mytable] as x where x.referencecode = [mytable].referencecode))) order by referencecode;

请参阅相关子查询 - 维基百科 [ ^ ]

这篇关于MS Access 2010所需的SQL查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！