Jovano Jovano - 1 month ago 8
SQL Question

Nested view performance

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 Source

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