27,582
社区成员




SET STATISTICS TIME ON
DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE @Process_Instance_Id
ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
CHECKPOINT
DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1 FROM ITWORKITEMS I
WHERE I.WIREFID LIKE 'nvevrqt6e25wzpvhpa95htn4h2.%'
ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC
CREATE PROC p
@Process_Instance_Id nVarChar(40)
AS SELECT 1 FROM ITWORKITEMS I WHERE I.WIREFID LIKE @Process_Instance_Id
GO
EXEC p 'nvevrqt6e25wzpvhpa95htn4h2.%'
CREATE PROC p @Process_Instance_Id varchar(50)
AS
SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE @Process_Instance_Id
GO
EXEC p 'nvevrqt6e25wzpvhpa95htn4h2.%'
如果优化程序认为WIREFID的选择性足够高,SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1
FROM ITWORKITEMS I WITH(INDEX(IX9_WORKITEMS))
WHERE I.WIREFID LIKE @Process_Instance_Id
-------
如果I.WIREFID LIKE'nvevrqt6e25wzpvhpa95htn4h2.%'
不能高选择性的过滤掉大部分记录的话,也就是符合条件的记录很多时,强制使用非聚焦索引,
会比直接表扫描成本还要高很多.
SELECT @@VERSION AS 'SQL Server Version'
DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE @Process_Instance_Id
ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC
OPTION (RECOMPILE) ---