Блог

Главная - Вопросы по программированию - SQL Server: как выполнить эффективное перекрестное соединение

SQL Server: как выполнить эффективное перекрестное соединение

#sql-server

Вопрос:

Мои данные структурированы следующим образом:

 create table data (id int, cluster int, weight float);
insert into data values (99,1,4);
insert into data values (99,2,3);
insert into data values (99,3,4);
insert into data values (1234,2,5);
insert into data values (1234,3,2);    
insert into data values (1234,4,3);

Тогда я должен вменить некоторые значения, потому что вектор имеет определенную длину x:

 declare @startnum int=0
declare @endnum int=4036;
with gen as (select @startnum as num
             union ALL
             select num 1 from gen where num 1<=@endnum)
select * into gen from gen -- store the numbers
option(maxrecursion 10000)

Затем мне нужно скрестить значения, хранящиеся в gen, но это делается в двух очень больших таблицах (не так, как в текущем примере), в настоящее время мой запрос выполняется более 2 часов, и я начинаю думать, что что-то не так. Есть какие-нибудь идеи о том, как я могу сделать эту процедуру быстрее и правильнее?

Вот чем я сейчас занимаюсь.

 select id, cluster, max(v) as weight
from (select id, cluster, case when cluster=num then weight else 0 end as v
     from data
          cross join gen) cross_num
group by id, cluster;
go

РЕДАКТИРОВАТЬ: Это последний запрос, который выполняется очень медленно, и, конечно, у меня очень большой набор данных 🙂

Примечание: Мне также интересно, в чем именно заключается План вставки, я на самом деле не знаю, как это искать, может ли кто-нибудь дать мне ресурс, который я могу найти и попытаться понять его?

Комментарии:

1. Публикация плана запроса была бы началом, если бы вы вставили план. Объяснить, чего вы пытаетесь достичь, было бы совсем другим. Однако использование a DISTINCT с a GROUP BY в одной и той же области всегда является признаком проблемы. Они GROUP BY уже должны распределять ваши данные по отдельным группам, так что либо GROUP BY это неправильно, либо DISTINCT бессмысленно (и просто добавляет ненужные расходы к вашему запросу).

2. case when cluster=cluster then weight else 0 end ? Кроме тех случаев, когда он имеет значение NULL , когда cluster не равен самому себе? CASE WHEN cluster IS NULL THEN 0 ELSE weight END был бы гораздо более разумный синтаксис. Я также очень рекомендую вам квалифицировать свои столбцы в этом подзапросе именем таблицы (или, еще лучше, псевдоним таблицы и использовать их для квалификатора).

3. Извините, не могли бы вы объяснить, для чего вы используете свою таблицу генов? Просто для вывода нескольких одинаковых строк? В любом случае, похоже, что значение gen не используется.

4. Я не понимаю, почему это не простое объединение и группа select id, cluster, max(weight) as weight from gen g left join data d on d.cluster = g.num group by d.id, d.cluster;

5. И именно поэтому вы берете gen g left join data d , а не data d join gen g таким образом получаете все строки gen , даже те, в которых нет совпадающих строк data . Я не получаю ваш текущий запрос, вы получите множество строк со всеми значениями null

Ответ №1:

Итак, проблема здесь в том, что вы создаете массивное декартово произведение и одновременно агрегируете.

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

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

Таким образом, первичный ключ для вашей таблицы не может быть id, cluster , потому что вы объединяетесь в этой группе. Если id, cluster это не очень избирательно-это означает, что для каждой комбинации существует много записей id, cluster , — то мы могли бы значительно сократить объем выполняемой здесь работы. Если для каждой записи будет 5 записей id, cluster , то это, вероятно, не сильно поможет, но если для каждой записи будет 100 000 id, cluster , то это, вероятно, очень поможет.

Сначала создайте свою gen таблицу. Я рекомендую создать кластеризованный первичный ключ на gen.num .

Во-вторых, давайте начнем собирать данные. Помните, я предполагаю StagingTable , что она пуста.

Вот первый запрос, который выполняет реальную работу:

 INSERT INTO StagingTable (id, cluster, weight)
SELECT id, cluster, MAX(weight) AS weight
FROM data
GROUP BY id, cluster

Запрос выиграет от индекса, но это будет зависеть от ваших данных, id, cluster, weight лучше или хуже, чем cluster, id, weight . Однако перед запуском этого вы должны отключить все индексы StagingTable , а затем перестроить индекс после запуска, по крайней мере, этой первой вставки.

В зависимости от ваших данных вам может потребоваться, или вы можете извлечь выгоду, или вам также следует избегать использования WHERE cluster BETWEEN 0 AND 4036 предложения в приведенном выше запросе. Это не для меня ясно, если есть 4037 кластеров пронумерованных от 0 до 4036, или если вы заинтересованы только в кластерах 0 до 4036 но есть более, или если вы заинтересованы только в создании «по умолчанию» отчеты вес 0 для групп от 0 до 4036, но хочу, чтобы все кластеры агрегированные если они происходят, чтобы пойти выше.

А теперь подумай о том, что внутри StagingTable . Все, что мы загрузили в эту таблицу, находится везде, где есть id, cluster в data таблице. Критически важно, что все id , что нам может понадобиться, будет в StagingTable наличии, даже если в нем отсутствует одно или несколько значений cluster .

Теперь нам просто нужно заполнить недостающие cluster значения для каждого id , и мы знаем, что weight один из недостающих кластеров таков 0 .

 INSERT INTO StagingTable (id, cluster, weight)
SELECT DISTINCT s.id, g.num, 0
FROM StagingTable s
INNER JOIN gen g
    ON g.num BETWEEN 0 AND 4036
WHERE NOT EXISTS (
        SELECT 1
        FROM StagingTable s2
        WHERE s2.id = s.id
            AND s2.cluster = g.num
    )

В INNER JOIN gen g ON g.num BETWEEN 0 AND 4036 этом может не быть необходимости, если gen всегда будут цифры от 0 до 4036. В таком случае вы можете просто использовать CROSS JOIN gen g .

Функция EXISTS необходима для удаления повторяющихся строк.

Опять же, для этого запроса может быть полезен индекс StagingTable , но без просмотра ваших фактических данных немного сложно точно сказать, что вам нужно (id, cluster) , это одна из возможностей, но (cluster, id) на самом деле может работать лучше. В идеале это должен быть кластеризованный первичный ключ.

Правка: Только что понял, что мой первоначальный второй запрос в некоторых случаях не сработает. Я изменил его, чтобы исправить логику.

Метки: