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
0
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
#1
0
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