We have this query in a non-indexed view in SQL Server 2022.

It’s used everywhere and I’m trying to improve the performance. A lot of queries we use elsewhere join to it.

My thought was whether there’s a way to re-write it so it could be an indexed view but I can’t figure out how! It has a UNION and EXISTS which rules an indexed view out.

If that’s not possible, a more performant way of getting the same result would be a step in the right direction.

Here’s the query:

    SELECT
        sub_product.product_id,
        sub_product_price.sub_product_id,
        sub_product_price.currency_id,
        sub_product_price.pricing_tier_id,
        sub_product_price.price,
        0 as inherited
    FROM cms_module_product_product_sub_product_price sub_product_price
    INNER JOIN cms_module_product_product_sub_product sub_product ON sub_product_price.sub_product_id = sub_product.id

    UNION ALL

    SELECT
        product_price.product_id,
        sub_product.id as sub_product_id,
        product_price.currency_id,
        product_price.pricing_tier_id,
        product_price.price,
        1 as inherited
    FROM cms_module_product_product_price product_price
    LEFT OUTER JOIN cms_module_product_product_sub_product sub_product ON product_price.product_id = sub_product.product_id
    WHERE NOT EXISTS (
        SELECT 1
        FROM cms_module_product_product_sub_product_price sub_product_price
        WHERE sub_product_price.sub_product_id = sub_product.id
        AND sub_product_price.currency_id = product_price.currency_id
        AND sub_product_price.pricing_tier_id = product_price.pricing_tier_id
    )

The gist of the query is to merge prices for sub products (variants) and products together. If the sub product doesn’t have a price set, it inherits from the product.