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

Откуда: Москва
Сообщений: 584
Доброго времени суток, All!

Буду признателе за соображения по следующей задаче.
Есть система, в ней - 2 или более однотипных таблиц вида:
- Ref - UUID (ключ записи)
- MType - int (некий тип параметра)
- Value - float
- Time - datetime

Назовем их таблицы А и В.

Внешний клиент хочет за 1 запрос получать данные из обоих таблиц, с наложением фильтров по времени и пары - имя таблицы и типа параметра. Последнее на примере - из таблицы А должны быть параметры 10 и 12, из таблицы В - 10 и 15. Еще требование - клиент хочет это получать универсальным запросом, указывая пару "имя таблицы - код параметра", для примеров это
IN ('A-10', 'A-12', 'B-10', 'B-15')
.
Сейчас это реализовано запросами вида
Select
MType,
Value,
Time
from (
select
'A-'+cast(MType as varchar(10)) as pair
MType,
Value,
Time
from Table_A
union all
select
'B-'+cast(MType as varchar(10)) as pair
MType,
Value,
Time
from Table_И
)
where pair IN ('A-10', 'A-12', 'B-10', 'B-15')
and Time between '2016-01-01' and '2016-01-02'

Все бы ничего, но конструкция вида
'A-'+cast(MType as varchar(10)) as pair
в подзапросе и фильтр на нее выше выливается в table scan с выборкой по Time, и фильтрации в дальнейшем. Таблицы А и В большие, вариантов MType у них много, и получается в начале плана запроса неселективная выборка. Поэтому производительность запроса - так себе, и ресурсов он ест.
Среда - MS SQL 2008R2, 2014.

Как можно модифицировать запрос, улучшив производительность и оставшись в рамках странных требований?
25 апр 16, 19:08    [19104792]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
Crimean
Member

Откуда:
Сообщений: 13147
мм... ну как вариант - попробовать "partitioned view". то есть добавить по полю в таблички. с DEFAULT. и наложить на него CHECK. да! поле будет частью ПК - это требования чтобы работало. но поскольку это будет константа, то не сильно повредит. а вы ПК сделаете просто UNIQUE если он вам важен. после чего будете парсить "B-12" в 2 поля. "B" и "12". первое сработает как селектор секции. второе уже будет работать "нативно". вам немножко работы по парсингу параметров. ну или упарываться дальше материализуя и индексируя "составное" поле. что вам проще / эффективнее - вам решать
25 апр 16, 19:28    [19104873]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
iljy
Member

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

сделать проверку MType перед объединением для каждого запроса отдельно. Ну или делать 2 поля вместо одного и условие типа (p1 = 'A' and MTypein (10,12) or p1 = 'B' and MType in (10,15)), но тут как повезет, оптимизатор может понять, а может и нет.
25 апр 16, 19:33    [19104888]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 584
iljy
Ferdipux,

сделать проверку MType перед объединением для каждого запроса отдельно. Ну или делать 2 поля вместо одного и условие типа (p1 = 'A' and MTypein (10,12) or p1 = 'B' and MType in (10,15)), но тут как повезет, оптимизатор может понять, а может и нет.

Гм, заходил с этой стороны. Есть проблема в виде внешнего клиента - который хочет задавать именно критерии в виде пар '<Table Alias> - MType', не задумываясь об их группировке, поэтому ваше предложение не пройдет. Клиенту можно объяснить, что пары задавать не в виде 'A-10', а в другом виде, но пары должны быть указаны в разделе Where запроса.

Если бы набор из IN ('A-10', 'A-12', 'B-10', 'В-15') преобразовать в табличную переменную или таблицу -- тогда ее можно использовать на самом нижнем уровне. Но у MType нет справочника, а значения больше чем 2047 (что не позволяет использовать spt_values).
25 апр 16, 20:42    [19105111]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
iljy
Member

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

тогда сделайте в таблицах вычисляемое поле и делайте индекс по нему.
25 апр 16, 20:45    [19105123]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Ferdipux
Но у MType нет справочника, а значения больше чем 2047 (что не позволяет использовать spt_values)
А зачем нужен справочник и spt_values для парсинга списка значений в таблицу?
25 апр 16, 20:57    [19105176]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 584
invm
А зачем нужен справочник и spt_values для парсинга списка значений в таблицу?

У меня была мысль, что при наличии справочника (и spt_values как его суррогата) - можно было бы преобразовать список значений в таблицу в CTE как
Select MType from MType_list where 'A-'+MType in ( список значений )

А как бы вы решили эту задачу?
25 апр 16, 22:28    [19105488]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
iljy
Member

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

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

ALTER function [dbo].[ParseStr] (@s varchar(max), @delim varchar(100))
returns table
as
return (
	with cte as(
		select cast(1 as int) N, cast(1 as int) p1, isnull(nullif(CHARINDEX(@delim, @s),0),LEN(@s) + 1) p2
			union all
		select cast(N + 1 as int), cast(p2 + datalength(@delim) as int),
			   isnull(nullif(CHARINDEX(@delim, @s, p2 + datalength(@delim)),0),LEN(@s) + 1)
		from cte
		where p2 < LEN(@s)
	)
	select N, SUBSTRING(@s, p1, p2-p1) val 
	from cte
)


разобрали строку в таблицу, а дальше фильтруйте по имени таблицы, отделяйте вторую часть и джойньте на здоровье.
25 апр 16, 22:45    [19105554]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
o-o
Guest
только почему в заголовке темы tuples?
tuple вообще-то кортеж, т.е. грубо говоря, строка таблицы
25 апр 16, 23:38    [19105647]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 584
iljy,

Спасибо за предложение посмотреть под другим углом!
В итоге, задача сводится к следующей - в запрос передается набор кортежей критериев в конструкции IN (...). Как из такого критерия сделать таблицу?
У меня, кроме составления таблицы возможных критериев и выборки из нее с применением полученного условия IN (...), ничего не придумывается. Буду признателен за иные идеи.
26 апр 16, 08:09    [19106055]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
Glory
Member

Откуда:
Сообщений: 104751
Ferdipux
в запрос передается набор кортежей критериев в конструкции IN (...).

В смысле "передается набор кортежей" ? с конструкцией IN можно только сразу сфомировать текст запроса на клиенте. Вы хотите парсить полный текст запроса ?
26 апр 16, 08:25    [19106073]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
iljy
Member

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

я так и не понял, что у вас в качестве входных данных, исхожу из того, что это строка вида 'A-10', 'A-12', 'B-10', 'B-15'. Вот ее я вам и предлагаю разобрать в таблицу из 4 строк вида ('A',10),('A',12)... А потом делайте с это таблицей JOIN в запросе.
26 апр 16, 08:39    [19106099]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 584
iljy
Ferdipux,
я так и не понял, что у вас в качестве входных данных, исхожу из того, что это строка вида 'A-10', 'A-12', 'B-10', 'B-15'.

Спасибо за идеи! Ситуация немного не такая.
Есть приложение, которое запрашивает данные с фильтрами по парам "тип-значение" - это пресловутые пары 'A-10', 'A-12', 'B-10', 'B-15' и интервалу времени. Приложение делает фильтры вида IN ('A-10', 'A-12', 'B-10', 'B-15') и интервала времени, и подставляет в шаблон запроса вида

Select ... where ... and MTType in #in_cond# and Time between #DT1# and #DT2#

В шаблон подставляются значения критериев в фрагменты в #...#, в #in_cond# подставляется перечень пар критериев. Шаблон запроса вынесен в настройку приложения, и его можно менять с определенными ограничениями на состав столбцов и пр. Логику формирования критериев - менять нельзя. (лучи восхищения кодерами...).
Мне остается только тюнить запрос. Одна из идей - пытаться сделать из фильтра пар вида IN ('A-10', 'A-12', 'B-10', 'B-15') табличку, тогда ее можно join-ить и пр.
26 апр 16, 15:14    [19108754]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
iljy
Member

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

а не судьба переделать приложение, чтоб оно строку формировало как

values('A-10'),('B-15)...
? Ну или ваше #in_cond# ставить так:

with cte as (
select * from
(select '#in_cond') c(s) cross apply dbo.ParseStr(SUBSTRING(s, 5, LEN(s) -5)
)
, а дальше джойнить с СТЕ в свое удовольствие.
26 апр 16, 15:21    [19108803]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с tuples  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 584
iljy,
За второе - спасибо!
Первое - не судьба, уже посылаю туда лучи любви...
26 апр 16, 15:44    [19108971]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить