Есть 2 таблицы:
Resources(ресурсы, на тестовой БД ~12 000 записей, в боевой ~ 30M) и
ResourceExtParams (дополнительные параметры, пока практически пустая)
Есть задача по добавлению/изменению дополнительных параметров ресурсов с определенным значением поля Resources.HashCode - хранимого вычисляемого поля (добавил только что)
1.
ALTER TABLE [dbo].[Resources] ADD [HashCode] AS (CHECKSUM(ISNULL(Name,'')+ ' ' + ISNULL(Code,'') + ' ' + ISNULL(UnitName,'')+ ' ' + CAST([Type] as NVARCHAR(10)))) PERSISTED NOT NULL
с построенным индексом
1.
2.
3.
4.
5.
6.
CREATE NONCLUSTERED INDEX [IX_HashCode] ON [dbo].[Resources]
(
[HashCode] ASC
)
INCLUDE ( [Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
Индекс включает поле Id, т.к. кластерный индекс построен по другому полю.
Была создана нехитрая хранимка
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
CREATE PROCEDURE [dbo].[UpdateResourceSupplierType]
@NewSupplierTypeId TINYINT,
@HashCode int
AS
BEGIN
SET NOCOUNT ON
MERGE INTO dbo.ResourceExtParams
USING (select Id from dbo.Resources
where HashCode = @HashCode) r
on (r.Id = ResourceId)
when matched then
UPDATE SET SupplierTypeId = @NewSupplierTypeId
when not matched by target then
insert (ResourceId, SupplierTypeId)
values (r.Id, @NewSupplierTypeId);
END
Обратил внимание, что при вызове
1.
exec UpdateResourceSupplierType 5,-1157169989
который должен изменить 4 записи в ResourceExtParams и 1 добавить, вместо поиска по IX_HashCode идет сканирование кластерного индекса Resources (
план1 в приложении), причем из него кроме Id зачем-то извлекаются все составляющие поля HashCode, но не оно само.
Попробовал добавить подсказку
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
CREATE PROCEDURE [dbo].[UpdateResourceSupplierType]
@NewSupplierTypeId TINYINT,
@HashCode int
AS
BEGIN
SET NOCOUNT ON
MERGE INTO dbo.ResourceExtParams
USING (select Id from dbo.Resources WITH (INDEX(IX_HashCode))
where HashCode = @HashCode) r
on (r.Id = ResourceId)
when matched then
UPDATE SET SupplierTypeId = @NewSupplierTypeId
when not matched by target then
insert (ResourceId, SupplierTypeId)
values (r.Id, @NewSupplierTypeId);
END
Выдал сообщение об ошибке
1.
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Что удивительно, т.к запуск
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
declare @NewSupplierTypeId int = 6,
@HashCode int = -1157169989
MERGE INTO dbo.ResourceExtParams
USING (select Id from dbo.Resources WITH (INDEX(IX_HashCode))
where HashCode = @HashCode) r
on (r.Id = ResourceId)
when matched then
UPDATE SET SupplierTypeId = @NewSupplierTypeId
when not matched by target then
insert (ResourceId, SupplierTypeId)
values (r.Id, @NewSupplierTypeId);
прекрасно отрабатывает с желанным планом (
план с подсказками в приложении)
Версия сервера
1.
2.
3.
4.
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )
1. Почему не работают подсказки в ХП?
2. Почему такой странный план без них(надо бы попробовать перестроить кластерный индекс CIX_PositionId)?
