SQL查询优化：如何避免重复调用相同子查询？

问题背景 #

原始查询中多次重复调用完全相同的子查询，导致执行效率低下。现尝试通过临时表优化，同时希望了解如何通过函数或存储过程关联主查询。

原始查询 #

SELECT
       t.[DataTran]
      ,t.[Dok]
      ,t.[Product]
      ,p.idxbox
      ,po.[Mark]
      ,po.[Model]
      ,(SELECT 
            TOP(1) [poz_La]
        FROM [RaportyAutko].[dbo].[G_API_Data]
        WHERE
            idxbox= p.idxbox 
            AND DATEDIFF(second,{d '1970-01-01'}, [dataandtime])  <  DATEDIFF(second,{d '1970-01-01'},t.[DataTran]) 
            AND  [distance]<>0
        ORDER BY dataandtime DESC) AS [poz_La]
      ,(SELECT 
            TOP(1) [poz_Lo]
        FROM [RaportyAutko].[dbo].[G_API_Data]
        WHERE
            idxbox= p.idxbox 
            AND DATEDIFF(second,{d '1970-01-01'}, [dataandtime])  <  DATEDIFF(second,{d '1970-01-01'},t.[DataTran]) 
            AND  [distance]<>0
        ORDER BY dataandtime DESC) AS [poz_Lo]
      ,(SELECT 
            TOP(1) [adress]
        FROM [RaportyAutko].[dbo].[G_API_Data]
        WHERE
            idxbox= p.idxbox 
            AND DATEDIFF(second,{d '1970-01-01'}, [dataandtime])  <  DATEDIFF(second,{d '1970-01-01'},t.[DataTran]) 
            AND  [distance]<>0
        ORDER BY dataandtime DESC) AS [adress]
      ,(SELECT 
            TOP(1) [dyst]
        FROM [RaportyAutko].[dbo].[G_API_Data]
        WHERE
            idxbox= p.idxbox 
            AND DATEDIFF(second,{d '1970-01-01'}, [dataandtime])  <  DATEDIFF(second,{d '1970-01-01'},t.[DataTran]) 
            AND  [distance]<>0
        ORDER BY dataandtime DESC) AS [dyst]
      ,(SELECT 
            TOP(1) Total
        FROM [RaportyAutko].[dbo].[G_API_Data]
        WHERE
            idxbox= p.idxbox 
            AND DATEDIFF(second,{d '1970-01-01'}, [dataandtime])  <  DATEDIFF(second,{d '1970-01-01'},t.[DataTran]) 
            AND  [distance]<>0
        ORDER BY dataandtime DESC) AS Total
  FROM [ReportsA].[dbo].[Tank] t
      LEFT JOIN [ReportsA].[dbo].[G_API_CARS] p ON t.[NumberRej] = p.[nrRej]
      LEFT JOIN [ReportsA].[dbo].[POJ] po ON t.[NumberRej] = po.[Nr rej]
  WHERE t.[NumberRej] IS NOT NULL

用户尝试的优化方案（存在问题） #

CREATE TABLE [dbo].[GBOX_API_PRZEBIEG]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [poz_La] [float] NULL,
    [poz_Lo] [float] NULL,
    [adress] [varchar](255) NULL -- 语法错误：缺少逗号
    [dist] [int] NULL,
    [Total] [bigint] NULL   
) ON [PRIMARY]
GO

INSERT INTO #tmpData -- 逻辑错误：表名与创建的GBOX_API_PRZEBIEG不匹配，且无法引用主查询的p.idxbox和t.DataTran
           ([poz_La]
           ,[poz_Lo]
           ,[adress] 
           ,[dist]
           ,[Total])
    (SELECT 
        TOP(1) 
        poz_La, poz_Lo, adress, dist, Total
    FROM [RaportyAutko].[dbo].[G_API_Data]
            WHERE
                idxbox= p.idxbox 
                AND DATEDIFF(second,{d '1970-01-01'}, [dataandtime])  <  DATEDIFF(second,{d '1970-01-01'},t.[DataTran]) 
                AND  [distance]<>0
            ORDER BY dataandtime DESC)

优化方案解答 #

1. 当前临时表方案的问题 #

• 语法错误：创建表时[adress] [varchar](255) NULL后缺少逗号，导致表创建失败。
• 逻辑错误：插入语句使用#tmpData，但实际创建的是永久表GBOX_API_PRZEBIEG，表名不匹配。
• 关联失效：临时表插入操作无法直接引用主查询的p.idxbox和t.DataTran，两者上下文不共享。

2. 正确的优化方式 #

方式一：CTE（公共表表达式）预计算数据

先为每个idxbox筛选出符合条件的最新记录，再关联主查询，彻底避免重复子查询：

WITH LatestGAPI AS (
    SELECT 
        g.idxbox,
        g.dataandtime,
        g.poz_La,
        g.poz_Lo,
        g.adress,
        g.dyst,
        g.Total,
        -- 按idxbox分组，取时间最晚的记录
        ROW_NUMBER() OVER (PARTITION BY g.idxbox ORDER BY g.dataandtime DESC) AS rn
    FROM [RaportyAutko].[dbo].[G_API_Data] g
    WHERE g.[distance] <> 0
)
SELECT
       t.[DataTran]
      ,t.[Dok]
      ,t.[Product]
      ,p.idxbox
      ,po.[Mark]
      ,po.[Model]
      ,lg.poz_La
      ,lg.poz_Lo
      ,lg.adress
      ,lg.dyst
      ,lg.Total
FROM [ReportsA].[dbo].[Tank] t
LEFT JOIN [ReportsA].[dbo].[G_API_CARS] p ON t.[NumberRej] = p.[nrRej]
LEFT JOIN [ReportsA].[dbo].[POJ] po ON t.[NumberRej] = po.[Nr rej]
-- 关联CTE，筛选时间早于t.DataTran的最新记录
LEFT JOIN LatestGAPI lg 
    ON lg.idxbox = p.idxbox 
    AND lg.dataandtime < t.[DataTran]
    AND lg.rn = 1
WHERE t.[NumberRej] IS NOT NULL

方式二：临时表存储预计算数据

若数据量较大，CTE性能不如临时表，可先存储每个idxbox的最新符合条件记录，再关联主查询：

-- 创建带索引的临时表，提升关联效率
CREATE TABLE #LatestGAPI (
    idxbox INT, -- 按实际字段类型调整
    dataandtime DATETIME,
    poz_La FLOAT,
    poz_Lo FLOAT,
    adress VARCHAR(255),
    dyst INT,
    Total BIGINT,
    PRIMARY KEY (idxbox, dataandtime)
)

-- 插入每个idxbox的最新符合条件记录
INSERT INTO #LatestGAPI
SELECT 
    g.idxbox,
    g.dataandtime,
    g.poz_La,
    g.poz_Lo,
    g.adress,
    g.dyst,
    g.Total
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY idxbox ORDER BY dataandtime DESC) AS rn
    FROM [RaportyAutko].[dbo].[G_API_Data]
    WHERE [distance] <> 0
) g
WHERE g.rn = 1

-- 主查询关联临时表
SELECT
       t.[DataTran]
      ,t.[Dok]
      ,t.[Product]
      ,p.idxbox
      ,po.[Mark]
      ,po.[Model]
      ,lg.poz_La
      ,lg.poz_Lo
      ,lg.adress
      ,lg.dyst
      ,lg.Total
FROM [ReportsA].[dbo].[Tank] t
LEFT JOIN [ReportsA].[dbo].[G_API_CARS] p ON t.[NumberRej] = p.[nrRej]
LEFT JOIN [ReportsA].[dbo].[POJ] po ON t.[NumberRej] = po.[Nr rej]
LEFT JOIN #LatestGAPI lg 
    ON lg.idxbox = p.idxbox 
    AND lg.dataandtime < t.[DataTran]
WHERE t.[NumberRej] IS NOT NULL

-- 清理临时表
DROP TABLE #LatestGAPI

3. 函数或存储过程关联方案 #

方式一：表值函数（推荐）

创建表值函数返回所需列，通过OUTER APPLY关联主查询：

CREATE FUNCTION dbo.GetLatestGAPIInfo(@idxbox INT, @cutoffTime DATETIME)
RETURNS TABLE
AS
RETURN (
    SELECT TOP(1)
        poz_La,
        poz_Lo,
        adress,
        dyst,
        Total
    FROM [RaportyAutko].[dbo].[G_API_Data]
    WHERE 
        idxbox = @idxbox
        AND dataandtime < @cutoffTime
        AND [distance] <> 0
    ORDER BY dataandtime DESC
)
GO

-- 主查询调用函数
SELECT
       t.[DataTran]
      ,t.[Dok]
      ,t.[Product]
      ,p.idxbox
      ,po.[Mark]
      ,po.[Model]
      ,g.poz_La
      ,g.poz_Lo
      ,g.adress
      ,g.dyst
      ,g.Total
FROM [ReportsA].[dbo].[Tank] t
LEFT JOIN [ReportsA].[dbo].[G_API_CARS] p ON t.[NumberRej] = p.[nrRej]
LEFT JOIN [ReportsA].[dbo].[POJ] po ON t.[NumberRej] = po.[Nr rej]
OUTER APPLY dbo.GetLatestGAPIInfo(p.idxbox, t.[DataTran]) g
WHERE t.[NumberRej] IS NOT NULL

方式二：存储过程封装查询

将优化后的查询封装为存储过程，方便重复执行：

CREATE PROCEDURE dbo.GetTankWithGAPIInfo
AS
BEGIN
    SET NOCOUNT ON;
    WITH LatestGAPI AS (
        SELECT 
            g.idxbox,
            g.dataandtime,
            g.poz_La,
            g.poz_Lo,
            g.adress,
            g.dyst,
            g.Total,
            ROW_NUMBER() OVER (PARTITION BY g.idxbox ORDER BY g.dataandtime DESC) AS rn
        FROM [RaportyAutko].[dbo].[G_API_Data] g
        WHERE g.[distance] <> 0
    )
    SELECT
           t.[DataTran]
          ,t.[Dok]
          ,t.[Product]
          ,p.idxbox
          ,po.[Mark]
          ,po.[Model]
          ,lg.poz_La
          ,lg.poz_Lo
          ,lg.adress
          ,lg.dyst
          ,lg.Total
    FROM [ReportsA].[dbo].[Tank] t
    LEFT JOIN [ReportsA].[dbo].[G_API_CARS] p ON t.[NumberRej] = p.[nrRej]
    LEFT JOIN [ReportsA].[dbo].[POJ] po ON t.[NumberRej] = po.[Nr rej]
    LEFT JOIN LatestGAPI lg 
        ON lg.idxbox = p.idxbox 
        AND lg.dataandtime < t.[DataTran]
        AND lg.rn = 1
    WHERE t.[NumberRej] IS NOT NULL
END
GO

-- 调用存储过程
EXEC dbo.GetTankWithGAPIInfo

内容的提问来源于stack exchange，提问作者ngLucas