SQL Fetch Query执行以在本地和天蓝色的服务器上减慢速度

SELECT Users.Id,
       Users.IsActive,
       Users.Risk,
       Users.Name,
       Users.EmployeeID,
       Users.OrganizationId,
       Users.Username,
       Users.IsUser,
       Users.WFID,
       Organizations.Name AS orgName,
       (SELECT MAX(UserTestGroups.ModifiedOn)
        FROM UsersInSchedules
        LEFT JOIN UserTestGroups ON UsersInSchedules.Id = UserTestGroups.UserInScheduleID
        WHERE Users.Id = UsersInSchedules.UserID) AS LastTest
FROM Users
LEFT JOIN Organizations ON Users.OrganizationID = Organizations.Id
WHERE Users.isUser = '0'
      AND Users.IsDeleted = '0'
ORDER BY Users.Name ASC
OFFSET 15 ROWS FETCH NEXT 15 ROWS ONLY

Above query is working too slowly, taking almost 7 seconds to respond from server.

以上查询工作太慢，从服务器响应大约需要7秒钟。

I am not understanding why that is happening - what is wrong with the query?

我不明白为什么会发生这种情况 - 查询有什么问题？

Here is the execution plan for query:

以下是查询的执行计划：

1 个解决方案

#1

The nested SELECT could be the reason for low performance of your query. The nested SELECT must be processed for each result of your outer query. I think a join approach could yield better result. You could try

嵌套的SELECT可能是查询性能低下的原因。必须为外部查询的每个结果处理嵌套的SELECT。我认为连接方法可以产生更好的结果。你可以试试

SELECT Users.Id,
       Users.IsActive,
       Users.Risk,
       Users.Name,
       Users.EmployeeID,
       Users.OrganizationId,
       Users.Username,
       Users.IsUser,
       Users.WFID,
       Organizations.Name AS orgName,
       UserTest.LastTest
FROM Users
    LEFT JOIN Organizations ON Users.OrganizationID = Organizations.Id
    LEFT JOIN 
        (
        SELECT UsersInSchedules.UserID, MAX(UserTestGroups.ModifiedOn) AS LastTest
        FROM UsersInSchedules
        LEFT JOIN UserTestGroups ON UsersInSchedules.Id = UserTestGroups.UserInScheduleID
        GROUP BY UsersInSchedules.UserID
        ) UserTest ON Users.Id = UserTest.UserID
WHERE Users.isUser = '0' AND Users.IsDeleted = '0'
ORDER BY Users.Name ASC
OFFSET 15 ROWS FETCH NEXT 15 ROWS ONLY