改写sql优化性能
1. select … FROM a, c
WHERE a.userid = c.id
and a.winRatio >= 0
order by case when leaderboardmarkid=1200000081 then 0 else 1 end, a.roiinperiod desc 如何优化这部分: case when leaderboardmarkid=1200000081 then 0 else 1 end
加上他,性能差很多。。。
2. SELECT TOP (42)
[Element1].[id] AS [id],
[Element1].[userId] AS [userId],
[Element1].[originatorUserId] AS [originatorUserId]
FROM (SELECT [Extent1].[id] AS [id], [Extent1].[createdAtUtc] AS [createdAtUtc]
FROM [dbo].[Comment] AS [Extent1]
WHERE (([Extent1].[securityId] IN (7, 144, 185, 455, 566, 574, 1095, 1361, 1417, 1464, 1483, 1640, 2070, 2629, 4691, 4754, 4836, 4838, 5615, 6679, 8686, 9148, 9161, 9240, 9260, 9272, 9288, 9311, 9393, 9456, 9481, 9503, 9515, 9539, 9616, 9649, 9656, 10130, 10361, 10370, 10375, 10376, 10398, 10437, 10464, 10477, 11690, 12256, 12340, 12375, 12772, 13002, 13483, 13536, 16548, 17083, 17361, 17396, 19489, 20801, 21350, 23640, 23708, 23744, 24121, 24398, 25076, 27888, 29451, 30939, 32375, 32391, 32463, 32689, 32696, 32840, 35026, 35208, 35602, 35797, 36007, 36071, 36073, 36396, 36461, 36493, 36575, 37163, 37175, 37302, 44209, 44655, 44801, 45675, 47229, 47431, 48130, 48620, 51405, 51763, 53127, 57103, 58153, 58454, 59582, 60109, 81354, 84501, 88983, 92273, 92299, 94714, 97359, 97456, 123118, 123326, 140279, 140733, 143787, 152011)) OR (([Extent1].[userId] IS NOT NULL) AND ([Extent1].[userId] IN (321235, 562001, 562005, 562018)))) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[TimeLineItem] AS [Extent2]
WHERE ([Extent1].[id] = [Extent2].[commentId]) AND ([Extent2].[originatorUserId] = [Extent1].[userId]) AND ([Extent2].[originatorDeletedAtUtc] IS NULL)
)) ) AS [Filter2]
OUTER APPLY (SELECT TOP (1)
[Extent3].[id] AS [id]
FROM [dbo].[TimeLineItem] AS [Extent3]
WHERE ([Extent3].[commentId] = [Filter2].[id]) AND ([Extent3].[originatorUserId] = [Extent3].[userId]) ) AS [Element1]
ORDER BY [Filter2].[createdAtUtc] DESC
其中comment 表要scan 两次,如何修改? 另外,此处out apply 如何能转换成 join? 此处没有函数,为啥会用apply