Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Табличные подсказки, вычисляемое поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 445
Есть 2 таблицы:
Resources(ресурсы, на тестовой БД ~12 000 записей, в боевой ~ 30M) и
ResourceExtParams (дополнительные параметры, пока практически пустая)

Есть задача по добавлению/изменению дополнительных параметров ресурсов с определенным значением поля Resources.HashCode - хранимого вычисляемого поля (добавил только что)
ALTER TABLE [dbo].[Resources] ADD [HashCode] AS (CHECKSUM(ISNULL(Name,'')+ ' ' + ISNULL(Code,'') + ' ' + ISNULL(UnitName,'')+ ' ' + CAST([Type] as NVARCHAR(10)))) PERSISTED NOT NULL

с построенным индексом
+
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, т.к. кластерный индекс построен по другому полю.
Была создана нехитрая хранимка
+
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

Обратил внимание, что при вызове
exec UpdateResourceSupplierType 5,-1157169989

который должен изменить 4 записи в ResourceExtParams и 1 добавить, вместо поиска по IX_HashCode идет сканирование кластерного индекса Resources (план1 в приложении), причем из него кроме Id зачем-то извлекаются все составляющие поля HashCode, но не оно само.

Попробовал добавить подсказку
+
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

Выдал сообщение об ошибке
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.

Что удивительно, т.к запуск
+
	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);

прекрасно отрабатывает с желанным планом (план с подсказками в приложении)

Версия сервера
+
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)?

К сообщению приложен файл (Планы.zip - 9Kb) cкачать
11 мар 16, 11:41    [18919028]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 445
2. Перестроение CIX_PositionId ничего не измменило
11 мар 16, 12:01    [18919134]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Шамиль Фаридович,

хинт может не работать в силу особенностей настройки базы, вида индекса. Т.е. если индекс принципиально не может быть использован, получим такую ошибку.
11 мар 16, 12:13    [18919187]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 445
Хинт выдает ошибку только при использовании его внутри ХП, если запустить запрос с использованием этого хинта то все прекрасно отрабатывает. как показано выше.
11 мар 16, 12:16    [18919201]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Шамиль Фаридович,

в таком случае SET настройки в консоли отличаются от тех, которые были использованы при создании процедуры.
11 мар 16, 12:29    [18919260]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 445
Владислав Колосов,

а какая опция запрещает/разрешает использование хинтов в ХП?
11 мар 16, 14:48    [18920155]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
sergeimv
Member

Откуда: Россия, г.Казань
Сообщений: 42
Описаны некоторые тонкости https://blogs.msdn.microsoft.com/craigfr/2009/04/28/implied-predicates-and-query-hints/
11 мар 16, 15:25    [18920377]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
sergeimv
Member

Откуда: Россия, г.Казань
Сообщений: 42
+ попробуйте переопределить параметр @HashCode внутри процедуры
declare @pHashCode int = @HashCode
и уже в запросе использовать его.
11 мар 16, 15:28    [18920394]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 445
sergeimv,
познавательная статейка, но к сожалению
sergeimv
+ попробуйте переопределить параметр @HashCode внутри процедуры
declare @pHashCode int = @HashCode
и уже в запросе использовать его.

не решает моей проблемы - выскакивает все та же ошибка.
Видимо, Executor упорно пытается подсчитать значение вычисляемого поле вместо того, чтобы взять его сохраненное значение.
И делает он это только при вызове ХП, запущенный в студии запрос отрабатывает без проблем.
Что нужно дописать в ХП, чтобы она тоже работала?
18 мар 16, 11:24    [18947067]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
o-o
Guest
вот тут SQL Server doesn't use index in stored procedure
мужик с таким же боролся,
ему уйму советов дали, но ничего не помогло
18 мар 16, 11:38    [18947153]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Шамиль Фаридович
Владислав Колосов,

а какая опция запрещает/разрешает использование хинтов в ХП?


использование хинтов - никакая. а вот индексы на вычисляемых столбцах могут использоваться только при определенных set-ах.

[quot BOL]
When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.
[quot]
18 мар 16, 12:13    [18947407]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 445
o-o,
улыбнуло) Правда описанный в топе пример у меня работает, возможно его пофиксили в каком-нить сервис-паке.

daw,
в точку! у меня при создании ХП было явно прописано
SET ANSI_NULLS OFF

Даже не знаю зачем.

Спасибо!
18 мар 16, 12:21    [18947474]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 445
Кстати, после проставления опций
daw
When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

сама собой решилась вторая проблема
Шамиль Фаридович
2. Почему такой странный план без них

То есть теперь без хинтов используется IX_HashCode.
18 мар 16, 12:34    [18947589]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Табличные подсказки, вычисляемое поле  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 53624
Владислав Колосов
Шамиль Фаридович,

хинт может не работать в силу особенностей настройки базы, вида индекса. Т.е. если индекс принципиально не может быть использован, получим такую ошибку.
коллеги, подскажите

в Оракле - если хинт не применим или не нравится оптимизатору, и фиг с ним, никакая ошибка не валится

Есть ли такой режим в MSSQL?

Ситуация:
есть табличная функция
внутри запрос -- несколько таблиц, юнионы, подзапросы, джойны, with, несколько or'ов
идет выбор или по диапазону одной даты, или по диапазону другой даты, все это размножается на or'ы других параметров
Вынес поиск id в with, добавил inner merge join, работает в разы быстрее
+
 ALTER FUNCTION [dbo].[get_tmp]
(
    @p_date_from    datetime,
    @p_date_to      datetime,
    @p_load_tasks   int, -- загружать ли подчиненные задачи
    @p_pocopy       int,
    -- where type
    -- 0 - по дате закрытия
    -- 1 - по дате создания
    -- 2 - по дате закрытия или дате SLA для просроченных за исключением отмененных
    @p_where_type   int,
    @p_folder       nvarchar(100) = N'М'
)

RETURNS TABLE AS RETURN
(

with ids as
(
    select INCIDENT_ID id
      from incidentsm1 obr
     where obr.CLOSE_TIME between cast(@p_date_from AT TIME ZONE 'FLE Standard Time' AT TIME ZONE 'UTC' as datetime)
                              and cast(@p_date_to   AT TIME ZONE 'FLE Standard Time' AT TIME ZONE 'UTC' as datetime)
           and @p_where_type in (0, 2)
           and not (isnull(obr.HPC_CANCELLED_BY_USER, '') = 't' and @p_where_type in (2))
           and (isnull(obr.folder, N'М') = @p_folder or isnull(@p_folder, '') = '' and isnull(obr.folder, N'М') <> N'H')
    union
    select INCIDENT_ID id
      from incidentsm1 obr
     where obr.open_TIME between cast(@p_date_from AT TIME ZONE 'FLE Standard Time' AT TIME ZONE 'UTC' as datetime)
                             and cast(@p_date_to   AT TIME ZONE 'FLE Standard Time' AT TIME ZONE 'UTC' as datetime)
           and @p_where_type = 1
           and (isnull(obr.folder, N'М') = @p_folder or isnull(@p_folder, '') = '' and isnull(obr.folder, N'М') <> N'H')
    union
    select INCIDENT_ID id
      from incidentsm1 obr
     where obr.HPC_NEXT_BREACH  between cast(@p_date_from AT TIME ZONE 'FLE Standard Time' AT TIME ZONE 'UTC' as datetime)
                                    and cast(@p_date_to   AT TIME ZONE 'FLE Standard Time' AT TIME ZONE 'UTC' as datetime)
            and @p_where_type in (2)
            and obr.sla_breach = 't'
            and (obr.CLOSE_TIME is null or obr.CLOSE_TIME between @p_date_from and @p_date_to)
           and (isnull(obr.folder, N'М') = @p_folder or isnull(@p_folder, '') = '' and isnull(obr.folder, N'М') <> N'H')
)
и далее это в подрапросах джойнится с кучей других таблиц, в т.ч. с incidentsm1



так работает нормально и быстро
+
    select *
      into #data
      from get_tmp(dateadd(second, -1, @p_open_time_ukr),
                   dateadd(second, +1, @p_open_time_ukr),
                   1,   -- @p_load_tasks
                   0,   -- @p_pocopy = 
                   1,   -- @p_where_type
                   @p_folder
                )



Но когда в селект к функции добавляю ограничение по конкретному id where id = @id
+
    select *
      into #data
      from get_tmp(dateadd(second, -1, @p_open_time_ukr),
                   dateadd(second, +1, @p_open_time_ukr),
                   1,   -- @p_load_tasks
                   0,   -- @p_pocopy = 
                   1,   -- @p_where_type
                   @p_folder
                )
    where request_id = @p_request_id;;
валится ошибка
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.
12 янв 19, 12:13    [21783532]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5612
andreymx,

option(recompile)
ну, вы понимаете, к чему это ведет
с другой стороны, если это несущественно - [skipped].
12 янв 19, 21:48    [21783815]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 53624
Сон Веры Павловны
andreymx,

option(recompile)
ну, вы понимаете, к чему это ведет
с другой стороны, если это несущественно - [skipped].
спасибо, буду пробовать
13 янв 19, 10:50    [21783942]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 53624
andreymx
Сон Веры Павловны
andreymx,

option(recompile)
ну, вы понимаете, к чему это ведет
с другой стороны, если это несущественно - [skipped].
спасибо, буду пробовать
я так подумал
Т.к. это табличная функция
Эту опцию придётся вставлять во все её вызова?
13 янв 19, 11:20    [21783947]     Ответить | Цитировать Сообщить модератору
 Re: Табличные подсказки, вычисляемое поле  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
andreymx
Эту опцию придётся вставлять во все её вызова?
Угу. Только все равно не поможет при наличии inner merge join, которого, кстати, в показонном коде нигде нет.
13 янв 19, 11:38    [21783952]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить