Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Compute Scalar (SubString) ROBUST PLAN не прокатывает  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
У одного моего знакомого: при переходе на 2005-й в одном работающем до селе запросе нарвался на один глюк:
SELECT	 Substring(et.NODE_PATH, 4, CharIndex('^', et.NODE_PATH, 4)-4) --,*
FROM	     StaffTree	et
	JOIN StaffPosts	ep ON ep.DEP_ID	 = et.NODE_ID
	JOIN Orders	ot ON ot.POST_ID = ep.POST_ID
WHERE	    ot.DATE_START between ep.DATE_START and IsNull(ep.DATE_FINISH, '01.01.3000')
	AND ot.DATE_START between et.DATE_START and IsNull(et.DATE_FINISH, '01.01.3000')
--OPTION(ROBUST PLAN)
Msg 536, Level 16, State 5, Line 1
Недопустимый параметр длины передан функции SUBSTRING.

Глюк заключается в том, что на указанных (в WHERE) данных нет и не может быть ситуаций, при котором происходит выход из допустимого диапазона для SubString. Для понимания проблемы привожу план запроса:
План
StmtText
|--Hash Match(Inner Join, HASH:([et].[Node_id])=([ep].[Dep_ID]), RESIDUAL:([StaffTree].[Node_id] as [et].[Node_id]=[StaffPosts].[Dep_ID] as [ep].[Dep_ID] AND [Orders].[Date_start] as [ot].[Date_start]>=[StaffTree].[Date_start] as [et].[Date_start] AND [Orders].[Date_start] as [ot].[Date_start]<=[Expr1008]))
|--Compute Scalar(DEFINE:([Expr1006]=substring([StaffTree].[Node_path] as [et].[Node_path],(4),charindex('^',[StaffTree].[Node_path] as [et].[Node_path],(4))-(4)), [Expr1008]=isnull([StaffTree].[Date_finish] as [et].[Date_finish],'3000-01-01 00:00:00.000')))
| |--Index Scan(OBJECT:([StaffTree].[IX_StaffTree_1] AS [et]))
|--Hash Match(Inner Join, HASH:([ot].[Post_ID])=([ep].[Post_ID]), RESIDUAL:([Orders].[Post_ID] as [ot].[Post_ID]=[StaffPosts].[Post_ID] as [ep].[Post_ID] AND [Orders].[Date_start] as [ot].[Date_start]>=[StaffPosts].[Date_start] as [ep].[Date_start] AND [Orders].[Date_start] as [ot].[Date_start]<=[Expr1007]))
|--Clustered Index Scan(OBJECT:([Orders].[PK_Orders] AS [ot]))
|--Compute Scalar(DEFINE:([Expr1007]=isnull([StaffPosts].[Date_finish] as [ep].[Date_finish],'3000-01-01 00:00:00.000')))
|--Clustered Index Scan(OBJECT:([StaffPosts].[PK_StaffPosts] AS [ep]))
Не обращайте внимание на сканы и прочую лабудень - скрипт изменён (обрезан) и не в этом суть. Видно что сначала вычисляется Compute Scalar, а потом перемножается Hash Match!
Глюк обошли путём наложения дополнительного дублирующего выражения, там кажись появился фильтр в Index Scan.
Но, вопрос: как стандартными средствами это решить (т.е. чтоб оптимизатор всё-таки сдвинул Compute Scalar выше Hash Match в данном запросе), а не обойти (изменить логику запроса).
OPTION(ROBUST PLAN) план не меняет. Кто-то сталкивался с подобным, и решал проблему? Или просто тупо обходили. :)
Да, чуть не забыл глюк появляется с начала 2005-го до текущего (и последующих :) ):
Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) Mar 23 2007 16:15:11 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
17 май 08, 11:55    [5677222]     Ответить | Цитировать Сообщить модератору
 Re: Compute Scalar (SubString) ROBUST PLAN не прокатывает  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А так?
SELECT	 Substring(NODE_PATH, 4, CharIndex('^', .NODE_PATH, 4)-4) --,*
from (
SELECT	 et.NODE_PATH --,*
FROM	     StaffTree	et
	JOIN StaffPosts	ep ON ep.DEP_ID	 = et.NODE_ID
	JOIN Orders	ot ON ot.POST_ID = ep.POST_ID
WHERE	    ot.DATE_START between ep.DATE_START and IsNull(ep.DATE_FINISH, '01.01.3000')
	AND ot.DATE_START between et.DATE_START and IsNull(et.DATE_FINISH, '01.01.3000')
) a
17 май 08, 12:33    [5677252]     Ответить | Цитировать Сообщить модератору
 Re: Compute Scalar (SubString) ROBUST PLAN не прокатывает  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
tpg
А так?
Такие манипуляции обычно до одного места в MS, такое лишь в Oracle может прокатить. ;)
Проверил - всё равно выдаёт ошибку. План не поменялся.
17 май 08, 14:45    [5677451]     Ответить | Цитировать Сообщить модератору
 Re: Compute Scalar (SubString) ROBUST PLAN не прокатывает  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Насильно loop'нуть второй джойн
-------------------------------------
Jedem Das Seine
17 май 08, 15:29    [5677531]     Ответить | Цитировать Сообщить модератору
 Re: Compute Scalar (SubString) ROBUST PLAN не прокатывает  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Maxx
Насильно loop'нуть второй джойн
Жестоко, но работает. В данном случае плохо приемлемо - скорость упала нехило (помимо времени, план хуже на два порядка).
Вариант с дополнительным условием (AND Len(et.NODE_PATH) > 4) лучше - не меняет основную структуру запроса.
SELECT	Substring(et.NODE_PATH, 4, CharIndex('^', et.NODE_PATH, 4)-4),et.NODE_PATH,et.NODE_PATH --,*
FROM	                StaffTree	et
	INNER LOOP JOIN StaffPosts	ep ON ep.DEP_ID	 = et.NODE_ID
	INNER      JOIN Orders		ot ON ot.POST_ID = ep.POST_ID
WHERE	    ot.DATE_START between ep.DATE_START and IsNull(ep.DATE_FINISH, '01.01.3000')
	AND ot.DATE_START between et.DATE_START and IsNull(et.DATE_FINISH, '01.01.3000')
--OPTION(ROBUST PLAN)
План обкуренный loop-ом
Внимание! Порядок данного соединения был выбран принудительно, поскольку использовалась подсказка локального соединения.
StmtText
|--Compute Scalar(DEFINE:([Expr1006]=substring([StaffTree].[Node_path] as [et].[Node_path],(4),charindex('^',[StaffTree].[Node_path] as [et].[Node_path],(4))-(4))))
|--Parallelism(Gather Streams)
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([ep].[Post_ID])=([ot].[Post_ID]), RESIDUAL:([StaffPosts].[Post_ID] as [ep].[Post_ID]=[Orders].[Post_ID] as [ot].[Post_ID] AND [Orders].[Date_start] as [ot].[Date_start]>=[StaffPosts].[Date_start] as [ep].[Date_start] AND [Orders].[Date_start] as [ot].[Date_start]<=isnull([StaffPosts].[Date_finish] as [ep].[Date_finish],'3000-01-01 00:00:00.000') AND [Orders].[Date_start] as [ot].[Date_start]>=[StaffTree].[Date_start] as [et].[Date_start] AND [Orders].[Date_start] as [ot].[Date_start]<=isnull([StaffTree].[Date_finish] as [et].[Date_finish],'3000-01-01 00:00:00.000')))
|--Sort(ORDER BY:([ep].[Post_ID] ASC))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ep].[Post_ID]), WHERE:(PROBE([Bitmap1007])=TRUE))
| |--Nested Loops(Inner Join, WHERE:([StaffPosts].[Dep_ID] as [ep].[Dep_ID]=[StaffTree].[Node_id] as [et].[Node_id]))
| |--Index Scan(OBJECT:([StaffTree].[IX_StaffTree_1] AS [et]))
| |--Table Spool
| |--Clustered Index Scan(OBJECT:([StaffPosts].[PK_StaffPosts] AS [ep]))
|--Sort(ORDER BY:([ot].[Post_ID] ASC))
|--Bitmap(HASH:([ot].[Post_ID]), DEFINE:([Bitmap1007]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ot].[Post_ID]))
|--Clustered Index Scan(OBJECT:([Orders].[PK_Orders] AS [ot]))

Вопрос: знали или предположили, что лупы более "правильны"? Вы знаете/предполагаете почему Compute Scalar ниже Hash Match?
Но, мне всё никак не понятно, почему? Не силён - не знаю всех особенностей. Но по моему, это баг.
Во первых: не оптимально - зачем вычислять, если строка может и не нужна.
Во вторых: ROBUST PLAN, по документации, изменяет поведение оптимизатора на учёт этих выходов их диапазона для SubString. Косячат.
Есть ли официальное описание bug-а или объяснение, что так должно быть и прооптимизируется ли в будующем?
Если конечно не предложится что-нидь сильнее ROBUST-а, который объяснит, что политика MS по строкам, всётаки не такая жестокая.
17 май 08, 17:57    [5677748]     Ответить | Цитировать Сообщить модератору
 Re: Compute Scalar (SubString) ROBUST PLAN не прокатывает  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mnior
Но по моему, это баг.
Во первых: не оптимально - зачем вычислять, если строка может и не нужна.
Во вторых: ROBUST PLAN, по документации, изменяет поведение оптимизатора на учёт этих выходов их диапазона для SubString. Косячат.
Есть ли официальное описание bug-а или объяснение, что так должно быть и прооптимизируется ли в будующем?
Если конечно не предложится что-нидь сильнее ROBUST-а, который объяснит, что политика MS по строкам, всётаки не такая жестокая.
Неужто никто не сталкивался с "замученной" нарезкой строк? Если так, тогда понятна политика MS. :)
20 май 08, 13:07    [5688202]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить