改進具有數百萬條記錄的自動完成框的查詢時間

我有一個非常基本的要求。我有一個儲存城市 ID（主鍵）和城市名稱（nvarchar 值）的表。其餘資訊儲存在單獨的表中。在我的 UI 中，我有一個自動完成框，它會在使用者開始輸入時列出所有城市。選擇後，應用程序獲取該特定城市的數據以填充。

只有兩列城市 id 和 name 的主表在 name 上建立索引。我目前正在執行以下查詢以獲取名稱列表。

Select cityname from citylist where cityname like '%' + citynamevar + '%'

該查詢應該為我提供包含使用者鍵入的值的所有名稱的列表。目前執行大約需要 2 分鐘。60 萬條記錄。

我還添加了一種在查詢執行時在開頭顯示 10 條記錄的方法。但即便如此，它也無濟於事，因為剩餘的數據載入速度太慢了。

我正在嘗試尋找可以改善此查詢時間的方法。到目前為止，我已經找到了全文搜尋，我相信這應該會有所幫助。我只是想知道是否有任何其他方法可以嘗試或任何技巧來讓查詢足夠快地響應。

我目前正在使用 SQL Server 2012。我嘗試直接在應用程序外部執行查詢，也使用 ssms。即便如此，它也花費了同樣多的時間。索引將查詢速度提高了大約 20%，但不足以產生意義。

編輯：

不知道為什麼我不能再對這篇文章發表評論了。無論如何，這是一個測試版本，看看自動完成框會有多大的影響。它在我的個人筆記型電腦（核心 i5，基本移動 GPU）上執行。我正在使用帶有 wpf 的 SQL Server 2012、.net 4.5。

我可以要求使用者通過輸入國家名稱來減少搜尋空間。但這應該是一個通用的解決方案。在某些情況下，我們沒有辦法減少搜尋空間。它必須是“包含”搜尋，而不是“開始於”。這可能是索引幾乎沒有任何影響的原因。

該表只有 600k 條記錄，其中我們需要顯示 1000-10k。當使用者鍵入第三個字元時，查詢只執行一次，之後剩餘的字元在應用程序中從 1000-10k 條記錄中排序。我可以通過將字元數增加到 5 來減少影響，但是使用者將無法看到大多數城市名稱的任何建議。

您是否考慮過在 UI 中限制搜尋？在他們輸入 3 或 4 個字元之前不要啟動自動完成查詢。然後，不是尋找 6000 A*，而是第一次跳躍仍然是該數字的一小部分。

此外，@Jonathan Fite 關於刪除前導 % 的評論也是減少時間的好方法。除非，您的使用者開始尋找 ew Yor 或 shington…

領先的百分比會扼殺索引的使用

大多數使用者無論如何都希望基於第一個字元自動完成

大多數使用者不關心超過 1000

個在城市上有索引

select top (1000) city 
from citylist 
where cityname like citynamevar + '%' 
order by city;

如果您可以讓他們從下拉菜單中選擇一個狀態

select top (1000) city 
from citylist 
where cityname like citynamevar + '%'  
and state = statenamevar
order by city;

如果您在桌面應用程序中，則將城市下載到集合併搜尋集合。您最終需要多次往返數據庫並多次返回相同的數據

我還使用的一個技巧是在發送請求之前等待 1/10 秒，這樣他們就可以在多個 4+ 字元中猛擊。

如果他們想要超過 1000 個，那麼給他們一個按鈕或使用一個鍵（如製表符）來表示獲得更多

引用自：https://dba.stackexchange.com/questions/126256