Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Эффективный ORDER BY по вычисляемым выражениям  [new]
WGA
Member

Откуда:
Сообщений: 401
Всем привет!

Дано: таблица очереди заявок
create table APPLICATION_IDX
(
	ID INT not null
		constraint PK_APPLICATION_IDX
			primary key
		constraint FK_APPLICATION_IDX
			references APPLICATION
				on delete cascade,
	CREATE_DATE DATETIME not null,
	SUPERVISOR_PRIORITY INT,
	RTDM_PRIORITY INT,
	SKILL_GROUP INT,
	REGION_CLIENT_PRIORITY INT,
	REGION_APP_PRIORITY INT,
	AMOUNT_PRIORITY INT,
	CHANNEL_PRIORITY INT,
	ATTRACT_CHANNEL_PRIORITY INT,
	PRODUCT_PRIORITY INT,
	IS_NOVEL BIT,
	IS_FRAUD_RETURN BIT,
	IS_SUSPENSIVE_TERMS BIT,
	IS_OUT_OF_DIAL_TIME BIT,
	LAST_USER_ID INT
		constraint FK_APPLICATION_IDX_USER_ID
			references APP_USER
				on delete cascade
)
Из этой выборки выполняется выборка в порядке приоретизации, что выливается в SELECT... ORDER BY. Количество и порядок ключей сортировки настраивается. Большинство параметров приоретизации вполне тривиальны: просто добавляется поле из APPLICATION_IDX. Но есть несколько кейсов, приводящих к вычисляемым выражениям, что препятствует использованию индекса.

1. Отложенная заявка с наступившим временем

Приводит к такому выражению
order by case when APPLICATION_IDX.SUSPEND_TIME > :datetime then 1 else 0 end

2. Последний оператор
order by case when APPLICATION_IDX.LAST_USER_ID = :user_id then 1 else 0 end

3. Время для звонка да/нет
Какая тут кляуза вообще пока не знаю. Видимо, надо добавить 2 столбца START_DIAL_TIME типа TIME и END_DIAL_TIME. При попадание в него значение ключа = 1, иначе 0.

Существует ли способ оптимизировать такие ORDER BY? ВЫборка будет или постраничная, или 1 запись, идущая первой в очереди.
11 окт 18, 07:34    [21701149]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
WGA
1. Отложенная заявка с наступившим временем

Приводит к такому выражению
order by case when APPLICATION_IDX.SUSPEND_TIME > :datetime then 1 else 0 end


2. Последний оператор
order by case when APPLICATION_IDX.LAST_USER_ID = :user_id then 1 else 0 end


3. Время для звонка да/нет
Какая тут кляуза вообще пока не знаю. Видимо, надо добавить 2 столбца START_DIAL_TIME типа TIME и END_DIAL_TIME. При попадание в него значение ключа = 1, иначе 0.

Существует ли способ оптимизировать такие ORDER BY? ВЫборка будет или постраничная, или 1 запись, идущая первой в очереди.
Первую сортировку можно заменить на order by APPLICATION_IDX.SUSPEND_TIME
А вот с второй и третьей непонятно, видимо, никак не оптимизировать

В общем случае, вычисляемые сортировки либо нужно оптимизировать, как в (1), либо предварительно рассчитывать (менять схему данных), либо вообще никак, если выражения недетерминированы (случай 2)
11 окт 18, 09:14    [21701184]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
WGA
Количество и порядок ключей сортировки настраивается.
При таком подходе, для оптимизации сортировки, т.е. ее физического исключения из плана выполнения, на каждый вариант порядка нужен свой индекс с секцией include, содержащей все остальные столбцы, используемые в запросе.
У вас сделано именно так?

При наличии сортировки по вычисляемым столбцам, зависимым от внешних данных, избавиться от физической сортировки невозможно.

Или для вас "оптимизация сортировки" нечто другое?
11 окт 18, 10:42    [21701257]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
aleks222
Member

Откуда:
Сообщений: 985
alexeyvg
либо вообще никак, если выражения недетерминированы (случай 2)

Да ладно те
union all спасет

where APPLICATION_IDX.LAST_USER_ID = :user_id 
union all
where APPLICATION_IDX.LAST_USER_ID <> :user_id 
11 окт 18, 16:56    [21701807]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
aleks222
alexeyvg
либо вообще никак, если выражения недетерминированы (случай 2)

Да ладно те
union all спасет

where APPLICATION_IDX.LAST_USER_ID = :user_id 
union all
where APPLICATION_IDX.LAST_USER_ID <> :user_id 
У меня мелькнула мысль, но это же на практике неприменимо.
Во первых, если таких "особых" сортировок больше одной, то union all получится слишком много, для всех комбинаций.
Во вторых, автор использует сортировку для постраничного вывода, и получится в итоге 2 больших наборов данных, которые нужно потом слить и отсортировать, на этот раз вообще без индексов.
11 окт 18, 17:36    [21701861]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
aleks222
union all спасет

where APPLICATION_IDX.LAST_USER_ID = :user_id 
union all
where APPLICATION_IDX.LAST_USER_ID <> :user_id 
Каким образом, если выражение не первое в списке?
А если первое, то чем гарантирован порядок строк в результирующем наборе?
11 окт 18, 18:52    [21701912]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Я думаю, что это задача прикладного уровня и должна решаться приложением.
12 окт 18, 11:24    [21702301]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Владислав Колосов
Я думаю, что это задача прикладного уровня и должна решаться приложением.
Постраничная выборка или выборка одной строки согласно разнообразным критериям сортировки?

ЗЫ: Некоторое время назад тут был один кадр, утверждавший, что джойнить таблицы в приложении гораздо выгоднее.
Думается мне, вы, в своей страсти низвести сервер СУБД до простого хранилища таблиц, скоро достигнете его уровня...
12 окт 18, 11:43    [21702323]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
Владислав Колосов
Member

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

не совсем так, я моем представлении здесь требуется динамический тоссинг заявок, а это выгоднее обрабатывать в памяти массивами данных. Т.е. в заявках приоритеты непрерывно изменяются, причем для каждого оператора индивидуально. Логично представить работу с индивидуально загруженными массивами данных и перерасчету приоритетов по таймеру, например. Т.е. робот рассчитывает приоритеты, интерфейс отображает и т.д. Задача комплексная, нельзя ее пытаться решить чисто средствами SQL.
12 окт 18, 12:13    [21702357]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
invm
aleks222
union all спасет

where APPLICATION_IDX.LAST_USER_ID = :user_id 
union all
where APPLICATION_IDX.LAST_USER_ID <> :user_id 

Каким образом, если выражение не первое в списке?
А если первое, то чем гарантирован порядок строк в результирующем наборе?
Идея видимо такая, что двумя запросами выбираются нужные записи, и в соотв запросах ставится поле типа is_current_user 0 или 1, а потом делается сортировка по нему.

Для компактного результата действительно хороший вариант, позволяет использовать индекс по LAST_USER_ID, но для постраничной выборки не сработает.

ЗЫ Можно конечно сказать, что для компактного результата это не нужно, потому что вычисляемое выражение будет вычисляться по небольшому количеству записей.

Но тут есть тонкость - для реализации очередей такое не сработает.

Допустим, мы должны выбирать для обработки каждый раз по 10 записей, из огромной таблицы
Тогда будет сортировка без индекса по большому объёму

А вот если сделать union all 2х запросов по 10 записей, и потом взять 10 из полученного набора (из максимум 20 записей), то получится быстрый запрос, использующий индексы.
12 окт 18, 12:33    [21702374]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Владислав Колосов
здесь требуется динамический тоссинг заявок, а это выгоднее обрабатывать в памяти массивами данных.
Это выгоднее обрабатывать средствами, предназначенными для манипулирования множествами, т.е. на стороне СУБД. Приложение должно получать готовый результат, а не лопатить сырые данные.
12 окт 18, 12:40    [21702385]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
alexeyvg
Идея видимо такая, что двумя запросами выбираются нужные записи, и в соотв запросах ставится поле типа is_current_user 0 или 1, а потом делается сортировка по нему.
Результат объединения все равно придется сортировать по всем критериям, а не только по этому признаку.
12 окт 18, 12:49    [21702399]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
invm
alexeyvg
Идея видимо такая, что двумя запросами выбираются нужные записи, и в соотв запросах ставится поле типа is_current_user 0 или 1, а потом делается сортировка по нему.
Результат объединения все равно придется сортировать по всем критериям, а не только по этому признаку.
Да, по всем критериям, и что это меняет в моих рассуждениях?
"А потом делается сортировка по нему" - имеется в виду, что при сортировке по всем критериям потом будет использоваться is_current_user вместо case.

То есть вместо
select * from ...
order by другие критерии, case when APPLICATION_IDX.LAST_USER_ID = :user_id then 1 else 0 end
будет
select *
from (
select *, 1 as is_current_user  from ...
where APPLICATION_IDX.LAST_USER_ID = :user_id 
union all
select *, 0 as is_current_user  from ...
where APPLICATION_IDX.LAST_USER_ID <> :user_id 
) t
order by другие критерии, is_current_user 


А для работы с очередью
select top 10 *
from (
select top 10 *, 1 as is_current_user  from ...
where APPLICATION_IDX.LAST_USER_ID = :user_id 
order by другие критерии
union all
select top 10 *, 0 as is_current_user  from ...
where APPLICATION_IDX.LAST_USER_ID <> :user_id 
order by другие критерии
) t
order by другие критерии, is_current_user 
12 окт 18, 13:39    [21702468]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
invm
Владислав Колосов
здесь требуется динамический тоссинг заявок, а это выгоднее обрабатывать в памяти массивами данных.
Это выгоднее обрабатывать средствами, предназначенными для манипулирования множествами, т.е. на стороне СУБД. Приложение должно получать готовый результат, а не лопатить сырые данные.


Не факт, если речь идет о персонифицированных наборах данных. Предварительные выборки да, выгоднее производить на сервере, но сортировки, фильтры и разметку в условиях, приближенных к реал-тайм - локально, вместо того, чтобы спамить крупные запросы и обновления на сервере от множественных пользователей каждые N секунд. С сервера же выгодно забирать небольшие порции свежих данных. Но, я вижу, у Вас стойкая позиция относительно архитектуры приложения подобного рода :)
12 окт 18, 14:04    [21702519]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
alexeyvg
То есть вместо
select * from ...
order by другие критерии, case when APPLICATION_IDX.LAST_USER_ID = :user_id then 1 else 0 end

будет
select *
from (
select *, 1 as is_current_user  from ...
where APPLICATION_IDX.LAST_USER_ID = :user_id 
union all
select *, 0 as is_current_user  from ...
where APPLICATION_IDX.LAST_USER_ID <> :user_id 
) t
order by другие критерии, is_current_user
+ Сравним
use tempdb;
go

create table dbo.t (id int not null, v int);

insert into dbo.t
select top(2000000)
 rand(checksum(newid())) * 1000, rand(checksum(newid())) * 10000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create clustered index CIX_t__id on dbo.t (id);
go

declare @v int, @id int = (select top (1) id from dbo.t);

--set statistics xml on;
set statistics time on;

select
 @v = v
from
 dbo.t
order by
 v, case when id = @id then 1 else 0 end
option
 (maxdop 1);

select
 @v = v
from
 (
  select v, 1 from dbo.t where id = @id
  union all
  select v, 0 from dbo.t where id <> @id
 ) a(v, is_current)
order by
 v, is_current
option
 (maxdop 1);

--set statistics xml off;
set statistics time off;
go

drop table dbo.t;
go

 SQL Server Execution Times:
CPU time = 2203 ms, elapsed time = 2248 ms.

SQL Server Execution Times:
CPU time = 2250 ms, elapsed time = 2283 ms.
И это "тепличный" пример.
Далеко не факт, что в реальной задаче ТС будет возможно задействовать индекс по LAST_USER_ID.
12 окт 18, 14:11    [21702533]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Владислав Колосов
Но, я вижу, у Вас стойкая позиция относительно архитектуры приложения подобного рода :)
У вас не менее стойкая :)
12 окт 18, 14:12    [21702535]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
.Евгений
Member

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

Мне кажется, что вы пытаетесь решить не совсем ту задачу, которую вам нужно решить; что вам нужна не единая абсолютная реал-тайм приоретизация, а взаимозависимые очереди звонков для операторов.

К вашему решению будут приходить запросы типа "выдай наиболее приоритетный звонок для указанного оператора (или группы, в т.ч. Все операторы) на текущее время, если такового нет - передай чей-то наименее приоритетный" и "измени приоритет звонка согласно изменению заявки". А это уже прямо описывает структуру данных таблицы Очередь:
Оператор (группа), Временной диапазон (подробности опускаю), Приоритет, Заявка. Индекс по первым трем полям (1-2-3 или 1-3-2), индекс по заявке. Можно секционировать по оператору.

При направлении заявки на обзвон в очереди создается 1...n записей.
При взятии оператором в обработку n-1 записей удаляются.
При модификации заявки (в т.ч. после звонка оператора) все записи по заявке удаляются и (при необходимости дальнейшего обзвона) создаются заново.

Вставка заявки в середину очереди приоритета может осуществляться апдейтом приоритета конца очереди, пересозданием очереди или небольшими ухищрениями.

Повторюсь: это то, что мне показалось.
12 окт 18, 15:38    [21702667]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
invm
Сравним
...
И это "тепличный" пример.
Далеко не факт, что в реальной задаче ТС будет возможно задействовать индекс по LAST_USER_ID.
Я же написал, почему этот вариант не сработает, в ответе aleks222.
Вы, по моему, прочитали одну фразу, и на неё отвечаете, а там было про несколько вариантов :-)

Единственное практическое применение описанного aleks222 подхода использования индекса получается, в итоге, только работа с очередью.
И вот тут будет фантастический выигрыш:
+ для очереди
use tempdb;
go

create table dbo.t (id int not null, v int);

insert into dbo.t
select top(2000000)
 rand(checksum(newid())) * 1000, rand(checksum(newid())) * 10000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create clustered index CIX_t__id on dbo.t (id);
go

declare @v int, @id int = (select top (1) id from dbo.t);

--set statistics xml on;
set statistics time on;

select top 1
 @v = v
from
 dbo.t
order by
 v, case when id = @id then 1 else 0 end
option
 (maxdop 1);

select top 1
 @v = v
from
 (
  select top 1 v, 1 from dbo.t where id = @id
  union all
  select top 1 v, 0 from dbo.t where id <> @id
 ) a(v, is_current)
order by
 v, is_current
option
 (maxdop 1);

--set statistics xml off;
set statistics time off;
go

drop table dbo.t;
go

SQL Server Execution Times:
CPU time = 984 ms, elapsed time = 994 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
12 окт 18, 23:41    [21703089]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Владислав Колосов
Не факт, если речь идет о персонифицированных наборах данных. Предварительные выборки да, выгоднее производить на сервере, но сортировки, фильтры и разметку в условиях, приближенных к реал-тайм - локально, вместо того, чтобы спамить крупные запросы и обновления на сервере от множественных пользователей каждые N секунд. С сервера же выгодно забирать небольшие порции свежих данных. Но, я вижу, у Вас стойкая позиция относительно архитектуры приложения подобного рода :)
Я тоже не понимаю вашего неприятия обработки данных на платформе обработки данных.
Почему приложение "СУБД" нельзя заспамить крупными запросами и обновлениями от множественных пользователей каждые N секунд, а собственноручно написанный сервер можно, откуда уверенность, что вы напишете обработку запросов лучше, чем команда разработчиков сервера?

Я понимаю, что то, отличное от стандартной обработки данных, типа расчётов, всяких биткгойнов и шифрований, для этого РСУБД действительно не затачивалась, но писать свой сервер для стандартной обработки данных, типа реляционных операций или работы с очередями? От многих слышал, что так правильно, но не могу этого понять.
12 окт 18, 23:49    [21703092]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
alexeyvg
И вот тут будет фантастический выигрыш
Да, но с не верным результатом. Вы отсортировать забыли
  select top 1 v, 1 from dbo.t where id = @id order by v
  union all
  select top 1 v, 0 from dbo.t where id <> @id order by v
13 окт 18, 10:52    [21703199]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
invm
alexeyvg
И вот тут будет фантастический выигрыш
Да, но с не верным результатом. Вы отсортировать забыли
  select top 1 v, 1 from dbo.t where id = @id order by v
  union all
  select top 1 v, 0 from dbo.t where id <> @id order by v
Ой, действительно, забыл
С сортировкой тот же результат (конечно, добавил индекс на v)
С чего бы результату измениться, ведь в первом запросе сервер должен отсортировать все записи, а во втором - взять топ по индексу?
+ сортировка
use tempdb;
go

create table dbo.t (id int not null, v int);

insert into dbo.t
select top(2000000)
 rand(checksum(newid())) * 1000, rand(checksum(newid())) * 10000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create clustered index CIX_t__id on dbo.t (id);
create index CIX_t__v on dbo.t (v);
go

declare @v int, @id int = (select top (1) id from dbo.t);

--set statistics xml on;
set statistics time on;

select top 1
 @v = v
from
 dbo.t
order by
 v, case when id = @id then 1 else 0 end
option
 (maxdop 1);

select top 1
 @v = v
from
 (
  select top 1 v, 1 from dbo.t where id = @id order by v
  union all
  select top 1 v, 0 from dbo.t where id <> @id order by v
 ) a(v, is_current)
order by
 v, is_current
option
 (maxdop 1);

--set statistics xml off;
set statistics time off;
go

drop table dbo.t;
go

 SQL Server Execution Times:
   CPU time = 953 ms,  elapsed time = 980 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
13 окт 18, 19:28    [21703373]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
alexeyvg
конечно, добавил индекс на v
Ну так вариант с индексом очевиден, но, видимо, далек от реальности ТС.
13 окт 18, 19:52    [21703383]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
invm
alexeyvg
конечно, добавил индекс на v
Ну так вариант с индексом очевиден, но, видимо, далек от реальности ТС.
Почему далёк?

У ТС вопрос, как обеспечить быструю сортировку по выражению, для страничного вывода и организации очередей. Без всяких условий, типа "индексов не создавать".
Можно создавать индексы, переписывать выражение, что угодно. Вплоть до полного изменения модели данных.

Вот, для его первого выражение решение очевидно, просто, видно, у ТС глаз замылился - нужно просто сортировать не по выражению, а по по дате, результат тот же. Понятно, что быстро будет, если опять же сделать индекс по этой дате.

А второе выражение так просто не заменишь, вот, можно на union all, с созданием индекса, но это поможет только для очередей, постраничная выборка будет медленной, какие бы индексы не делать.

Но для рочереди это работает, притом что никакие индексы не помогут для первоначального запроса с сортировкой по case
13 окт 18, 21:00    [21703405]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
alexeyvg
Почему далёк?
Я исхожу из "Количество и порядок ключей сортировки настраивается".
И не известно где будет среди этого перечня вычисляемый столбец. И сколько этих вычисляемых столбцов.
13 окт 18, 21:40    [21703425]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный ORDER BY по вычисляемым выражениям  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
invm
alexeyvg
Почему далёк?
Я исхожу из "Количество и порядок ключей сортировки настраивается".
И не известно где будет среди этого перечня вычисляемый столбец. И сколько этих вычисляемых столбцов.
В таком случае подбирают индексы под типовые запросы, собирая статистику (логи) по ним.

Как же иначе?
Иначе любая сортировка, в такой постановке, сортировка всей выборки в памяти, тогда вообще не имеет смысла что то там менять, смотреть планы, оптимизировать, хоть для сортировки по выражениям, хоть по полям.
То есть тогда вопрос ТС вообще не имеет смысла.
14 окт 18, 16:47    [21703656]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить