sql - 嵌套 View 性能

Question

我有这个 SQL 查询:

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

我可以解释我的查询的作用，但我认为这无关紧要，因为查询已经运行良好。我唯一的问题是处理时间；我必须等待 20-25 秒才能得到结果，我认为这有点太长了。

SQL Server 似乎做的(我认为)是 SELECT 我的值从 View [pp].dbo.VW_BEZETTING_RAW 11 次(我的每个值 10 次子查询和我的正常查询 1 次)。这是一项繁重的任务，因为 SELECT * FROM [pp].dbo.VW_BEZETTING_RAW 的处理时间大约为 2 秒。 SELECT 不是必需的，引用一次并在每个子查询中使用结果就足够了。我测试了我的性能问题是否真的是因为子查询，结果是:当我将查询减少到 2 个而不是 10 个子查询时，处理时间仅为 6-7 秒。

仅供引用，这里是 [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

我明确的问题是:是否可以SELECT [pp].dbo.VW_BEZETTING_RAW 的结果仅一次，并在每个子查询中使用此结果(而不是再次触发此 SELECT 查询 10 次)，或者，如果不可能，是否有任何其他方法可以减少我的查询的处理时间？

Answer 1

此查询应该可以解决您的问题(包括您是否可以使用负订单/时间值)。

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