問題描述

我有一個用戶表，其中有用戶名和應(yīng)用程序列.用戶名可能會重復(fù)，但用戶名 + 應(yīng)用程序的組合是唯一的，但我沒有在表上設(shè)置唯一約束(為了性能)

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)

問題:是否有任何區(qū)別(性能方面):

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 不會影響結(jié)果.我一直認為添加 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 字段的索引，則數(shù)據(jù)庫在隔離單個記錄之前甚至不必接觸表.此外，它已經(jīng)從表統(tǒng)計信息中知道這些值是唯一的，因此使用 top 沒有區(qū)別.

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.

這篇關(guān)于在 SELECT 查詢中使用 TOP 1 的性能影響的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！