Медленный запрос с предложением where

У меня есть следующий sql-запрос, который выполняется только 1 секунда:

select a.date, b.rate, c.type, a.value from

a inner join b on a.id = b.aid
c inner join b.id = c.bid
where a.name = 'xxx'

Но мне нужен набор результатов, чтобы получить результаты со скоростью больше 0. Поэтому, когда я меняю запрос на это, требуется 7 минут:

select a.date, b.rate, c.type, a.value from

a inner join b on a.id = b.aid
c inner join b.id = c.bid
where a.name = 'xxx' and b.rate>0

Почему это заставляет время запроса увеличиваться с 1 секунды до 7 минут? Поскольку таблица b огромна, я даже пытался использовать CTE, но это также не улучшало производительность. Я думал, что с CTE будет меньше набор значений для фильтрации, поэтому он должен быть быстрее, но это не помогло:

;with x as
(select a.date, b.rate, c.type, a.value from

a inner join b on a.id = b.aid
c inner join b.id = c.bid
where a.name = 'xxx')
select * from x where rate>0

Я не могу включить план выполнения, поскольку у меня нет прав на db, кроме запроса.

Ответ 1

Я предполагаю, что медленный план выполнения делает фильтр rate>0 неудачным способом, например, как часть сканирования внутри соединения цикла или что-то в этом роде.

Если это произойдет, одним из решений будет сохранение промежуточного набора результатов и его фильтрация в отдельном заявлении.

Я предлагаю это с пониманием того, что вы не можете вносить изменения в свою базу данных поставщика и что вы в основном застреваете. Это существенно устраняет некоторый контроль от оптимизатора - то, чего вы обычно не хотите делать, а также добавляет относительно небольшое количество накладных расходов при создании таблицы temp. Но в этом случае это должно облегчить медлительность. Я по-прежнему буду работать с вашим продавцом по стратегиям индексирования.

select a.date, b.rate, c.type, a.value 
into #t
from a inner join b on a.id = b.aid
c inner join b.id = c.bid
where a.name = 'xxx' 

select * from #t where rate>0

Ответ 2

Создайте индексы в столбцах rate и name, и вы увидите большую разницу.

EDIT:

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

EDIT:

Инструкции по индексированию

Ответ 3

Если вы пытаетесь рассказать серверу о том, как оптимизировать ваш запрос, используя CTE, я думаю, вы шутите. Вы получите лучшие результаты с индексами. Но в любом случае у вас, похоже, есть часть CTE на голове. Вы хотите попросить сервер сначала выполнить CTE, а затем оценить внешний запрос. Как это возможно...

;with x as (Select a.id, a.date, a.value from a where a.name='xxx')
Select x.date, b.rate, c.type, x.value
From x
  Inner join b on x.id=b.aid
  Inner Join c on b.id=c.bid
where b.rate>0

или

;with x as (
   Select b.id, b.rate, c.type 
   From b 
     Inner Join c on b.id=c.bid
   where b.rate>0)
Select a.date, x.rate, x.type, a.value
From a
  Inner join x on a.id=b.id
Where a.name='xxx'

Ответ 4

Прежде всего позвольте мне объяснить, почему у вас плохой план. Одно из них - то, о чем я мог подумать. когда не было ставки. Таблица a была первой и основывалась на sarg, и индекс сказал, что он оценил 10000 строк. Это могло бы использовать объединение merge.Now, чтобы присоединиться к таблице b (на данный момент предполагается, что существует от 1 до n сопоставление, и на avg есть 2 строки для каждой строки a). Затем оценочные строки после объединения составляют 20000, а затем соединяются с таблицей C.Now в зависимости от того, насколько велика таблица C, которую он мог бы использовать, например, Merge Join.Say. Фактические строки были в 100 тысячах

Но когда вы добавили скорость SARG > 0. Тогда оптимизатор не оценил 20K строк после объединения A и B, но вместо этого оценил 6667 строк (по умолчанию 30% для > 0, когда нет auto_create_statistics), а затем возможно, выбрали ответ вложенного объединения цикла с таблицей C вместо объединения слиянием. Но фактические строки могли быть намного больше, и поэтому вложенное объединение циклов могло бы использоваться для 100 тысяч, и, таким образом, окончательное объединение вложенных циклов могло бы взять эту большую часть время.

В целом, я пытаюсь сказать, что из-за дополнительного sarg были неправильные оценки от оптимизатора и, следовательно, плохой план.

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

Включение или выключение auto_create_statistics для вашей базы данных? Или таблица b имеет статистику для скорости столбца? Когда статистика обновляется/создается для этих таблиц? если не сначала создать статистику для этого столбца, и я уверен, что ваш план будет нормальным. Если вы не можете создать статистику, попробуйте заставить тот же план, что и вы, без ставки > 0 CTE не улучшает код perf как таковой, может быть, экспрессия. код более читабельный.

Ответ 5

Возможно, вы сможете отправить оптимизатор по правильному пути, применив объединение слиянием:

Выбрать... из таблицы А оставьте объединение присоединиться к TableB на...

Ответ 6

Часть b>0 радикально меняет его, потому что сервер больше не может оптимизировать запрос, используя кластерный индекс и другие методы. Когда вы добавляете b>0, который превращает этот запрос в запрос диапазона, который потребует гораздо больше времени, потому что он действительно должен проверять больше значений. Есть несколько оптимизаций, которые, как я считаю, уже существуют, например организации B-tree для таких запросов, но это не улучшит их.