Improve pivot view performance

I have a key-value structure for storing dynamic entities (standard EAV). I made a view for this Entity with the Pivot command. I tried to set the index for this view, but it is not possible due to the use of pivot.

Is there a way to drag my data into SQL at the time of insert, Update? Because performance is very important for this data.

CREATE VIEW View_Products WITH SCHEMABINDING
AS
SELECT [Children Bicycles], [Comfort Bicycles], [Cruisers Bicycles]
        , [Cyclocross Bicycles], [Electric Bikes], [Mountain Bikes], [Road Bikes]
FROM (
    SELECT category_name, product_id
    FROM dbo.production.products p
    INNER JOIN dbo.production.categories c ON c.category_id = p.category_id
) t
PIVOT (
    COUNT(product_id) FOR category_name IN (
        [Children Bicycles], [Comfort Bicycles], [Cruisers Bicycles]
        , [Cyclocross Bicycles], [Electric Bikes], [Mountain Bikes], [Road Bikes]
    )
) AS pivot_table

Answer

Not really. Unless you have very stupid table design errors (which you do NOT have) or are lacking indices that actually make sense – there is not a lot you can do.

What you CAN do is not using Pivot. Pivot is a runtime construct and I have had repeatedly scenarios where I was not doing runtime calcualtions on demand. I.e. your Vew_Products is a view – but it COULD be a table that is maintained by triggers. This will distribute the performance from query to insert / update / delete (with a small impact there) – but ultimately this may take out a LOT of performance from the query. Depending on Applicatoin this may be very beneficial.

There is a limit of what can be calculated at runtime, particularly if you talk of a non-trivial data set.