SELECT * FROM (SELECT ROW_NUMBER()
OVER(ORDER BY Name ASC) AS rownum, Name,Sex,Email,Mobile,Department,ID,FK_AssessmentActivity_ID,TestResultId FROM (SELECT dbo.AssessmentUserInfo.Name
, dbo.AssessmentUserInfo.Sex
, dbo.BeisenUser.Email
, dbo.AssessmentUserInfo.Mobile
, dbo.AssessmentUserInfo.Department
, dbo.BeisenUser.ID
, dbo.AssessmentUserTestResult.ID as ResultId
, dbo.AssessmentUserTestResult.FK_AssessmentActivity_ID
, AssessmentUserTestResult.Result.value('(/TestResult/test/endtime/text())[1]', 'DateTime' ) as TestEndTime
, dbo.AssessmentUserTestResult.ID AS TestResultId
, AssessmentUserTestResult.TestCent
, dbo.AssessmentUserTestResult.IsDelete
, dbo.AssessmentUserTestResult.FK_Tenant_ID as TenantID
FROM dbo.AssessmentUserTestResult
INNER JOIN dbo.AssessmentUserInfo ON dbo.AssessmentUserTestResult.FK_BeisenUser_ID = dbo.AssessmentUserInfo.FK_BeisenUser_ID
INNER JOIN dbo.BeisenUser ON dbo.AssessmentUserInfo.FK_BeisenUser_ID = dbo.BeisenUser.ID) AS T where EXISTS(SELECT * FROM dbo.f_SplitToInt('654518,',',') AS TBTEMP WHERE TBTEMP.a=FK_AssessmentActivity_ID) AND
(TestCent.exist ('/TestCent/test[id="F2AFE895-D78B-4B92-93C5-EF5210989D29"]') = 1)
AND IsDelete = 0 AND TenantID = 100237) AS Dwhere
WHERE rownum BETWEEN 1 and 50AssessmentUserTestResult 作答结果BeisenUser 用户邮箱,ID
AssessmentUserInfo 用户姓名,年龄。等信息
上述三张表数据都很多,线上达到了百万级别,现在很容易超时或者速度很慢,虽然分页了,各位有什么好的优化建议?
SELECT * FROM (SELECT ROW_NUMBER()
OVER(ORDER BY Name ASC) AS rownum, Name,Sex,Email,Mobile,Department,ID,FK_AssessmentActivity_ID,TestResultId FROM (SELECT dbo.AssessmentUserInfo.Name
, dbo.AssessmentUserInfo.Sex
, dbo.BeisenUser.Email
, dbo.AssessmentUserInfo.Mobile
, dbo.AssessmentUserInfo.Department
, dbo.BeisenUser.ID
, dbo.AssessmentUserTestResult.ID as ResultId
, dbo.AssessmentUserTestResult.FK_AssessmentActivity_ID
, AssessmentUserTestResult.Result.value('(/TestResult/test/endtime/text())[1]', 'DateTime' ) as TestEndTime
, dbo.AssessmentUserTestResult.ID AS TestResultId
, AssessmentUserTestResult.TestCent
, dbo.AssessmentUserTestResult.IsDelete
, dbo.AssessmentUserTestResult.FK_Tenant_ID as TenantID
FROM dbo.AssessmentUserTestResult
INNER JOIN dbo.AssessmentUserInfo ON dbo.AssessmentUserTestResult.FK_BeisenUser_ID = dbo.AssessmentUserInfo.FK_BeisenUser_ID
INNER JOIN dbo.BeisenUser ON dbo.AssessmentUserInfo.FK_BeisenUser_ID = dbo.BeisenUser.ID) AS T
inner join dbo.f_SplitToInt('654518,',',') AS TBTEMP on TBTEMP.a=FK_AssessmentActivity_ID)
AND (TestCent.exist ('/TestCent/test[id="F2AFE895-D78B-4B92-93C5-EF5210989D29"]') = 1)
AND IsDelete = 0
AND TenantID = 100237
WHERE rownum BETWEEN 1 and 50
和order by 注释了 快bu ?
AND TenantID = 100237
这个条件限制了多少数据量?这个没啥好优化的了。除了你的条件能尽可能的约束数据外。关联条件索引,以及包含索引要建立OK看计划来确定哪些耗费较大。