Luis Ferrao's questions

Considere o seguinte conjunto, que é o conteúdo da variável de tabela @ProductContent que acabou de ser criada para a consulta executada nela:

Observe como os itens são classificados por media_type_id, image_weight, image_id e finalmente image_width DESC. Se eu executar a seguinte consulta:

SELECT -- <images>
         pcs.image_type AS [@Type]
        ,pcs.image_type_id AS [@ImageTypeId]
        ,(
            SELECT -- <imageCollection>
                 pcc.image_id AS [@Guid]
                ,pcc.image_angle AS [@Angle]
                ,pcc.image_weight AS [@Weight]
                ,(
                    SELECT -- <image>
                         pci.mime_type AS [@MimeType]
                        ,pci.content_guid AS [@Guid]
                        ,pci.url AS [@Url]
                        ,pci.media_type_id AS '@MediaTypeId'
                        ,t.media_type_description '@Description'
                        ,(
                            SELECT DISTINCT -- <attribute>
                                a.meta_attribute_id '@Id'
                                ,a.meta_attribute_name  '@Name'
                                ,v.meta_value_name AS '@Value'
                            FROM [ADS2].dbo.ads_digital_content_meta m
                            JOIN [ADS2].dbo.ads_digital_content_meta_atr_voc a ON a.meta_attribute_id = m.meta_attribute_id
                            JOIN [ADS2].dbo.ads_digital_content_meta_value_voc v ON v.meta_value_id = m.meta_value_id
                            WHERE m.content_guid = pci.content_guid
                            FOR XML PATH('Attribute'), TYPE)
                    FROM
                        @ProductContent pci

                    JOIN [ADS2].dbo.ads_digital_content_media_type t ON t.media_type_id = pci.media_type_id

                    WHERE
                        pci.image_id = pcc.image_id

                    GROUP BY pci.content_guid, pci.mime_type, pci.url, pci.media_type_id, pci.image_width, t.media_type_description

                    FOR XML PATH('Image'), TYPE)        

            FROM @ProductContent pcc

            WHERE pcc.image_type_id = pcs.image_type_id
            AND pcc.product_id = pc.product_id

            GROUP BY
                    pcc.product_id
                ,pcc.image_weight
                ,pcc.image_id
                ,pcc.image_angle

            FOR XML PATH('ImageCollection'), TYPE)

    FROM @ProductContent pcs

    WHERE pcs.media_type_id = 15
    AND pcs.product_id = pc.product_id

    GROUP BY
        pcs.image_type,
        pcs.image_type_id

    FOR XML PATH('Images'), TYPE

Por algum motivo as imagens não saem na ordem correta. Por exemplo, a imagem com largura 400 vem primeiro, em vez da largura 640. Minha pergunta é, que parte desta consulta está mudando a ordem da forma como está em @ProductContent? Prefiro apenas classificar uma vez antes de inserir em @ProductContent, pois as cláusulas order by na consulta XML são um grande impacto no desempenho.

TL;DR

Como essa pergunta continua recebendo visualizações, vou resumi-la aqui para que os recém-chegados não tenham que sofrer com a história:

JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell
JOIN table t ON t.member = COALESCE(@value1, @value2)    -- this is blazing fast
-- Note that here if @value1 has a value, @value2 is NULL, and vice versa

Eu percebo que isso pode não ser um problema de todos, mas ao destacar a sensibilidade das cláusulas ON, isso pode ajudá-lo a olhar na direção certa. De qualquer forma, o texto original está aqui para futuros antropólogos:

Texto original

Considere a seguinte consulta simples (apenas 3 tabelas envolvidas)

    SELECT

        l.sku_id AS ProductId,
        l.is_primary AS IsPrimary,
        v1.category_name AS Category1,
        v2.category_name AS Category2,
        v3.category_name AS Category3,
        v4.category_name AS Category4,
        v5.category_name AS Category5

    FROM category c4
    JOIN category_voc v4 ON v4.category_id = c4.category_id and v4.language_code = 'en'

    JOIN category c3 ON c3.category_id = c4.parent_category_id
    JOIN category_voc v3 ON v3.category_id = c3.category_id and v3.language_code = 'en'

    JOIN category c2 ON c2.category_id = c3.category_id
    JOIN category_voc v2 ON v2.category_id = c2.category_id and v2.language_code = 'en'

    JOIN category c1 ON c1.category_id = c2.parent_category_id
    JOIN category_voc v1 ON v1.category_id = c1.category_id and v1.language_code = 'en'

    LEFT OUTER JOIN category c5 ON c5.parent_category_id = c4.category_id
    LEFT OUTER JOIN category_voc v5 ON v5.category_id = c5.category_id and v5.language_code = @lang

    JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
    (
        l.category_id = c4.category_id OR
        l.category_id = c5.category_id
    )

    WHERE c4.[level] = 4 AND c4.version_id = 5

Esta é uma consulta bastante simples, a única parte confusa é a última junção de categoria, é assim porque a categoria de nível 5 pode ou não existir. No final da consulta, estou procurando informações de categoria por ID do produto (ID do SKU), e é aí que entra a tabela muito grande category_link. Finalmente, a tabela #Ids é apenas uma tabela temporária contendo 10.000 IDs.

Quando executado, recebo o seguinte plano de execução real:

Como você pode ver, quase 90% do tempo é gasto nos Loops Aninhados (Inner Join). Aqui estão informações extras sobre esses loops aninhados:

Observe que os nomes das tabelas não correspondem exatamente porque editei os nomes das tabelas de consulta para facilitar a leitura, mas é muito fácil corresponder (ads_alt_category = category). Existe alguma maneira de otimizar essa consulta? Observe também que em produção, a tabela temporária #Ids não existe, é um parâmetro com valor de tabela dos mesmos 10.000 IDs passados ​​para o procedimento armazenado.

Informação adicional:

• índices de categoria em category_id e parent_category_id
• índice category_voc em category_id, language_code
• índice category_link no sku_id, category_id

Editar (resolvido)

Conforme apontado pela resposta aceita, o problema era a cláusula OR no category_link JOIN. No entanto, o código sugerido na resposta aceita é muito lento, mais lento até que o código original. Uma solução muito mais rápida e também muito mais limpa é simplesmente substituir a condição JOIN atual pela seguinte:

JOIN category_link l on l.sku_id IN (SELECT value FROM @p1) AND l.category_id = COALESCE(c5.category_id, c4.category_id)

Este ajuste de minuto é a solução mais rápida, testada contra a junção dupla da resposta aceita e também testada contra o CROSS APPLY, conforme sugerido por valverij.