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

Откуда:
Сообщений: 827
Недавно, роясь в нашем окаменелом дерьме исследуя наследие великих, обнаружил несколько скалярных функций вот такого типа:

+

CREATE FUNCTION [dbo].[IsPlace](@value [nvarchar](255))
RETURNS [int] WITH EXECUTE AS CALLER
AS 
BEGIN
    Declare @yes int;

    SELECT @yes=
	   Case 
			When @value like '% поселок' then 1
			when @value like '% пос' then 1
			When @value like '% пос.' then 1
			When @value like '% п' then 1
			When @value like '% п.' then 1
			When @value like '% п-к.' then 1
			When @value like '% п-к' then 1
			When @value like '% рп' then 1
			When @value like '% рп.' then 1
			When @value like '% р-п' then 1
			When @value like '% раб.п.' then 1
			When @value like '% раб-п' then 1
			When @value like '% кп' then 1
			When @value like '% кп.' then 1
			When @value like '% дп'  then 1
			When @value like '% массив'  then 1
			When @value like '% нп' then 1
			When @value like '% нп.' then 1
			When @value like '% дп' then 1
			When @value like '% дп.' then 1
			When @value like '% округ' then 1
			When @value like '% дачи' then 1
			When @value like '% мкр' then 1 
			When @value like '% мкр.' then 1 

			When @value like '% село' then 1
			when @value like '% с.' then 1
			When @value like '% с' then 1
			When @value like '% с/с' then 1
			When @value like '% с\с' then 1
	   
			when @value like '% ч.' then 1
			When @value like '% ч' then 1
		   
			When @value like '% совхоз' then 1
			When @value like '% свх' then 1
			When @value like '% свх.' then 1
			When @value like '% с-з' then 1
			When @value like '% с/з' then 1
			When @value like '% с\з' then 1

			When @value like '% с/п' then 1
			When @value like '% с/о' then 1

			When @value like '% село' then 1
			when @value like '% с.' then 1
			When @value like '% с' then 1
			When @value like '% сл' then 1
			When @value like '% сл.' then 1

			When @value like '% деревня' then 1
			When @value like '% дер' then 1
			When @value like '% дер.' then 1
			When @value like '% д.' then 1
			When @value like '% д' then 1

			When @value like '% хутор' then 1
			When @value like '% хут' then 1
			When @value like '% хут.' then 1
			When @value like '% х.' then 1
			When @value like '% х' then 1

			When @value like '% пгт' then 1
			When @value like '% пгт.' then 1
			When @value like '% птг' then 1
			When @value like '% птг.' then 1

			When @value like '% станица' then 1 
			When @value like '% ст-ца' then 1 
			When @value like '% ст' then 1 
			When @value like '% ст.' then 1 
			When @value like '% с/т' then 1 
			When @value like '% с\т' then 1 

			When @value like '% станция' then 1
			When @value like '% п/ст' then 1

			When @value like '% аул' then 1
			When @value like '% а' then 1
			When @value like '% а.' then 1

			When @value like '% снт' then 1
			When @value like '% разъезд' then 1
			When @value like '% Учхоз' then 1
			When @value like '% ДНТ' then 1
			When @value like '% ДН/Т' then 1
			When @value like '% пансионат' then 1
			When @value like '% тер' then 1
			When @value like '% тер.' then 1

			When @value like '% городок' then 1
			When @value like '% ЗАТО' then 1

			When @value like '% рзд' then 1

			---------------------------------------------------------------
		   
			When @value like 'поселок %' then 1
			When @value like 'пос %' then 1
			When @value like 'пос.%'  then 1
			When @value like 'п %' then 1
			When @value like 'п.%' then 1
			When @value like 'п-к. %' then 1
			When @value like 'п-к.%' then 1
			When @value like 'рп %' then 1
			When @value like 'р-п %' then 1
			When @value like 'рп.%' then 1
			When @value like 'раб.п.%' then 1
			When @value like 'раб-п%' then 1
			When @value like 'кп %' then 1
			When @value like 'кп.%' then 1
			When @value like 'массив %' then 1
			When @value like 'нп %' then 1
			When @value like 'нп.%' then 1
			When @value like 'дп %' then 1
			When @value like 'дп.%' then 1
			When @value like 'разъезд %' then 1

			When @value like 'село %' then 1
			when @value like 'с.%' then 1
			When @value like 'с %' then 1
			When @value like 'с/с %' then 1
			When @value like 'с\с %' then 1
			When @value like 'сл %' then 1
			When @value like 'сл. %' then 1

			When @value like 'деревня%' then 1
			When @value like 'д. [А-Я][А-Я][А-Я][А-Я]%' then 1
			When @value like 'д [А-Я][А-Я][А-Я][А-Я]%' then 1
			When @value like 'дер %' then 1
			When @value like 'дер.%' then 1

			When @value like 'хутор %' then 1
			When @value like 'хут %' then 1
			When @value like 'хут.%' then 1
			When @value like 'х.%' then 1
			When @value like 'х %' then 1

			When @value like 'пгт %' then 1
			When @value like 'пгт.%' then 1
			When @value like 'птг %' then 1
			When @value like 'птг.%' then 1

			When @value like 'станица%' then 1 
			When @value like 'ст-ца%' then 1 
			When @value like 'ст %' then 1 
			When @value like 'ст.%' then 1
			When @value like 'с/т %' then 1 
			When @value like 'с\т %' then 1
			When @value like 'ж/д%' then 1 

			When @value like 'совхоз %' then 1
			When @value like 'с-з %' then 1
			When @value like 'с/з %' then 1
			When @value like 'с\з %' then 1
			When @value like 'свх %' then 1
			When @value like 'свх.%' then 1
			When @value like 'станция %' then 1
			When @value like 'п/ст %' then 1

			When @value like 'с/п %' then 1
			When @value like 'с/о %' then 1
		   
			When @value like 'аул %' then 1

			when @value like 'ч.%' then 1
			When @value like 'ч %' then 1
			When @value like 'снт. %' then 1
			When @value like 'снт %' then 1
			When @value like 'дп. %'  then 1
			When @value like 'дп %'  then 1
			When @value like 'округ %' then 1
			When @value like 'Учхоз %' then 1
			When @value like 'дачи %' then 1
			When @value like 'ДНТ %' then 1
			When @value like 'ДН/Т %' then 1
			When @value like 'пансионат %' then 1
			When @value like 'тер %' then 1
			When @value like 'тер.%' then 1

			When @value like 'городок %' then 1

			When @value like 'ЗАТО %' then 1

			When @value like 'рзд %' then 1

	   Else 0
	   End
    Return @yes
End



Я хотел уже переписать их так (по прежнему в скалярку, чтобы не ломать логику запросов, которые на них базируются):
+

Select top(1) * 
From 
(
Select 1 yes
Where exists (
	Select 1 
		from (values
	 ('% поселок' )
	,('% пос' )
	,('% пос.' )
	,('% п' )
	,('% п.' )
	,('% п-к.' )
	,('% п-к' )
	,('% рп' )
	,('% рп.' )
	,('% р-п' )
	,('% раб.п.' )
	,('% раб-п' )
	,('% кп' )
	,('% кп.' )
	,('% дп'  )
	,('% массив'  )
	,('% нп' )
	,('% нп.' )
	,('% дп' )
	,('% дп.' )
	,('% округ' )
	,('% дачи' )
	,('% мкр' ) 
	,('% мкр.' ) 
	,('% село' )
	,('% с.' )
	,('% с' )
	,('% с/с' )
	,('% с\с' )
	,('% ч.' )
	,('% ч' )
	,('% совхоз' )
	,('% свх' )
	,('% свх.' )
	,('% с-з' )
	,('% с/з' )
	,('% с\з' )
	,('% с/п' )
	,('% с/о' )
	,('% село' )
	,('% с.' )
	,('% с' )
	,('% сл' )
	,('% сл.' )
	,('% деревня' )
	,('% дер' )
	,('% дер.' )
	,('% д.' )
	,('% д' )
	,('% хутор' )
	,('% хут' )
	,('% хут.' )
	,('% х.' )
	,('% х' )
	,('% пгт' )
	,('% пгт.' )
	,('% птг' )
	,('% птг.' )
	,('% станица' ) 
	,('% ст-ца' ) 
	,('% ст' ) 
	,('% ст.' ) 
	,('% с/т' ) 
	,('% с\т' ) 
	,('% станция' )
	,('% п/ст' )
	,('% аул' )
	,('% а' )
	,('% а.' )
	,('% снт' )
	,('% разъезд' )
	,('% Учхоз' )
	,('% ДНТ' )
	,('% ДН/Т' )
	,('% пансионат' )
	,('% тер' )
	,('% тер.' )
	,('% городок' )
	,('% ЗАТО' )
	,('% рзд' )
	,('поселок %' )
	,('пос %' )
	,('пос.%'  )
	,('п %' )
	,('п.%' )
	,('п-к. %' )
	,('п-к.%' )
	,('рп %' )
	,('р-п %' )
	,('рп.%' )
	,('раб.п.%' )
	,('раб-п%' )
	,('кп %' )
	,('кп.%' )
	,('массив %' )
	,('нп %' )
	,('нп.%' )
	,('дп %' )
	,('дп.%' )
	,('разъезд %' )
	,('село %' )
	,('с.%' )
	,('с %' )
	,('с/с %' )
	,('с\с %' )
	,('сл %' )
	,('сл. %' )
	,('деревня%' )
	,('д. [А-Я][А-Я][А-Я][А-Я]%' )
	,('д [А-Я][А-Я][А-Я][А-Я]%' )
	,('дер %' )
	,('дер.%' )
	,('хутор %' )
	,('хут %' )
	,('хут.%' )
	,('х.%' )
	,('х %' )
	,('пгт %' )
	,('пгт.%' )
	,('птг %' )
	,('птг.%' )
	,('станица%' ) 
	,('ст-ца%' ) 
	,('ст %' ) 
	,('ст.%' )
	,('с/т %' ) 
	,('с\т %' )
	,('ж/д%' ) 
	,('совхоз %' )
	,('с-з %' )
	,('с/з %' )
	,('с\з %' )
	,('свх %' )
	,('свх.%' )
	,('станция %' )
	,('п/ст %' )
	,('с/п %' )
	,('с/о %' )
	,('аул %' )
	,('ч.%' )
	,('ч %' )
	,('снт. %' )
	,('снт %' )
	,('дп. %'  )
	,('дп %'  )
	,('округ %' )
	,('Учхоз %' )
	,('дачи %' )
	,('ДНТ %' )
	,('ДН/Т %' )
	,('пансионат %' )
	,('тер %' )
	,('тер.%' )
	,('городок %' )
	,('ЗАТО %' )
	,('рзд %' )) t(pattern)
Where @value like t.pattern
)
Union all
Select 0
) t


Но призадумался, а будет ли от этого польза и удовольствие?
Т.е. будет ли оно меньше грузить диск, процессор, и вообще- работать быстрее?

Можно ли, не глядя на план :-), сказать, что, например, второе - бест практиз, а первое - нет. Ну и наоборот.
Ну или, может есть совсем правильное решение?
14 авг 19, 18:23    [21949544]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение со списком значений. Как будет правильно?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31357
uaggster
Я хотел уже переписать их так (по прежнему в скалярку, чтобы не ломать логику запросов, которые на них базируются)
Но призадумался, а будет ли от этого польза и удовольствие?
Т.е. будет ли оно меньше грузить диск, процессор, и вообще- работать быстрее?
То есть переписать тело скалярки? ИМХО без разницы.

PS Второй вариант выглядит подозрительно, с чего это серверу не выбрать из 2х записей "0" в запросе SELECT TOP 1 ? сортировки то нет, захочет, и выберет.

Если бы оптимизатором был я, я бы вообще не выполнял часть с "from (values"; зачем, если возвращать всегда константу "0" будет по логике запроса безупречно правильно?
14 авг 19, 22:13    [21949660]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение со списком значений. Как будет правильно?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31357
uaggster
Ну или, может есть совсем правильное решение?
А вообще, лучше такое хранить в словаре, а не в коде.
14 авг 19, 22:16    [21949661]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение со списком значений. Как будет правильно?  [new]
uaggster
Member

Откуда:
Сообщений: 827
alexeyvg, Эээ... Union All выполняется последовательно, как except или intersect.
Т.е. к первому резалтсету всегда приклеивается второй и т.д.
Вот если б там union был, то точно б вернулся хаотически, т.к. он выполняется параллельно.
Вроде так.
Хотя, наверное, для полной уверенности можно добавить order by 1 desc.

А по поводу "в словаре" - это не моя компетенция. Почему разработчики не вынесли это в табличку - я не знаю.
Хотя по pattern всё равно ведь индекс не построишь. Построишь, но он всё равно сканироваться будет, не так ли?
14 авг 19, 22:32    [21949668]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение со списком значений. Как будет правильно?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31357
uaggster
Хотя, наверное, для полной уверенности можно добавить order by 1 desc.
Ага, это гарантирует, а "к первому резалтсету всегда приклеивается второй и т.д." - только предположения.

uaggster
Хотя по pattern всё равно ведь индекс не построишь. Построишь, но он всё равно сканироваться будет, не так ли?
Я тут имел в виду администрирование всем этим хозяйством. Лучше словари хранить в справочниках, а не в коде, удобнее.
14 авг 19, 23:10    [21949682]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение со списком значений. Как будет правильно?  [new]
uaggster
Member

Откуда:
Сообщений: 827
Да, alexeyvg, вы абсолютно правы.
Это не документированно, и лишь особенность оптимизатора:
https://sqlperformance.com/2017/05/sql-plan/union-all-optimization
Впрочем, в жизни б я все равно написал order by, просто потому, что испытывал бы чувство тревоги от потенциальной неоднозначности.
:)

Но это частности.
Почему с with то нельзя писать?
Мне заявляют - "я художник, я так вижу".
Как парировать то?
14 авг 19, 23:42    [21949693]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить