Nested view performance

Question

I have this SQL-query:

SELECT Start,
    ISNULL((SELECT Orders FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'WP'),0) AS Werkplaats_ord,
    ISNULL((SELECT Tijd FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'WP'),0) AS Werkplaats_tijd, 
    ISNULL((SELECT Orders FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'MAG'),0) AS Magazijn_ord,
    ISNULL((SELECT Tijd FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'MAG'),0) AS Magazijn_tijd, 
    ISNULL((SELECT Orders FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'SERV'),0) AS Service_ord,
    ISNULL((SELECT Tijd FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'SERV'),0) AS Service_tijd, 
    ISNULL((SELECT Orders FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'CNC'),0) AS Draaibank_ord,
    ISNULL((SELECT Tijd FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'CNC'),0) AS Draaibank_tijd, 
    ISNULL((SELECT Orders FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'SECL'),0) AS Bougiekabels_ord,
    ISNULL((SELECT Tijd FROM [pp].dbo.VW_BEZETTING_RAW b2 WHERE b2.Start=b1.Start AND Afdeling = 'SECL'),0) AS Bougiekabels_tijd 
FROM [pp].dbo.VW_BEZETTING_RAW b1
GROUP BY Start

I could explain what my query does, but I don't think that would be relevant as the query already works fine. My only problem is the processing time; I have to wait 20-25 seconds for my result and I think that's a bit to long.

What SQL Server seems to do (I think), is SELECT my values 11 times from view [pp].dbo.VW_BEZETTING_RAW (10 times for each of my subquery's and 1 time for my normal query). That's a heavy task because SELECT * FROM [pp].dbo.VW_BEZETTING_RAW has a processing time from about 2 seconds. SELECT is not neccessary, it should be enough to refer it one time and use the result in each of the subquery's. I tested if my performance-issue is really because of the subquery, and it is: when I reduce my query to just 2 instead of 10 subquery's, the processing time is just 6-7 seconds.

Just for information, here is a part of the result of [pp].dbo.VW_BEZETTING_RAW:

    Start       Afdeling    Orders  Tijd
    2013-05-14  SERV        3       0
    2013-05-14  WP          17      0
    2013-05-15  MAG         1       0
    2013-05-15  SERV        3       0
    2013-05-15  WP          14      0
    2013-05-16  CNC         1       0
    2013-05-16  MAG         9       0
    2013-05-16  SERV        3       0
    2013-05-16  WP          22      0
    2013-05-17  MAG         19      0
    2013-05-17  WP          8       0
    2013-05-20  MAG         11      0

My explicit question is: Is it possible to SELECT the results of [pp].dbo.VW_BEZETTING_RAW only one time and use the result of this in every subquery (instead of trigger this SELECT query 10 times again), or, if not possible, is there any other way to reduce the processing time for my query?

Answer 1

Try this -

;WITH cte AS 
(
    SELECT 
          Orders
        , Tijd
        , Afdeling
        , Start 
    FROM dbo.VW_BEZETTING_RAW    
)
SELECT Start,
    ISNULL((SELECT Orders FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'WP'),0) AS Werkplaats_ord,
    ISNULL((SELECT Tijd FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'WP'),0) AS Werkplaats_tijd, 
    ISNULL((SELECT Orders FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'MAG'),0) AS Magazijn_ord,
    ISNULL((SELECT Tijd FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'MAG'),0) AS Magazijn_tijd, 
    ISNULL((SELECT Orders FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'SERV'),0) AS Service_ord,
    ISNULL((SELECT Tijd FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'SERV'),0) AS Service_tijd, 
    ISNULL((SELECT Orders FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'CNC'),0) AS Draaibank_ord,
    ISNULL((SELECT Tijd FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'CNC'),0) AS Draaibank_tijd, 
    ISNULL((SELECT Orders FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'SECL'),0) AS Bougiekabels_ord,
    ISNULL((SELECT Tijd FROM cte b2 WHERE b2.Start=b1.Start AND Afdeling = 'SECL'),0) AS Bougiekabels_tijd 
FROM cte b1
GROUP BY Start

Or try this -

SELECT 
    Start,
    Werkplaats_ord = MIN(CASE WHEN Afdeling = 'WP' THEN Orders ELSE 0 END) ,
    Werkplaats_tijd = MIN(CASE WHEN Afdeling = 'WP' THEN Tijd ELSE 0 END) , 
    Magazijn_ord = MIN(CASE WHEN Afdeling = 'MAG' THEN Orders ELSE 0 END) ,
    Magazijn_tijd = MIN(CASE WHEN Afdeling = 'MAG' THEN Tijd ELSE 0 END) , 
    Service_ord = MIN(CASE WHEN Afdeling = 'SERV' THEN Orders ELSE 0 END) ,
    Service_tijd = MIN(CASE WHEN Afdeling = 'SERV' THEN Tijd ELSE 0 END) , 
    Draaibank_ord = MIN(CASE WHEN Afdeling = 'CNC' THEN Orders ELSE 0 END) ,
    Draaibank_tijd = MIN(CASE WHEN Afdeling = 'CNC' THEN Tijd ELSE 0 END) , 
    Bougiekabels_ord = MIN(CASE WHEN Afdeling = 'SECL' THEN Orders ELSE 0 END) ,
    Bougiekabels_tijd = MIN(CASE WHEN Afdeling = 'SECL' THEN Tijd ELSE 0 END)  
FROM dbo.VW_BEZETTING_RAW b1
GROUP BY Start

Answer 2

This query should solve your problem (including if you can have negative Orders/Time values).

SELECT Start,
    ISNULL(MIN(CASE WHEN Afdeling = 'WP' THEN Orders END),0) AS Werkplaats_ord,
    ISNULL(MIN(CASE WHEN Afdeling = 'WP' THEN Tijd END),0) AS Werkplaats_tijd, 
    ISNULL(MIN(CASE WHEN Afdeling = 'MAG' THEN Orders END),0) AS Magazijn_ord,
    ISNULL(MIN(CASE WHEN Afdeling = 'MAG' THEN Tijd END),0) AS Magazijn_tijd, 
    ISNULL(MIN(CASE WHEN Afdeling = 'SERV' THEN Orders END),0) AS Service_ord,
    ISNULL(MIN(CASE WHEN Afdeling = 'SERV' THEN Tijd END),0) AS Service_tijd, 
    ISNULL(MIN(CASE WHEN Afdeling = 'CNC' THEN Orders END),0) AS Draaibank_ord,
    ISNULL(MIN(CASE WHEN Afdeling = 'CNC' THEN Tijd END),0) AS Draaibank_tijd, 
    ISNULL(MIN(CASE WHEN Afdeling = 'SECL' THEN Orders END),0) AS Bougiekabels_ord,
    ISNULL(MIN(CASE WHEN Afdeling = 'SECL' THEN Tijd END),0) AS Bougiekabels_tijd 
FROM [pp].dbo.VW_BEZETTING_RAW b1
GROUP BY Start