优化SQL查询sql

Question

我还需要帮助加速这个查询，需要25分钟的实时数据库，1秒的测试数据库。

这是在一些修改之后，最初我使用while循环向db服务器查询数百次，以从php页面获取主查询的每一行的数据，然后我尝试使用临时表创建一个过程来返回数据，45分钟后我取消了执行。所以我试了一下。

我想象一下，可以对"select TOP1FROM NurQueryResults“查询执行内连接，但我搞不清楚。我得到了结果，但不是最新的结果，我做了一个"order by t1.time，t2.time，t3.time...“。它确实在顶部有t1最近的结果，并且它返回了比它应该有的更多的结果。

SELECT o.VisitID                                          AS VisitID,
       (SELECT TOP 1 Response
        FROM   NurQueryResults
        WHERE  QueryID = 'OEDTCAT'
               AND VisitID = o.VisitID
        ORDER  BY DateTime DESC)                          AS PPN,
       (SELECT TOP 1 Response
        FROM   NurQueryResults
        WHERE  QueryID = 'OEDTMEAT'
               AND VisitID = o.VisitID
        ORDER  BY DateTime DESC)                          AS MEAT,
       (SELECT TOP 1 Response
        FROM   OeOrderQueries
        WHERE  QueryID = 'OESPMOD'
               AND VisitID = o.VisitID
        ORDER  BY RowUpdateDateTime DESC)                 AS SPMOD,
       (SELECT TOP 1 Response
        FROM   OeOrderQueries
        WHERE  QueryID = 'OERT3'
               AND VisitID = o.VisitID
        ORDER  BY RowUpdateDateTime DESC)                 AS SPMOD2,
       (SELECT TOP 1 Response
        FROM   NurQueryResults
        WHERE  QueryID = 'OEDTDECUB'
               AND VisitID = o.VisitID
        ORDER  BY DateTime DESC)                          AS DECUB,
       (SELECT TOP 1 Response
        FROM   NurQueryResults
        WHERE  QueryID = 'OEALL2'
               AND VisitID = o.VisitID
        ORDER  BY DateTime DESC)                          AS FOODALL,
       o.OrderedProcedureName,
       o.OrderDateTime,
       a.RoomID,
       a.BedID,
       a.Name,
       a.Sex,
       DATEDIFF(year, a.ComputedBirthDateTime, GETDATE()) AS Age
FROM   OeOrders o
       INNER JOIN AdmVisits a
         ON o.VisitID = a.VisitID
            AND o.Category = 'DIET'
            AND o.StatusChoice = 'S'
            AND a.Status = 'ADM IN'
ORDER  BY o.VisitID,
          o.OrderDateTime DESC 

Answer 1

我只是想知道，当你这样做的时候，结果会是怎样的：

SELECT
    o.VisitID AS VisitID,
    PPN.Response
FROM OeOrders o
JOIN AdmVisits a
    ON o.VisitID = a.VisitID
    AND o.Category = 'DIET'
    AND o.StatusChoice = 'S'
    AND a.Status = 'ADM IN'
join
(
    SELECT 
        Response, 
        VisitID, 
        ROW_NUMBER() OVER(ORDER BY DateTime DESC) AS MyRowNumber 
    FROM NurQueryResults 
    WHERE QueryID = 'OEDTCAT'
) AS PPN
    on o.VisitID = PPN.VisitID
    and PPN.MyRowNumber = 1

这不是您的整个查询，而只是获得列PPN的第一个子查询。

如果像这样更改所有子查询，是否会有任何性能提升？

Answer 2

打开Execution Plan并查看结果。它可能表明您缺少一个索引或类似的东西。说到索引，请检查所涉及的索引的碎片，如果碎片较高，请考虑重建它们。我最近获得了相当多的成功，通过遵循上面的过程，在生产上运行需要2分钟的程序，在1-3秒内执行。