mssql сильно тормозит OR
От: tnikolai  
Дата: 14.09.21 01:55
Оценка:
Если в 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
Отредактировано 14.09.2021 15:59 tnikolai . Предыдущая версия . Еще …
Отредактировано 14.09.2021 15:58 tnikolai . Предыдущая версия .
Re: mssql сильно тормозит OR
От: takTak  
Дата: 14.09.21 05:18
Оценка:
T>Не хочется использовать вариант с UNION так как запросы большие и может быть много OR — будет куча дублированного кода.
T>Иногда помогает OPTION(TABLE HINT . . . но тоже не очень удобный вариант.
T>Есть ли что-то получше?

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

вообще, странно немного слышать про красоту SQL кода... ну будет больше строк и что?
зато всегда можно посмотреть на план выполнения запроса и понять , что имеет смысл, а что- меньше
Re: mssql сильно тормозит OR
От: Sinclair Россия https://github.com/evilguest/
Дата: 14.09.21 05:56
Оценка: 31 (4) +2
Здравствуйте, 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()) способна драматически изменить скорость выполнения.
Если разработчик не владеет такими нюансами, то добиться хорошей производительности приложения будет тяжело.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re[2]: mssql сильно тормозит OR
От: tnikolai  
Дата: 14.09.21 16:03
Оценка:
Все проблемы на достаточно простых случаях
например

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
Re[3]: mssql сильно тормозит OR
От: Sinclair Россия https://github.com/evilguest/
Дата: 15.09.21 04:12
Оценка: +1
Здравствуйте, tnikolai, Вы писали:

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

Да, всё верно. Что вы хотите спросить?
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re: mssql сильно тормозит OR
От: paradok  
Дата: 15.09.21 14:44
Оценка:
Здравствуйте, 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
Re: mssql сильно тормозит OR
От: gyraboo  
Дата: 15.09.21 14:46
Оценка: :))) :)))
Здравствуйте, tnikolai, Вы писали:

T>Если в join есть OR типа

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

Тоже сталкивался с тормозами mysql по многим пунктам. Перешел для своих проектов на Постгрес, уже лет 10 как — не нарадуюсь и скорости, и функционалу. Переходи на постгрес))
Re[2]: mssql сильно тормозит OR
От: tnikolai  
Дата: 15.09.21 15:27
Оценка:
Здравствуйте, 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
Re[3]: mssql сильно тормозит OR
От: paradok  
Дата: 16.09.21 06:01
Оценка:
Здравствуйте, 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 будет затруднительно разбить его на юнионы
Отредактировано 16.09.2021 6:32 paradok . Предыдущая версия . Еще …
Отредактировано 16.09.2021 6:13 paradok . Предыдущая версия .
Re: mssql сильно тормозит OR
От: paradok  
Дата: 16.09.21 07:50
Оценка:
Еще повертев запрос немного пришел к выводу что запрос сам по себе плохой
(две таблицы джойнятся не по уник идентификатоу)

Вот так у меня работает быстро (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 результатов
Отредактировано 16.09.2021 9:19 paradok . Предыдущая версия .
Re: mssql сильно тормозит OR
От: paradok  
Дата: 16.09.21 08:32
Оценка:
автор, попробуй еще вот -на моем ПК это самый быстрый вариант -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)
Re[4]: mssql сильно тормозит OR
От: Maniacal Россия  
Дата: 16.09.21 09:49
Оценка:
Здравствуйте, paradok, Вы писали:

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

P>давай еще выдвигай гипотезы!

Я пробовал на Postgre подобное, он просто логическое выражение оптимизирует и NOT AND преращает в OR
Re[5]: mssql сильно тормозит OR
От: paradok  
Дата: 16.09.21 09:59
Оценка:
Здравствуйте, 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)

но в принципе не ясна задача топик стартера — его запрос выводит какую-то белиберду — все комбинации неуников — нафига это надо?
если генератор рандома плохой то их будет триллионы!
Re[2]: mssql сильно тормозит OR
От: tnikolai  
Дата: 16.09.21 13:34
Оценка:
Здравствуйте, 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 фактически можно считать запрещённым.
Re[3]: mssql сильно тормозит OR
От: Maniacal Россия  
Дата: 16.09.21 13:52
Оценка:
Здравствуйте, tnikolai, Вы писали:

T>Вроде mssql довольно серьёзная база, и я вообще не ожидал, что простой OR она вообще не способна оптимизировать.

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

Oracle тоже в середине 2000-х не умел, думаю и сейчас вряд ли умеет. PostgreSQL тоже сейчас не умеет. Сделай на эти поля индексы и будет тебе счастье.
Re[4]: mssql сильно тормозит OR
От: tnikolai  
Дата: 16.09.21 13:56
Оценка:
Здравствуйте, Maniacal, Вы писали:

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


T>>Вроде mssql довольно серьёзная база, и я вообще не ожидал, что простой OR она вообще не способна оптимизировать.

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

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


Индексы не помогают.
Re[5]: mssql сильно тормозит OR
От: Maniacal Россия  
Дата: 16.09.21 14:33
Оценка:
Здравствуйте, tnikolai, Вы писали:

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


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


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

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


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

Запрос выполняется одну секунду и план выполнения такой:
Re[6]: mssql сильно тормозит OR
От: tnikolai  
Дата: 16.09.21 16:11
Оценка:
Здравствуйте, 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 снова и это никак не оптимизируемо
Re[7]: mssql сильно тормозит OR
От: Maniacal Россия  
Дата: 16.09.21 20:44
Оценка: 4 (1)
Здравствуйте, tnikolai, Вы писали:

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


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

Нужно два отдельных индекса. Двойной не комбинированный, вроде, только Oracle умел пользовать как два отдельных.
Два независимых индекса даже на SQLite сработают, уверен. В индексе по двум полям почти у 100% СУБД поиск по второму полю происходит только после совпадения по первому. B-TREE в двух измерениях. Я с проблемами с OR ещё году в 2003ем столкнулся.
Отредактировано 16.09.2021 20:52 Maniacal . Предыдущая версия .
Re[8]: mssql сильно тормозит OR
От: tnikolai  
Дата: 16.09.21 21:54
Оценка:
Здравствуйте, 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
Отредактировано 16.09.2021 22:14 tnikolai . Предыдущая версия .
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.