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

Откуда: ни: возьмись.
Сообщений: 1601
В триггере есть такой код:
INSERT INTO CodeCache (EntityID, CodePart)
	SELECT ID, LEFT(Code, 7) FROM Inserted WHERE LEN(Code)=13 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 8) FROM Inserted WHERE LEN(Code)=13 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 9) FROM Inserted WHERE LEN(Code)=13 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 10) FROM Inserted WHERE LEN(Code)=13 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 11) FROM Inserted WHERE LEN(Code)=13 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 12) FROM Inserted WHERE LEN(Code)=13 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 6) FROM Inserted WHERE LEN(Code)=12 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 7) FROM Inserted WHERE LEN(Code)=12 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 8) FROM Inserted WHERE LEN(Code)=12 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 9) FROM Inserted WHERE LEN(Code)=12 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 10) FROM Inserted WHERE LEN(Code)=12 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 11) FROM Inserted WHERE LEN(Code)=12 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 4) FROM Inserted WHERE LEN(Code)=8 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 5) FROM Inserted WHERE LEN(Code)=8 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 6) FROM Inserted WHERE LEN(Code)=8 AND dbo.ValidCode(Code)=1
	UNION ALL
	SELECT ID, LEFT(Code, 7) FROM Inserted WHERE LEN(Code)=8 AND dbo.ValidCode(Code)=1
Меня не устраивает, что для каждого случая заново проверяем код на валидность и для каждой длины заново делаем SELECT.
В идеале, по мере выборки строчек одним-единственным SELECTом для каждой строчки вставлять в таблицу сразу набор вариантов.
9 июл 11, 22:45    [10951258]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
qwerty112
Guest
BPK
Меня не устраивает, что для каждого случая заново проверяем код на валидность и для каждой длины заново делаем SELECT.
В идеале, по мере выборки строчек одним-единственным SELECTом для каждой строчки вставлять в таблицу сразу набор вариантов.

функцию dbo.ValidCode - покажите ?
9 июл 11, 22:52    [10951266]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
BPK
Member

Откуда: ни: возьмись.
Сообщений: 1601
qwerty112
функцию dbo.ValidCode - покажите ?

Функция использует для работы только подставленную в неё строчку, не задействуя никакие другие объекты БД.
9 июл 11, 22:55    [10951269]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
qwerty112
Guest
BPK
qwerty112
функцию dbo.ValidCode - покажите ?

Функция использует для работы только подставленную в неё строчку, не задействуя никакие другие объекты БД.

с этого факта нужно возрадоватся или печалится ??

в чём заключается проверка валидности ?
почему её нельзя сделать НЕ "отрезая" по символу от Code ?

зы
вы в курсе, что возвращает LEFT(Code, 7), LEFT(Code, 8), ... при LEN(Code)=13, например ?
9 июл 11, 23:06    [10951290]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
with cte
as
(
select  id
      , case len(Code) when 8 then left(Code, 4) else null end as val4 
      , case len(Code) when 8 then left(Code, 5) else null end as val5
      , case when len(Code) in (8, 12) then left(Code, 6) else null end as val6
      , case when len(Code) in (8, 12, 13) then left(Code, 7) else null end as val7 
      , case when len(Code) in (12, 13) then left(Code, 8) else null end as val8
      , case when len(Code) in (12, 13) then left(Code, 9) else null end as val9  
      , case when len(Code) in (12, 13) then left(Code, 10) else null end as val10    
      , case when len(Code) in (12, 13) then left(Code, 11) else null end as val11
      , case when len(Code) = 13 then left(Code, 12) else null end as val12     
  from inserted 
  where dbo.ValidCode(Code)=1 and len(Code) in ( 8, 12, 13 )
) 
insert into CodeCache (EntityID, CodePart)
select id, val4 from cte where val4 is not null
union all
select id, val5 from cte where val5 is not null
union all
select id, val6 from cte where val6 is not null
union all
select id, val7 from cte where val7 is not null
union all
select id, val8 from cte where val8 is not null
union all
select id, val9 from cte where val9 is not null
union all
select id, val10 from cte where val10 is not null
union all
select id, val11 from cte where val11 is not null
union all
select id, val12 from cte where val12 is not null
9 июл 11, 23:07    [10951293]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
BPK
Member

Откуда: ни: возьмись.
Сообщений: 1601
qwerty112
с этого факта нужно возрадоватся или печалится ??

Это Вы сами решайте.

qwerty112
в чём заключается проверка валидности ?

В проверке контрольной суммы.

qwerty112
почему её нельзя сделать НЕ "отрезая" по символу от Code ?

Она и делается без отрезания по символу от Code.
Я хочу:
1. Выбрать все валидные записи
2. Для каждой ОДНОЙ валидной записи вставить в кэш-таблицу НЕСКОЛЬКО строчек, отличающихся степенью обрезанности кода.

А по обрезанному коду - индекс.

qwerty112
вы в курсе, что возвращает LEFT(Code, 7), LEFT(Code, 8), ... при LEN(Code)=13, например ?

Да

Knyazev Alexey
Спасибо. Все разглагольствуют, а Вы просто помогли. Сейчас буду разбираться.
9 июл 11, 23:13    [10951316]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
BPK
Member

Откуда: ни: возьмись.
Сообщений: 1601
Knyazev Alexey
with cte

Красивое решение, только я беспокоюсь о разрастании CTE в памяти за счёт NULLов.
9 июл 11, 23:19    [10951333]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
qwerty112
Guest
BPK
Она и делается без отрезания по символу от Code.

даа, тут я неправ :)
declare @t table (code varchar(13))

insert into @t
select 'a123456789012' union all
select 'b12345678901' union all
select 'c1234567890' union all
select 'd123456789' union all
select 'e12345678' union all
select 'f1234567' union all
select 'g123456'

------------------

select left(code, dig)
from @t,
(select 4 as dig union all select 5 union all select 6 union all select 7) a
where len(code)=8 and dbo.ValidCode(Code)=1

union all

select left(code, dig)
from @t,
(select 6 as dig union all select 7 union all select 8 union all select 9 union all select 10 union all select 11) a
where len(code)=12 and dbo.ValidCode(Code)=1

union all

select left(code, dig)
from @t,
(select 7 as dig union all select 8 union all select 9 union all select 10 union all select 11 union all select 11) a
where len(code)=13 and dbo.ValidCode(Code)=1
9 июл 11, 23:33    [10951391]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
BPK
Member

Откуда: ни: возьмись.
Сообщений: 1601
qwerty112
select 7 as dig union all ...

Попробовал так, и ... удалил нафиг и триггеры, и кэш-таблицу, поскольку время выполнения основного запроса, делающего частичное сравнение строк, снизилось с 8 секунд до 2, а это уже приемлемо.
10 июл 11, 02:45    [10951877]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
INSERT INTO CodeCache (EntityID, CodePart)
SELECT ID, LEFT(Code, [left]) 
FROM (select * Inserted WHERE dbo.ValidCode(Code)=1) I
inner join
(select 7 as [left], 13 as [len]
 union all
 select 8 as [left], 13 as [len]
 union all
 ...
 ) X
 on LEN(I.Code)=X.[len]

10 июл 11, 08:38    [10951984]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
BPK
Member

Откуда: ни: возьмись.
Сообщений: 1601
aleks2
(select 7 as [left], 13 as [len]
 union all
 select 8 as [left], 13 as [len]
 union all
 ...

Можно даже заготовить такую постоянную таблицу в БД, но это не совсем красивый жест.
10 июл 11, 23:22    [10953187]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
BPK
aleks2
(select 7 as [left], 13 as [len]
 union all
 select 8 as [left], 13 as [len]
 union all
 ...

Можно даже заготовить такую постоянную таблицу в БД, но это не совсем красивый жест.


Не нада бояться константных таблиц.
11 июл 11, 06:43    [10953604]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
BPK
Member

Откуда: ни: возьмись.
Сообщений: 1601
aleks2
Не нада бояться константных таблиц.


Испытания показали, что оптимальный вариант такой:
(select 7 as [left], 13 as [len]
 union all
 select 8 as [left], 13 as [len]
 union all
 ...
или табличная переменная.

Применение констант-таблиц, что с индексами, что без, замедляет запрос.

Самый медленный вариант - констант-таблица с любым индексом.
11 июл 11, 14:05    [10955620]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить