mssql сильно тормозит OR - Базы данных

Если в join есть OR типа
inner join tbl1 on(a=b OR c=d)
mssql ужасно тормозит, такая проблема часто возникает.

Целый час выполняется запрос
Переписали через Union
. . .
inner join tbl1 on(a=b)
UNION
. . .
inner join tbl1 on(a=b)
выполняется за несколько секунд

Почему mssql не может оптимизировать OR?
Не хочется использовать вариант с UNION так как запросы большие и может быть много OR — будет куча дублированного кода.
Иногда помогает OPTION(TABLE HINT . . . но тоже не очень удобный вариант.
Есть ли что-то получше?

Пример

create table tx1(
id int IDENTITY primary key,
x1 int,
x2 int)
go

Declare @i int=0
SELECT RAND(100)
WHILE @i <30000
BEGIN
Set @i=@i+1
Insert into tx1 values(RAND()*10000000,RAND()*10000000)
END;

CREATE NONCLUSTERED INDEX IX_tx1 ON tx1 (x1,x2);
GO

-- 45 sec
select * from tx1 t1
inner join tx1 t2 on(t1.x1=t2.x1 or t1.x2=t2.x2) and t1.id!=t2.id

-- <1 sec
select * from tx1 t1
inner join tx1 t2 on(t1.x1=t2.x1) and t1.id!=t2.id
union
select * from tx1 t1
inner join tx1 t2 on(t1.x2=t2.x2) and t1.id!=t2.id

T>Не хочется использовать вариант с UNION так как запросы большие и может быть много OR — будет куча дублированного кода.
T>Иногда помогает OPTION(TABLE HINT . . . но тоже не очень удобный вариант.
T>Есть ли что-то получше?

так это давно известная проблема

вообще, странно немного слышать про красоту SQL кода... ну будет больше строк и что?
зато всегда можно посмотреть на план выполнения запроса и понять , что имеет смысл, а что- меньше

Здравствуйте, tnikolai, Вы писали:

T>Если в join есть OR типа
T>inner join tbl1 on(a=b OR c=d)
T>mssql ужасно тормозит, такая проблема часто возникает.

T>Целый час выполняется запрос
T>Переписали через Union
T>. . .
T>inner join tbl1 on(a=b)
T>UNION
T>. . .
T>inner join tbl1 on(a=b)
T>выполняется за несколько секунд

T>Почему mssql не может оптимизировать OR?
Коротко: потому, что не хочет.
Длинно: потому, что такая оптимизация является достаточно дорогой. При этом есть очевидный workaround, который позволяет поднять производительность запроса, не переписывая движок MS SQL.
Такое сочетание стоимости разработки и потенциального выигрыша отодвигает фичу в конец списка приоритетов.

T>Не хочется использовать вариант с UNION так как запросы большие и может быть много OR — будет куча дублированного кода.
Да, к сожалению, с повторным использованием в SQL беда. Я не знаю, что у вас стоит слева от inner join. Если там одна таблица — то код и так достаточно неплох. Если у вас там какое-то сложное выражение — вы можете свернуть его во view или в table-valued function (если там есть параметры), и джойниться с ней. Это улучшит читаемость кода и снизит вероятность ошибки, когда вы будете вносить изменения в этот запрос.

Если у вас в проекте много SQL-кода, то вам нужно или нанять компетентного DBA, или отправить кого-то из команды на курсы повышения квалификации.
SQL, несмотря на всю свою декларативность, оптимизируется по разному. Это означает, что для эффективной работы недостаточно просто написать технически корректный запрос. Нужно достаточно хорошо понимать, как он будет исполняться.
Банальная замена where DateAdd(DAY, 30, orderDate) > GETDATE() на where orderDate > DateAdd(DAY, -30, GETDATE()) способна драматически изменить скорость выполнения.
Если разработчик не владеет такими нюансами, то добиться хорошей производительности приложения будет тяжело.

Все проблемы на достаточно простых случаях
например

create table tx1(
id int IDENTITY primary key,
x1 int,
x2 int)
go

Declare @i int=0
SELECT RAND(100)
WHILE @i <30000
BEGIN
Set @i=@i+1
Insert into tx1 values(RAND()*10000000,RAND()*10000000)
END;

GO

-- 45 sec
select * from tx1 t1
inner join tx1 t2 on(t1.x1=t2.x1 or t1.x2=t2.x2) and t1.id!=t2.id

-- <1 sec
select * from tx1 t1
inner join tx1 t2 on(t1.x1=t2.x1) and t1.id!=t2.id
union
select * from tx1 t1
inner join tx1 t2 on(t1.x2=t2.x2) and t1.id!=t2.id

Здравствуйте, tnikolai, Вы писали:

T>Все проблемы на достаточно простых случаях
Да, всё верно. Что вы хотите спросить?

Здравствуйте, tnikolai, Вы писали:

T>-- 45 sec
T>select * from tx1 t1
T>inner join tx1 t2 on(t1.x1=t2.x1 or t1.x2=t2.x2) and t1.id!=t2.id

а вот так тормозит? (or нет)
inner join tx1 t2 on not (t1.x1<>t2.x1 and t1.x2<>t2.x2) and t1.id!=t2.id

Здравствуйте, tnikolai, Вы писали:

T>Если в join есть OR типа
T>inner join tbl1 on(a=b OR c=d)
T>mssql ужасно тормозит, такая проблема часто возникает.

Тоже сталкивался с тормозами mysql по многим пунктам. Перешел для своих проектов на Постгрес, уже лет 10 как — не нарадуюсь и скорости, и функционалу. Переходи на постгрес))

Здравствуйте, paradok, Вы писали:

P>Здравствуйте, tnikolai, Вы писали:

T>>-- 45 sec
T>>select * from tx1 t1
T>>inner join tx1 t2 on(t1.x1=t2.x1 or t1.x2=t2.x2) and t1.id!=t2.id

P>а вот так тормозит? (or нет)
P>inner join tx1 t2 on not (t1.x1<>t2.x1 and t1.x2<>t2.x2) and t1.id!=t2.id

тоже 45 sec

Здравствуйте, tnikolai, Вы писали:

T>Здравствуйте, paradok, Вы писали:

P>>Здравствуйте, tnikolai, Вы писали:

T>>>-- 45 sec
T>>>select * from tx1 t1
T>>>inner join tx1 t2 on(t1.x1=t2.x1 or t1.x2=t2.x2) and t1.id!=t2.id

P>>а вот так тормозит? (or нет)
P>>inner join tx1 t2 on not (t1.x1<>t2.x1 and t1.x2<>t2.x2) and t1.id!=t2.id

T>тоже 45 sec

но в нем нет OR — только and — значет ты не туда копал и дело вовсе не в OR !
давай еще выдвигай гипотезы!

И еще попробуй запустить 2-3 раза подряд, иногда первый раз тормозит.

И конечно раз и AND тормозит то все плохо,так как зачастую
для сложного запроса на AND будет затруднительно разбить его на юнионы

Еще повертев запрос немного пришел к выводу что запрос сам по себе плохой
(две таблицы джойнятся не по уник идентификатоу)

Вот так у меня работает быстро (1 сек)

select top 1000000
*
from tx1 t1
inner join tx1 t2 on (t1.x1=t2.x1 or t1.x2=t2.x2)
where
t1.id!=t2.id

и учти что если много повторов (для это множитель в рандоме сделай 100 а не 10000000)
получается много совпадений и при 3000 объеме таблицы и множителе 100 получается 172000 результатов

автор, попробуй еще вот -на моем ПК это самый быстрый вариант -0.7 сек

select top 1000000
*
from tx1 t1
inner join tx1 t2 on ((t1.x1=t2.x1) and t1.id!=t2.id) or ((t1.x2=t2.x2) and t1.id!=t2.id)

Здравствуйте, paradok, Вы писали:

P>но в нем нет OR — только and — значет ты не туда копал и дело вовсе не в OR !
P>давай еще выдвигай гипотезы!

Я пробовал на Postgre подобное, он просто логическое выражение оптимизирует и NOT AND преращает в OR

Здравствуйте, Maniacal, Вы писали:

M>Здравствуйте, paradok, Вы писали:

P>>но в нем нет OR — только and — значет ты не туда копал и дело вовсе не в OR !
P>>давай еще выдвигай гипотезы!

M>Я пробовал на Postgre подобное, он просто логическое выражение оптимизирует и NOT AND преращает в OR

если расставить скобки подробно то все быстро
select top 1
*
from tx1 t1
inner join tx1 t2 on ((t1.x1=t2.x1) and t1.id!=t2.id) or ((t1.x2=t2.x2) and t1.id!=t2.id)

но в принципе не ясна задача топик стартера — его запрос выводит какую-то белиберду — все комбинации неуников — нафига это надо?
если генератор рандома плохой то их будет триллионы!

Здравствуйте, paradok, Вы писали:

P>автор, попробуй еще вот -на моем ПК это самый быстрый вариант -0.7 сек

P>select top 1000000
P>*
P>from tx1 t1
P> inner join tx1 t2 on ((t1.x1=t2.x1) and t1.id!=t2.id) or ((t1.x2=t2.x2) and t1.id!=t2.id)

top 1 быстро выполняется
без top всего возвращается 158 строк(RAND псевдослучайный, исходные данные должны быть всегда одинаковы), и все варианты дают около 45 сек.

Вроде mssql довольно серьёзная база, и я вообще не ожидал, что простой OR она вообще не способна оптимизировать.
Прочитал разные статьи по оптимизации, пытался хинты, индексы указать, бесполезно. Нет чтоб было написано в статьях прямо: оператор OR в mssql фактически можно считать запрещённым.

Здравствуйте, tnikolai, Вы писали:

T>Вроде mssql довольно серьёзная база, и я вообще не ожидал, что простой OR она вообще не способна оптимизировать.
T>Прочитал разные статьи по оптимизации, пытался хинты, индексы указать, бесполезно. Нет чтоб было написано в статьях прямо: оператор OR в mssql фактически можно считать запрещённым.

Oracle тоже в середине 2000-х не умел, думаю и сейчас вряд ли умеет. PostgreSQL тоже сейчас не умеет. Сделай на эти поля индексы и будет тебе счастье.

Здравствуйте, Maniacal, Вы писали:

M>Здравствуйте, tnikolai, Вы писали:

T>>Вроде mssql довольно серьёзная база, и я вообще не ожидал, что простой OR она вообще не способна оптимизировать.
T>>Прочитал разные статьи по оптимизации, пытался хинты, индексы указать, бесполезно. Нет чтоб было написано в статьях прямо: оператор OR в mssql фактически можно считать запрещённым.

M>Oracle тоже в середине 2000-х не умел, думаю и сейчас вряд ли умеет. PostgreSQL тоже сейчас не умеет. Сделай на эти поля индексы и будет тебе счастье.

Индексы не помогают.

Здравствуйте, tnikolai, Вы писали:

M>>Oracle тоже в середине 2000-х не умел, думаю и сейчас вряд ли умеет. PostgreSQL тоже сейчас не умеет. Сделай на эти поля индексы и будет тебе счастье.

T>Индексы не помогают.

Ещё как помогают, но для таких запросов они должны быть простыми, по одному полю.

Вот на Postgre без индексов твой пример выполняется за 90 секунд и план выполнения следующий:

А после создания индексов
CREATE INDEX tx1$x1 ON tx1(x1);
CREATE INDEX tx1$x2 ON tx1(x2);

Запрос выполняется одну секунду и план выполнения такой:

Здравствуйте, Maniacal, Вы писали:

M>Здравствуйте, tnikolai, Вы писали:

M>>>Oracle тоже в середине 2000-х не умел, думаю и сейчас вряд ли умеет. PostgreSQL тоже сейчас не умеет. Сделай на эти поля индексы и будет тебе счастье.

T>>Индексы не помогают.

M>Ещё как помогают, но для таких запросов они должны быть простыми, по одному полю.

M>Вот на Postgre без индексов твой пример выполняется за 90 секунд и план выполнения следующий:
M>Image: Plan1.png

M>А после создания индексов
M>CREATE INDEX tx1$x1 ON tx1(x1);
M>CREATE INDEX tx1$x2 ON tx1(x2);

M>Запрос выполняется одну секунду и план выполнения такой:
M>Image: Plan2.png

Так это Postgre, а на mssql не берёт индексы, на каждую строку tx1 перебирает все строки tx1 снова и это никак не оптимизируемо

Здравствуйте, tnikolai, Вы писали:

T>Так это Postgre, а на mssql не берёт индексы, на каждую строку tx1 перебирает все строки tx1 снова и это никак не оптимизируемо

У тебя индекс комбинированный: CREATE NONCLUSTERED INDEX IX_tx1 ON tx1 (x1,x2);

Нужно два отдельных индекса. Двойной не комбинированный, вроде, только Oracle умел пользовать как два отдельных.
Два независимых индекса даже на SQLite сработают, уверен. В индексе по двум полям почти у 100% СУБД поиск по второму полю происходит только после совпадения по первому. B-TREE в двух измерениях. Я с проблемами с OR ещё году в 2003ем столкнулся.

Здравствуйте, Maniacal, Вы писали:

M>Здравствуйте, tnikolai, Вы писали:

T>>Так это Postgre, а на mssql не берёт индексы, на каждую строку tx1 перебирает все строки tx1 снова и это никак не оптимизируемо

M>У тебя индекс комбинированный: CREATE NONCLUSTERED INDEX IX_tx1 ON tx1 (x1,x2);

M>Нужно два отдельных индекса. Двойной не комбинированный, вроде, только Oracle умел пользовать как два отдельных.
M>Два независимых индекса даже на SQLite сработают, уверен. В индексе по двум полям почти у 100% СУБД поиск по второму полю происходит только после совпадения по первому. B-TREE в двух измерениях. Я с проблемами с OR ещё году в 2003ем столкнулся.

CREATE NONCLUSTERED INDEX IX_tx1 ON tx1 (x1);
CREATE NONCLUSTERED INDEX IX_tx2 ON tx1 (x2);

всё равно никак

900 миллионов переборов

Ещё FORCESEEK нужно, тогда сработало
select * from tx1 t1 WITH (FORCESEEK)
inner join tx1 t2 on(t1.x1=t2.x1 or t1.x2=t2.x2) and t1.id!=t2.id

Наконец то нашёл как

CREATE NONCLUSTERED INDEX IX_tx1 ON tx1 (x1);
CREATE NONCLUSTERED INDEX IX_tx2 ON tx1 (x2);

select * from tx1 t1 WITH (FORCESEEK)
inner join tx1 t2 on(t1.x1=t2.x1 or t1.x2=t2.x2) and t1.id!=t2.id

Здравствуйте, tnikolai, Вы писали:

T>Наконец то нашёл как

А то я уже хотел предложить воспользоваться хинтами к плану выполнения запроса

Вот рекорд с OR без union — 0,046 сек или 46 миллисекунд

select
t1.id
from tx1 t1
left join tx1 t2 on t1.x1=t2.x1 and t1.id!=t2.id
left join tx1 t3 on t1.x2=t3.x2 and t1.id!=t3.id
where
t3.x2 = t1.x2 or t2.x1 = t1.x1

Здравствуйте, paradok, Вы писали:

P>Вот рекорд с OR без union — 0,046 сек или 46 миллисекунд

P>select
P> t1.id
P>from tx1 t1
P> left join tx1 t2 on t1.x1=t2.x1 and t1.id!=t2.id
P> left join tx1 t3 on t1.x2=t3.x2 and t1.id!=t3.id
P>where
P> t3.x2 = t1.x2 or t2.x1 = t1.x1

В реальных задачах ещё надо ещё join-ить к t2/t3 и там опять ветвление будет

Здравствуйте, tnikolai, Вы писали:

T>Здравствуйте, paradok, Вы писали:

P>>Вот рекорд с OR без union — 0,046 сек или 46 миллисекунд

P>>select
P>> t1.id
P>>from tx1 t1
P>> left join tx1 t2 on t1.x1=t2.x1 and t1.id!=t2.id
P>> left join tx1 t3 on t1.x2=t3.x2 and t1.id!=t3.id
P>>where
P>> t3.x2 = t1.x2 or t2.x1 = t1.x1

T>В реальных задачах ещё надо ещё join-ить к t2/t3 и там опять ветвление будет

давай реальный пример где у тебя опять ветвится-тормозится!
— подумаем, но пока видитcя этот подход мега универcальный и позволяет победить любое кол-во OR

Здравствуйте, gyraboo, Вы писали:

T>>inner join tbl1 on(a=b OR c=d)
G>Тоже сталкивался с тормозами mysql по многим пунктам. Перешел для своих проектов на Постгрес, уже лет 10 как — не нарадуюсь и скорости, и функционалу. Переходи на постгрес))
Совет правильный, но два пункта:
1) в топике mssql, а не mysql. Поборет ли Постгря МС в общем случае — это вопрос;
2) постгря себя ведёт для подобного запроса так же "тупо", как и mssql.

Сам недавно на Постгре столкнулся с подобным, нашёл обходной путь через UNION. Гордился собой, т.к. не знал, что это общая проблема

... << RSDN@Home 1.0.0 alpha 5 rev. 0>>

Здравствуйте, Rhino, Вы писали:

R>2) постгря себя ведёт для подобного запроса так же "тупо", как и mssql.

R>Сам недавно на Постгре столкнулся с подобным, нашёл обходной путь через UNION. Гордился собой, т.к. не знал, что это общая проблема

Ну так это не потому что оптимизатор тупой, а просто оптимизация дизъюнкции в общем виде не так проста и требует статического анализа условий на взаимоисключаемость. Грубо говоря, есть у тебя кортеж, на нем вычисляется A or B, на выходе этот кортеж будет 0 или 1 раз (в зависимости от истинности предиката). А если ты это перепишешь в union, где слева будет этот кортеж с А, а справа он же с B, то на выходе он будет 2 раза если оба условия истинны. Т.е. меняется семантика запроса, могут появиться дубликаты. В частных случаях оптимизатор может понять, когда такое преобразование валидно, а в общем надо или оставлять все как есть, либо переписывать в union с вычитанием того же отношения с условием A & B (это плохо масштабируется если условией через OR не два, а много).

Здравствуйте, kl, Вы писали:
kl>Ну так это не потому что оптимизатор тупой, а просто оптимизация дизъюнкции в общем виде не так проста и требует статического анализа условий на взаимоисключаемость. Грубо говоря, есть у тебя кортеж, на нем вычисляется A or B, на выходе этот кортеж будет 0 или 1 раз (в зависимости от истинности предиката). А если ты это перепишешь в union, где слева будет этот кортеж с А, а справа он же с B, то на выходе он будет 2 раза если оба условия истинны. Т.е. меняется семантика запроса, могут появиться дубликаты. В частных случаях оптимизатор может понять, когда такое преобразование валидно, а в общем надо или оставлять все как есть, либо переписывать в union с вычитанием того же отношения с условием A & B (это плохо масштабируется если условией через OR не два, а много).
Вроде как union устраняет дубликаты, поэтому переписывание на union формально можно делать без анализа пересекаемости условий.
ТС приводил пример именно с union, а не с union all.

Впрочем, это всего лишь означает, что union all будет ещё быстрее. А корректность его зависит от взаимосвязи между (a=b) и (c=d).

Здравствуйте, Sinclair, Вы писали:

S>Вроде как union устраняет дубликаты, поэтому переписывание на union формально можно делать без анализа пересекаемости условий.
S>ТС приводил пример именно с union, а не с union all.

А, прошу прощения, забыл об этой разнице!

S>Впрочем, это всего лишь означает, что union all будет ещё быстрее. А корректность его зависит от взаимосвязи между (a=b) и (c=d).

Ага. Устранение дубликатов — штука недешевая (если входные потоки не отсортированы по одному ключу).

Здравствуйте, kl, Вы писали:

kl>Здравствуйте, Sinclair, Вы писали:

S>>Вроде как union устраняет дубликаты, поэтому переписывание на union формально можно делать без анализа пересекаемости условий.
S>>ТС приводил пример именно с union, а не с union all.

kl>А, прошу прощения, забыл об этой разнице!

Кстати, в этом случае все равно могут измениться результаты т.к. union может убрать дубликаты, которые взялись не из-за пересекаемости условий, а из самого джойна (или базовых таблиц). А union all — добавить дубликаты. В общем, непростая эта тема, оптимизация дизъюнкций общего вида.

	От:	tnikolai
	Дата:	14.09.21 01:55
	Оценка:

	От:	takTak
	Дата:	14.09.21 05:18
	Оценка:

От:	Sinclair	https://github.com/evilguest/
Дата:	14.09.21 05:56
Оценка:	31 (4) +2

	От:	tnikolai
	Дата:	14.09.21 16:03
	Оценка:

От:	Sinclair	https://github.com/evilguest/
Дата:	15.09.21 04:12
Оценка:	+1

От:	kl	http://stardog.com
Дата:	01.10.21 19:12
Оценка:	40 (2) +2

	От:	paradok
	Дата:	16.09.21 06:01
	Оценка:

	От:	paradok
	Дата:	16.09.21 07:50
	Оценка:

	От:	paradok
	Дата:	16.09.21 08:32
	Оценка:

	От:	Maniacal
	Дата:	16.09.21 09:49
	Оценка: