在SQL中，使用DISTINCT如何影响性能? [英] In SQL, How does using DISTINCT affect performance?

问题描述

我正在尝试选择一个不同的列表，其中在多个字段上创建重复项.例如

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;

有什么已知的原因吗?我必须承认我正在使用MS Access 2003，这可能是问题所在.

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

推荐答案

是的，基本上，它必须对结果进行排序，然后进行重新处理以消除重复项.也可以在排序过程中进行此剔除，但是我们只能推测代码在后台的工作方式.您可以尝试通过创建由所有三(3)个字段组成的索引来提高性能.

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.

这篇关于在SQL中，使用DISTINCT如何影响性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！