在SQL中，使用DISTINCT如何影响性能？

I am attempting to select a distinct list where duplicates are created over several fields. For example,

我试图选择一个不同的列表,在多个字段上创建重复项。例如,

SELECT tablename.field1Date, 
       tablename.field2Number, 
       tablename.field3Text 
FROM tablename;

Would select duplicating records over the date, number and text fields respectively.

将分别在日期,数字和文本字段上选择重复记录。

Now, when I select distinct records to provide what I am looking for, the performance seems to decrease dramatically.

现在,当我选择不同的记录来提供我正在寻找的东西时,性能似乎会急剧下降。

SELECT DISTINCT tablename.field1Date,
                tablename.field2Number, 
                tablename.field3Text 
FROM tablename;

Is there any known reasons for this? I must admit I am using MS Access 2003 which may be the issue.

这有什么原因吗?我必须承认我正在使用MS Access 2003,这可能是个问题。

3 个解决方案

#1

Yes, basically it has to sort the results and then re-processed to eliminate the duplicates. This cull could also be being done during the sort, but we can only speculate as to how exactly the code works in the background. You could try and improve the performance by creating an index composed of all three (3) fields.

是的,基本上它必须对结果进行排序,然后重新处理以消除重复。这种剔除也可以在排序期间完成,但我们只能推测代码在后台运行的确切程度。您可以通过创建由所有三(3)个字段组成的索引来尝试提高性能。

#2

This page has tips on improving your query performance and also some information on using the performance analyzer. It will tell you what if any indexes are needed.

此页面提供了有关提高查询性能的提示以及有关使用性能分析器的一些信息。它会告诉你如果需要任何索引。

http://support.microsoft.com/kb/209126

#3

Yes, the application needs to compare every record to the "distinct" records cache as it goes. You can improve performance by using an index, particularly on the numeric and date fields.

是的,应用程序需要将每个记录与“不同”记录缓存进行比较。您可以使用索引来提高性能,尤其是在数字和日期字段上。

#1

#2