在 SELECT 查询中使用 TOP 1 的性能影响 [英] Performance effect of using TOP 1 in a SELECT query

问题描述

我有一个用户表，其中有用户名和应用程序列.用户名可能会重复，但用户名 + 应用程序的组合是唯一的，但我没有在表上设置唯一约束(为了性能)

I have a User table where there are a Username and Application columns. Username may repeat but combination of Username + Application is unique, but I don't have the unique constraint set on the table (for performance)

问题:是否有任何区别(性能方面):

Question: will there be any difference (performance-wise) between :

SELECT * FROM User where UserName='myuser' AND Application='myapp'

AND -

SELECT TOP 1 * FROM User where UserName='myuser' AND Application='myapp'

由于 Username + Application 的组合是唯一的，因此两个查询将始终返回不超过一条记录，因此 TOP 1 不会影响结果.我一直认为添加 TOP 1 会真正加快速度，因为 sql server 会在找到匹配项后停止查找，但我最近在一篇文章中读到，使用 TOP 实际上会减慢速度，建议避免使用，尽管它们没有解释原因.

As combination of Username + Application is unique, both queries will always return no more than one record, so TOP 1 doesn't affect the result. I always thought that adding TOP 1 will really speed things up as sql server would stop looking after it found one match, but I recently read in an article that using TOP will actually slow things down and it's recommended to avoid, though they haven't explained why.

有什么意见吗?

谢谢！安德烈

推荐答案

您可能会因使用 top 而获得一些性能差异，但使用索引可以获得真正的性能.

You may get some performance difference from just using top, but the real performance you get by using indexes.

如果您有 UserName 和 Application 字段的索引，则数据库在隔离单个记录之前甚至不必接触表.此外，它已经从表统计信息中知道这些值是唯一的，因此使用 top 没有区别.

If you have an index for the UserName and Application fields, the database doesn't even have to touch the table until it has isolated the single record. Also, it will already know from the table statistics that the values are unique, so using top makes no difference.

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