我有一个包含大约100万条记录的表(运行sql Server 2008 Web).我有一个搜索例程,试图匹配产品代码和产品描述.
但是在某些情况下它很慢.下面是(cut-down)sql语句：
但是在某些情况下它很慢.下面是(cut-down)sql语句：
WITH AllProducts AS (
SELECT p.*,Row_Number() OVER (ORDER BY ProductId) AS RowNumber
FROM Product AS p
WHERE p.IsEnabled=1 AND
(
p.BaseSku = 'KPK-3020QWC-C' -- this on its own is fast
OR
CONTAINS(p.FreeTextStrings,'"KPK-3020QWC*"') -- and this on its own is fast,but not both
)
) SELECT * FROM AllProducts
WHERE RowNumber BETWEEN 1 AND 20;
请注意,如果我只是在[p.BaseSku =’KPK-3020QWC-C’]或[CONTAINS(p.FreeTextStrings,’“KPK-3020QWC *”’)上单独(但不是两者)比较它的瞬间.如果我将它们比较在一起需要年龄(几分钟) – 并且只返回一行.
IsEnabled和BaseSku被编入索引,FreeTextStrings是FTS索引的.
我记得这个工作很精细.
任何人都可以对此有所了解并提出一些解决方案吗？
执行计划文件可在此处获取：http://wiki.webgear.co.nz/GetFile.aspx?File=Temp%5cSearch%20Test.sqlplan.zip
解决方法
或者在sql Server上出了名的慢.至少可以说,它更加恶化.
尝试使用union将其拆分为两个查询：
WITH AllProducts AS (
select *,Row_Number() OVER (ORDER BY ProductId) AS RowNumber
from (
SELECT p.*
FROM Product AS p
WHERE p.IsEnabled=1 AND
p.BaseSku = 'KPK-3020QWC-C'
UNION
SELECT p.*
FROM Product AS p
WHERE p.IsEnabled=1 AND
CONTAINS(p.FreeTextStrings,'"KPK-3020QWC*"')
)
) SELECT * FROM AllProducts
WHERE RowNumber BETWEEN 1 AND 20;
