How to convert a query and not call the subquery several times?

Question

I have this query:

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

I don't want to call the subquery several times. What is the best way to convert the query? I am thinking about creating a temporary table and insert the columns: poz_La, poz_Lo, adress, dist, Total from subquery:

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
           ([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)

Is this the right way? How to call a function or other procedure that will refer to the main procedure?

Answer 1

Move the query to the FROM, and then you can use a "Top 1 for each group" there. This method uses APPLY to be able to use a correlated subquery and returns all the columns form your table G_API_DATA:

SELECT t.[DataTran],
       t.[Dok],
       t.[Product],
       p.idxbox,
       po.[Mark],
       po.[Model],
       D.[poz_La],
       D.[poz_Lo],
       D.[adress],
       D.[dyst],
       D.Total
FROM [ReportsA].[dbo].[Tank] t
    LEFT JOIN [ReportsA].[dbo].[G_API_CARS] p ON t.[NumberRej] = p.[nrRej] --"p" is for "cars"?
    LEFT JOIN [ReportsA].[dbo].[POJ] po ON t.[NumberRej] = po.[Nr rej]
    --OUTER APPLY as unsure if this should be a LEFT JOIN or not
    OUTER APPLY (SELECT TOP (1)
                        GAD.[poz_La],
                        GAD.[poz_Lo],
                        GAD.[adress],
                        GAD.[dyst],
                        GAD.Total
                 FROM [RaportyAutko].[dbo].[G_API_Data] GAD
                 WHERE GAD.idxbox = p.idxbox
                   AND GAD.[dataandtime] < t.[DataTran] --This should be the same, and will result in a SARGable clause
                   --AND DATEDIFF(SECOND, { D '1970-01-01' }, GAD.[dataandtime]) < DATEDIFF(SECOND, { D '1970-01-01' }, t.[DataTran]) --This isn't SARgable, so may perform poorly
                   AND GAD.[distance] <> 0
                 ORDER BY GAD.dataandtime DESC) D
WHERE t.[NumberRej] IS NOT NULL;