Как написать запрос, который будет считывать меньше данных?

Question

[mak_arti]

Таблица Crosses (ArticleId, CrossArticleId)

1 | 5
5 | 10
5 | 12
1 | 6
6 | 10
6 | 13
1 | 8
8 | 10
1 | 9
9 | 13
9 | 15

Строки ArticleId = 1 во временную таблицу @tempFlatCrosses (ArticleId, CrossArticleId)

1 | 5
1 | 6
1 | 8
1 | 9

Далее нужно сделать повторную выборку

select с.CrossArticleId from Crosses c
inner join @tempFlatCrosses f on c.ArticleId = f.CrossArticleId

То мы получим много дубликатов, будет считывать много лишних строк из таблицы Crosses.

10
12
10
13
10
13
15

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

Answer 1

[Иван]

Запрос можно оптимизировать тремя способами:
1) Использовать индексы по полям ArticleId и CrossArticleId в таблице Crosses;
2) Использовать группировку по полю CrossArticleId во временной таблице @tempFlatCrosses, чтобы избежать дубликатов при соединении с таблицей Crosses;
3) Использовать оператор EXISTS, вместо INNER JOIN, чтобы проверить наличие соответствующих записей в таблице Crosses, без возврата лишних данных.

-- Создаем индексы по полям ArticleId и CrossArticleId в таблице Crosses
CREATE INDEX idx_crosses_articleid ON Crosses (ArticleId);
CREATE INDEX idx_crosses_crossarticleid ON Crosses (CrossArticleId);

-- Достаем все строки, у которых ArticleId = 1 и группируем по CrossArticleId
SELECT ArticleId, CrossArticleId
INTO @tempFlatCrosses
FROM Crosses
WHERE ArticleId = 1
GROUP BY CrossArticleId;

-- Делаем выборку из таблицы Crosses, используя оператор EXISTS
SELECT c.ArticleId
FROM Crosses c
WHERE EXISTS (
  SELECT 1
  FROM @tempFlatCrosses f
  WHERE c.CrossArticleId = f.CrossArticleId
);

Comments

[mak_arti]

SELECT c.CrossArticleId
FROM Crosses c
WHERE EXISTS (
  SELECT 1
  FROM @tempFlatCrosses f
  WHERE c.ArticleId = f.CrossArticleId
);

Так мы тоже получим дубли( Просто не знаем как подсказать серверу, что такой CrossArticleId один раз уже учли

Answer 2

[MVV]

Здесь временная таблица не нужна: все можно сделать одним запросом:

select distinct c.CrossArticleId from Crosses c inner join Crosses f 
on c.ArticleId=f.CrossArticleId where f.ArticleId=1

А чтобы избежать сканирования всей таблицы, создайте (если их ещё нет)индексы по полям Crosses.ArticleId и Crosses.CrossArticleId - например, как в предыдущем ответе написано. И пусть работает и оптимизирует запрос планировщик: он кремниевый, и у него есть статистика по индексам. Если вдруг сомневаетесь в планировщике - посмотрите план выполнения запроса.
Индексы - это ключевой момент: без них серверу придется сканировать всю таблицу.

Comments

[mak_arti]

Спасибо, мне решение понятна. Нужные индексы есть. Если в "inner join Crosses" будут 2000 записей, удовлетворяющие условии, то уже будет много логический чтений, более 200 000 строк. А в итоге получаю 3000 записей уникальных. Наверное задал тупой вопрос. Идеи нет

[MVV]

Сделайте запрос и посмотрите его план выполнения. Возможно, идеи появятся. Или вы уже делали, но просто про это не рассказали?

[mak_arti]

MVV, Делал, все нужные индексы есть, все seek'ается.
Попробую на примере:
Есть две кросс-связи 1- 2, 1 - 3. Собственно у 2 есть 4, и у 3 есть 4. В этом случае будет два логических чтений 2-4, 3-4. Я просто подумал есть ли возможность сделать если прочитали 2-4, то остальных строк игнорировали на чтение, у которых есть связь к 4. В данном примере игнорировать бы чтение строки 3-4