sql-server - SQL LIKE 参数 : huge performance hit

Question

我有这个查询:

select top 100 id, email, amount from view_orders
    where email LIKE '%test%' order by created_at desc

运行时间不到一秒。

现在我想参数化它:

declare @m nvarchar(200)
set @m = '%test%'
SELECT TOP 100 id, email, amount FROM view_orders
    WHERE email LIKE @m ORDER BY created_at DESC

5 分钟后，它仍在运行。对于任何其他类型的参数测试(如果我用“=”替换“like”)，它会下降到第一个查询性能级别。

我使用的是 SQL Server 2008 R2。

我尝试过 OPTION(RECOMPILE) ，它下降到 6 秒，但仍然慢得多(非参数化查询是瞬时的)。由于这是一个我预计会经常运行的查询，因此这是一个问题。

表的列有索引，但 View 没有，我不知道这是否会有所不同。

该 View 连接 5 个表:一张有 3,154,333 行(用户)，一张有 1,536,111 行(订单)，还有 3 个最多几十行(订单类型等)。搜索是在“用户”表(有 300 万行)上完成的。

硬编码值:

参数:

更新

我已使用SET STATISTICS IO ON运行查询。这是结果(抱歉我不知道如何阅读):

硬编码值:

Table 'currency'. Scan count 1, logical reads 201.

Table 'order_status'. Scan count 0, logical reads 200.

Table 'payment'. Scan count 1, logical reads 100.

Table 'gift'. Scan count 202, logical reads 404.

Table 'order'. Scan count 95, logical reads 683.

Table 'user'. Scan count 1, logical reads 7956.

参数:

Table 'currency'. scan count 1, logical reads 201.

Table 'order_status'. scan count 1, logical reads 201.

Table 'payment'. scan count 1, logical reads 100.

Table 'gift'. scan count 202, logical reads 404.

Table 'user'. scan count 0, logical reads 4353067.

Table 'order'. scan count 1, logical reads 4357031.

更新2

此后我看到了“强制索引使用”提示:

SELECT TOP 100 id, email, amount
FROM view_orders with (nolock, index=ix_email)
WHERE email LIKE @m
ORDER BY created_at DESC

但不确定它是否有效，我不再在这个地方工作了。

Answer 1

这可能是参数嗅探问题。更好的索引或全文搜索是可行的方法，但您也许可以获得可行的折衷方案。尝试:

SELECT TOP 100 A, B, C FROM myview WHERE A LIKE '%' + @a + '%'
OPTION (OPTIMIZE FOR (@a = 'testvalue'));

(就像 Sean Coetzee 所建议的那样，我不会在参数中传递通配符)