Если в 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()) способна драматически изменить скорость выполнения.
Если разработчик не владеет такими нюансами, то добиться хорошей производительности приложения будет тяжело.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, 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
Здравствуйте, 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 результатов
Здравствуйте, 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