什么是一个通配符搜索的最佳优化技术，通过10万条记录中的SQL表 [英] What is the best optimization technique for a wildcard search through 100,000 records in sql table

问题描述

我工作的一个ASP.NET MVC应用程序。本申请是由200个用户使用。这些
用户不断（每5分钟）搜索从10万的项目列表中的项目（这个名单是怎么回事了1-2％，每月增加）。 100000物品此列表存储在SQL Server表。

搜索是一个通配符搜索

例如：

 选择itemCode，ITEMNAME，从tblItems 
 ItemDesc 
其中ITEMNAME LIKE'％搜索内容％'
  

的搜索需要真快，因为主要业务依赖于搜索和选择的项目。

我会想知道如何获得最佳的性能。搜索结果必须拿出瞬间。

我曾尝试 -

1. 我试过预加载整个10万条记录到内存缓存，然后从内存缓存读取。我试图避免SQL Server的每一个搜索呼叫。

   
   
   

   这需要花费大量的时间。一个项目每次用户搜索，我们从内存缓存中检索10万条记录，然后做搜索。这是几乎回吐2-3倍的时间比直接的SQL搜索。




2. 我想这样做对SQL Server表中直接搜索，但结果限制为只50条记录一次（使用前50名）

   
   
   

   这似乎是好了，但仍然没有在那里了，我们正在寻求

   性能

我想听到的可能解决方案，并链接到任何文章/代码。

谢谢提前

解决方案

运行SQL事件探查器，并做了调整轮廓。这将使索引上的建议执行对数据库。

此外，查询，如下面将是值得一试。

  SELECT * 
起价
（
选择ROW_NUMBER）OVER（ORDER（BY ColumnA）AS ROWNUMBER，itemCode，ITEMNAME，ItemDesc 
 FROM tblItems 
，其中ITEMNAME LIKE ％FooBar的％'
）作为RowResults 
，其中ROWNUMBER> = 1 AND ROWNUMBER< 50 
 ORDER BY ROWNUMBER 
  

编辑：更新查询，以反映你的真实场景。

I am working on an ASP.NET MVC application. This application is used by 200 users. These users constantly (every 5 mins) search for an item from the list of 100,000 items (this list is going to increase every month by 1-2 %). This list of 100,000 items are stored in a SQL Server table.

The search is a wildcard search

eg:

Select itemCode, itemName, ItemDesc 
from tblItems
Where itemName like '%SearchWord%'

The searching needs to really fast since the main business relies on searching and selecting the item.

I would like to know how to get the best performance. The search results have to come up instantaneously.

What I have tried -

1. I tried pre-loading the entire 100,000 records into memcache and then reading from the memcache. I was trying to avoid the calls to SQL Server for every search.

   This takes a lot of time. Every time user searches for an item, we are retrieving 100,000 records from the memcache and then doing the search. This is taking almost 2-3 times more time than direct SQL searches.

2. I tried doing a direct search on the SQL Server table but limiting the results to only 50 records at a time (using top 50)

   This seems to be Ok but still no-where near the performance we are seeking

I would like to hear the possible solutions and links to any articles/code.

Thanks in advance

解决方案

Run SQL Profiler and do a tuning profile. This will make recommendations on indexes to execute against your database.

Also, a query such as the following would be worth a try.

SELECT  *
FROM    
( 
    SELECT    ROW_NUMBER() OVER ( ORDER BY ColumnA) AS RowNumber, itemCode, itemName, ItemDesc
    FROM      tblItems
    WHERE     itemName LIKE '%FooBar%'
) AS RowResults
WHERE   RowNumber >= 1 AND RowNumber < 50
ORDER BY RowNumber

EDIT: Updated query to reflect your real scenario.

这篇关于什么是一个通配符搜索的最佳优化技术，通过10万条记录中的SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！