SQL Distinct 关键字会降低性能? [英] SQL Distinct keyword bogs down performance?

问题描述

我收到了一个使用 distinct 关键字的 SQL 查询.当我尝试运行查询时，至少需要一分钟才能将包含数十万条记录的两个表连接起来并实际返回一些内容.

I have received a SQL query that makes use of the distinct keyword. When I tried running the query it took at least a minute to join two tables with hundreds of thousands of records and actually return something.

然后我去掉了区别，它在 0.2 秒后又回来了.distinct 关键字真的会让事情变得那么糟糕吗?

I then took out the distinction and it came back in 0.2 seconds. Does the distinct keyword really make things that bad?

这是查询:

SELECT DISTINCT
    c.username, o.orderno, o.totalcredits, o.totalrefunds,
    o.recstatus, o.reason 
FROM management.contacts c 
    JOIN management.orders o ON (c.custID = o.custID)
WHERE o.recDate > to_date('2010-01-01', 'YYYY/MM/DD')

推荐答案

是的，因为使用 DISTINCT 会(有时根据评论)导致结果被排序.对数百条记录进行排序需要时间.

Yes, as using DISTINCT will (sometimes according to a comment) cause results to be ordered. Sorting hundreds of records takes time.

尝试 GROUP BY 你所有的列，它有时会导致查询优化器选择更有效的算法(至少我注意到 Oracle 有显着的性能提升).

Try GROUP BY all your columns, it can sometimes lead the query optimiser to choose a more efficient algorithm (at least with Oracle I noticed significant performance gain).

这篇关于SQL Distinct 关键字会降低性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！