提高SQL查询性能

Question

我有以下SQL查询：

SELECT * FROM (
      SELECT dbo.Table1.Table1_ID, dbo.Table1.Name, dbo.Table5.Ergebniss, 
      dbo.Table2.Datum, dbo.Table3.Stelle, dbo.Table2.Nummer 
    FROM dbo.Table1 
    INNER JOIN dbo.Table2 ON dbo.Table1.Table1_ID = dbo.Table2.Table1_ID 
    INNER JOIN dbo.Table3 ON dbo.Table2.Table2_ID = dbo.Table3.Table2_ID 
    INNER JOIN dbo.Table4 ON dbo.Table3.Table3_ID = dbo.Table4.Table3_ID 
    INNER JOIN dbo.Table5 ON dbo.Table4.Table4_ID = dbo.Table5.Table4_ID
    GROUP BY dbo.Table1.Table1_ID, dbo.Table1.Name, dbo.Table5.Wert2, 
    dbo.Table5.Wert1, dbo.Table5.Ergebniss, dbo.Table2.Datum, 
    dbo.Table2.Nummer, dbo.Table3.Stelle
       HAVING (dbo.Table1.Table1_ID = 1) 
         AND (dbo.Table2.Nummer = (SELECT MAX(dbo.Table2.Nummer) AS 
   MaxSchichtNummer 
 FROM dbo.Table1 
 INNER JOIN dbo.Table2 ON dbo.Table1.Table1_ID = dbo.Table2.Table1_ID 
 WHERE (dbo.Table1.Table1_ID = 1))) ) AS TG 
 where tg.Nummer 
   in (Select top 5000 Nummer from dbo.Table2 where Table1_ID=1 order by datum 
 desc) order by Datum desc

这需要很长时间，因此请选择数据。有没有可能加快查询速度？

Answer 1

考虑使用EXISTS和WHERE语句。与IN相比，EXISTS的性能更好。WHERE语句在GROUP BY之前执行，因此我们有更好的性能。此外，请尝试将WHERE语句移到GROUP BY之前，因为这样可以过滤行，这样可以过滤更少的行。

SELECT *
FROM
(
    SELECT dbo.Table1.Table1_ID,
           dbo.Table1.Name,
           dbo.Table5.Ergebniss,
           dbo.Table2.Datum,
           dbo.Table3.Stelle,
           dbo.Table2.Nummer
    FROM dbo.Table1
        INNER JOIN dbo.Table2
            ON dbo.Table1.Table1_ID = dbo.Table2.Table1_ID
        INNER JOIN dbo.Table3
            ON dbo.Table2.Table2_ID = dbo.Table3.Table2_ID
        INNER JOIN dbo.Table4
            ON dbo.Table3.Table3_ID = dbo.Table4.Table3_ID
        INNER JOIN dbo.Table5
            ON dbo.Table4.Table4_ID = dbo.Table5.Table4_ID
    WHERE (dbo.Table1.Table1_ID = 1)
           AND (dbo.Table2.Nummer =
               (
                   SELECT MAX(dbo.Table2.Nummer) AS MaxSchichtNummer
                   FROM dbo.Table1
                       INNER JOIN dbo.Table2
                           ON dbo.Table1.Table1_ID = dbo.Table2.Table1_ID
                   WHERE (dbo.Table1.Table1_ID = 1)
               )
               )
            and exists                 
            (
                SELECT TOP 5000
                Nummer
                FROM dbo.Table2
                WHERE Table1_ID = 1
                AND nummer = dbo.Table2.Nummer 
            )
    GROUP BY dbo.Table1.Table1_ID,
             dbo.Table1.NAME,
             dbo.Table5.Wert2,
             dbo.Table5.Wert1,
             dbo.Table5.Ergebniss,
             dbo.Table2.Datum,
             dbo.Table2.Nummer,
             dbo.Table3.Stelle

) AS TG
ORDER BY Datum DESC;

Answer 2

请尝试此查询，

SELECT * FROM 
(
        SELECT Table1.Table1_ID, Table1.Name, Table5.Ergebniss, Table2.Datum, Table3.Stelle, Table2.Nummer 
        FROM Table1 
        INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table1_ID 
        INNER JOIN Table3 ON Table2.Table2_ID = Table3.Table2_ID 
        INNER JOIN Table4 ON Table3.Table3_ID = Table4.Table3_ID 
        INNER JOIN Table5 ON Table4.Table4_ID = Table5.Table4_ID
        WHERE Table1.Table1_ID = 1
        GROUP BY Table1.Table1_ID, Table1.Name, Table5.Wert2, Table5.Wert1, Table5.Ergebniss, Table2.Datum, Table2.Nummer, Table3.Stelle
        HAVING (Table2.Nummer = (SELECT MAX(Table2.Nummer) AS MaxSchichtNummer 
                                    FROM Table1 
                                    INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table1_ID 
                                    WHERE Table1.Table1_ID = 1)

)tg
where tg.Nummer 
  in (Select top 5000 Nummer from dbo.Table2 where Table1_ID = 1 order by datum desc) 
order by Datum desc