Синтаксис SQL Server против множественного соединения

Что более эффективно использовать в SQL Server 2005: PIVOT или MULTIPLE JOIN?

Например, я получил этот запрос, используя два соединения:

SELECT p.name, pc1.code as code1, pc2.code as code2
FROM product p
    INNER JOIN product_code pc1
    ON p.product_id=pc1.product_id AND pc1.type=1
    INNER JOIN product_code pc2
    ON p.product_id=pc2.product_id AND pc2.type=2

Я могу сделать то же самое с помощью PIVOT:

SELECT name, [1] as code1, [2] as code2
FROM (
    SELECT p.name, pc.type, pc.code
    FROM product p
        INNER JOIN product_code pc
        ON p.product_id=pc.product_id
    WHERE pc.type IN (1,2)) prods1
PIVOT(
    MAX(code) FOR type IN ([1], [2])) prods2

Какой из них будет более эффективным?

Ответы

Ответ 1

Ответ, конечно, будет "это зависит", но на основе тестирования этой цели...

Предполагая

  • 1 млн. продуктов
  • product имеет кластеризованный индекс на product_id
  • Большинство (если не все) продуктов имеют соответствующую информацию в таблице product_code
  • Идеальные индексы, присутствующие в product_code для обоих запросов.

Версия PIVOT в идеале нуждается в индексе product_code(product_id, type) INCLUDE (code), тогда как версия JOIN в идеале нуждается в индексе product_code(type,product_id) INCLUDE (code)

Если они находятся на месте, давая планы ниже

Plans

то версия JOIN более эффективна.

В случае, когда type 1 и type 2 являются единственными types в таблице, тогда версия PIVOT слегка имеет край в терминах количества чтений, так как ему не нужно искать в product_code дважды, но это более чем перевешивается дополнительными служебными данными оператора агрегации потока

PIVOT

Table 'product_code'. Scan count 1, logical reads 10467
Table 'product'. Scan count 1, logical reads 4750
   CPU time = 3297 ms,  elapsed time = 3260 ms.

Join

Table 'product_code'. Scan count 2, logical reads 10471
Table 'product'. Scan count 1, logical reads 4750
   CPU time = 1906 ms,  elapsed time = 1866 ms.

Если есть дополнительные type записи, отличные от 1 и 2, версия JOIN увеличит ее преимущество, так как она просто объединяет объединения в соответствующих разделах индекса type,product_id, тогда как PIVOT plan использует product_id, type и поэтому придется сканировать дополнительные строки type, которые смешаны с строками 1 и 2.

Ответ 2

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

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