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

Откуда:
Сообщений: 10
в общем написал такую функцию для определения входит ли, заданный IP адрес в интервал,сеть:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[MatchIPORSubnet](@IPAddress AS VARCHAR(18),@IPAddressCheck AS VARCHAR(18))

RETURNS bit AS BEGIN

DECLARE @returning AS bit

if (CHARINDEX('/',@IPAddress,0))<>0

begin

DECLARE @ip AS VARCHAR(15) DECLARE @size AS INT

SELECT @ip = Left(@IPAddress, CHARINDEX('/',@IPAddress,0)-1)

SELECT @size = substring(@IPAddress, CHARINDEX('/',@IPAddress,0)+1,len(@IPAddress))

SELECT @returning=

(case ((CAST(

CAST( CAST( PARSENAME( @IPAddressCheck, 4 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( @IPAddressCheck, 3 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( @IPAddressCheck, 2 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( @IPAddressCheck, 1 ) AS INTEGER) AS BINARY(1)) as bigint))

^

--Subnet

(CAST(

CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) as bigint))

&

--Mask

CAST (CAST(0xFFFFFFFF*POWER (2, 32-@size) AS BINARY(4))as bigint)

)WHEN 0 THEN 1 ELSE 0 END)

END

ELSE

BEGIN

SELECT @returning=(case WHEN @IPAddressCheck like @IPAddress THEN 1 ELSE 0 END)

end

RETURN @returning END


И использую ее в таком запросе, где vw_List - это список шаблонов, а [Session] - это таблица со списком интернет сессий. шаблоны могут быть такого вида:
10.*(* подменяется на %)
10.0.0.0/8
10.0.0.1
10.0.0.[1-9]*

declare @IDList int =881

SELECT IDList,IDSession

FROM vw_List l INNER JOIN [Session] a

ON dbo.MatchIPORSubnet(l.IPAddress,a.IPAddress)= 1

where l.IDList = @IDList
--
в итоге запрос выполняется 1,5 минуты, такой же:
--
declare @IDList int =881

SELECT IDList,IDSession

FROM vw_List l INNER JOIN [Session] a

ON l.IPAddress LIKE a.IPAddress

where l.IDList = @IDList

--
Доли секунд.
Понимаю, что из функции возвращенное значение не индексируемое, но как это победить не знаю. Занимаюсь MSSQL 4 мес. если вопрос очевиден и отвечать не хочется, ткните хотя б где про это пишут.Заранее благодарен.

Сообщение было отредактировано: 18 окт 12, 15:28
17 окт 12, 14:35    [13334186]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Crimean
Member

Откуда:
Сообщений: 13147
trialexl

SELECT IDList,IDSession

FROM vw_List l INNER JOIN [Session] a

ON dbo.MatchIPORSubnet(l.IPAddress,a.IPAddress)= 1

where l.IDList = @IDList


у вас на Session условий нет, значит фактически это нифига не INNER JOIN, а CROSS JOIN, то есть будет к результату (vw_List.IDList = @IDList) * Session применен расчет dbo.MatchIPORSubnet(l.IPAddress,a.IPAddress) а уже после - фильтр "= 1" к результатам расчета. вот и думайте, сколько "это" будет выполняться
17 окт 12, 14:42    [13334255]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Crimean,
все верно, но условие это как раз и обрабатывается в функции, по другим полям не связать. Совета так и не увидел.
17 окт 12, 14:55    [13334413]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Glory
Member

Откуда:
Сообщений: 104751
trialexl
Понимаю, что из функции возвращенное значение не индексируемое,

О каких индексах идет речь для скалярнорй функции ?

trialexl
но как это победить не знаю.

Не использовать функцию.
17 окт 12, 15:02    [13334485]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Crimean
Member

Откуда:
Сообщений: 13147
trialexl
Совета так и не увидел.


а вы перечитайте ответ. пока не сократите объем данных для обработки способом, понятным серверу (а не вам) - быстро не будет
ну или загляните в план для разнообразия - там видно сколько данных поднимается и обрабатывается
17 окт 12, 15:05    [13334516]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Glory
О каких индексах идет речь для скалярнорй функции ?

Речь не идет.
Glory
Не использовать функцию.

Не вижу как.
if там не вставить.
17 окт 12, 15:10    [13334566]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Crimean
а вы перечитайте ответ. пока не сократите объем данных для обработки способом, понятным серверу (а не вам) - быстро не будет
ну или загляните в план для разнообразия - там видно сколько данных поднимается и обрабатывается

Был там. Не воодушевило))
Решения иного не вижу просто. если хранить "10.0.0.0" в одном столбце, а размер "/8" в другой, то и функции не надо, но в дальнейшем использовании будет ужасно неудобно.
17 окт 12, 15:17    [13334616]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Glory
Member

Откуда:
Сообщений: 104751
trialexl
Не вижу как.
if там не вставить.

Разумеется. В запросаз используется CASE.
17 окт 12, 15:17    [13334619]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Glory
Member

Откуда:
Сообщений: 104751
trialexl
но в дальнейшем использовании будет ужасно неудобно.

например
17 окт 12, 15:18    [13334625]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Glory
Разумеется. В запросаз используется CASE.

пробовал через case уже, на чем споткнулся сейчас не могу сказать, завтра на работе протестирую и отвечу.
А в дальнейшем использовании это
1) обрабатывать случаи, когда значение ip=10.* size=NULL и когда ip=10.0.0.0, а size=8
2) перегрузка шаблонов+ связанных таблиц.
3) складывать при выводе ip+'/'+size для наглядности пользователю
4) ну и привык писать функции, где алгоритм встречается более 2-х раз.
17 окт 12, 15:32    [13334739]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Crimean
Member

Откуда:
Сообщений: 13147
trialexl
привык писать функции, где алгоритм встречается более 2-х раз.


T-SQL это не "алгоритмический язык", это "способ обработки данных". тут "привычные" подходы нифига не работают
хотите "быстро" - расскажите серверу как эффективно выбрать то, что вам нужно
подсказка - индексы, альтернатив нет
точнее, альтернатива есть - выгрести все данные и самому "алгоритмически" обработать
17 окт 12, 15:36    [13334774]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Crimean,
спасибо Вам за напутствия, завтра продолжу и надеюсь на такие же быстрые ответы))))
17 окт 12, 16:02    [13335013]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Glory
trialexl
Не вижу как.
if там не вставить.

Разумеется. В запросаз используется CASE.


переписал без функции:

declare @IDList int =881

SELECT IDList,IDSession

FROM vw_List l INNER JOIN [Session] a

ON a.IPAddress like
(CASE (CHARINDEX('/',@IPAddress,0)) WHEN 0 THEN l.IPAddress
ELSE

(case ((CAST(

CAST( CAST( PARSENAME( a.IPAddress, 4 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( a.IPAddress, 3 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( a.IPAddress, 2 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( a.IPAddress, 1 ) AS INTEGER) AS BINARY(1)) as bigint))

^

--Subnet

(CAST(

CAST( CAST( PARSENAME( Left( l.IPAddress, CHARINDEX('/', l.IPAddress,0)-1), 4 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( Left( l.IPAddress, CHARINDEX('/', l.IPAddress,0)-1), 3 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( Left( l.IPAddress, CHARINDEX('/', l.IPAddress,0)-1), 2 ) AS INTEGER) AS BINARY(1))

+CAST( CAST( PARSENAME( Left( l.IPAddress, CHARINDEX('/', l.IPAddress,0)-1), 1 ) AS INTEGER) AS BINARY(1)) as bigint))

&

--Mask

CAST (CAST(0xFFFFFFFF*POWER (2, 32- substring( l.IPAddress, CHARINDEX('/', l.IPAddress,0)+1,len( l.IPAddress))) AS BINARY(4))as bigint)

)WHEN 0 THEN a.IPAddress ELSE NULL END)

END)

where l.IDList = @IDList
Работает быстрее, но не во всех случаях. в случае ELSE NULL перебирает всю таблицу [Session] на наличие NULL. Можно сделать так что б в этом случае он исключал просто данную запись или переход к следующей сверке. В случае с циклом я б написал а-ля continue. Есть в mssql подобное?Знаю что подходы разные. Объясняю просто что хотелось бы получить.
18 окт 12, 14:34    [13340735]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Glory
Member

Откуда:
Сообщений: 104751
trialexl
Работает быстрее, но не во всех случаях. в случае ELSE NULL перебирает всю таблицу [Session] на наличие NULL. Можно сделать так что б в этом случае он исключал просто данную запись или переход к следующей сверке. В случае с циклом я б написал а-ля continue. Есть в mssql подобное?

Это вы так думаете или в плане выполнения увидели ?
18 окт 12, 14:37    [13340755]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Glory,
но это ж очевидно, что если условие попадает туда, то a.IPAddress like NULL срабатывает.
Пример:
1)в списке шаблонов 192.168.0.* (l.IPAddress)
срабатывает по a.IPAddress like l.IPAddress (доли секунд)

2)в списке шаблонов 192.168.0.32/24 (l.IPAddress)
срабатывает a.IPAddress like a.IPAddress и по a.IPAddress like NULL

и если изменить ELSE NULL на ELSE a.IPAddress
работает доли секунд, но уже естественно не правильно
18 окт 12, 14:51    [13340845]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Glory
Member

Откуда:
Сообщений: 104751
trialexl
но это ж очевидно, что если условие попадает туда, то a.IPAddress like NULL срабатывает.

Куда - туда ?
Мне кажется вы много спекулируете о том, что же на самом деле делает сервер
18 окт 12, 15:17    [13341078]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Glory
trialexl
но это ж очевидно, что если условие попадает туда, то a.IPAddress like NULL срабатывает.

Куда - туда ?
Мне кажется вы много спекулируете о том, что же на самом деле делает сервер

Туда это, "ELSE NULL"
в примере описанным на пост выше пункт 2 когда в 192.168.0.32/24 a.IPAddress не входит.
Если б я представлял, как именно отрабатывает сервер мои запросы, то вопросы ,кажущиеся вам глупыми не задавал.
А теперь вопрос именно к вам. Вы поняли что я хочу получить, о чем писал в посте [13340735]?
18 окт 12, 15:52    [13341458]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Glory
Member

Откуда:
Сообщений: 104751
trialexl
Если б я представлял, как именно отрабатывает сервер мои запросы, то вопросы ,кажущиеся вам глупыми не задавал.

Вам уже несколько раз сказали, что есть такая штука, как план выполнения запроса (execution plan)
18 окт 12, 16:16    [13341669]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
это ж числа
Guest
trialexl,

автор
входит ли, заданный IP адрес в интервал,сеть

это ж числа. сделай вычисляемый столбец в котором строковое представление ip приводи к числовому виду и фильтруй по >= <=.
18 окт 12, 17:23    [13342305]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
trialexl
Member

Откуда:
Сообщений: 10
Glory
trialexl
Если б я представлял, как именно отрабатывает сервер мои запросы, то вопросы ,кажущиеся вам глупыми не задавал.

Вам уже несколько раз сказали, что есть такая штука, как план выполнения запроса (execution plan)

Спасибо что еще раз напомнили о плане, только мне это ничего не дает.Я не знаю как иначе построить запрос. По моему я точно задал вопрос и хотел такой же ответ получить. Есть ли такая команда? Думаю следующий ваш пост будет в стиле "какая такая команда?". В общем благодарю за потраченное мое и ваше время. Удачи.
19 окт 12, 12:27    [13345901]     Ответить | Цитировать Сообщить модератору
 Re: джоин по возвращенному значению из функции  [new]
Glory
Member

Откуда:
Сообщений: 104751
trialexl
Спасибо что еще раз напомнили о плане, только мне это ничего не дает.

План не должен давать
План должен показывать, что делать сервер. Чтобы вы не придумывали фраз "ну это очевидно, что условие туда попадает"
План точно показывает, что и в какой момент времени куда будет "попадать"

trialexl
По моему я точно задал вопрос

По фотографии лечил только Кашпировский.
Остальные используют мониторинг того, что происходит на сервере. С последующим анализом
trialexl
Есть ли такая команда?

Команды - "работать быстро и правильно" - нет.
19 окт 12, 12:34    [13345981]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить