MS-SQL Server
改写sql优化性能
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区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
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
...全文
相关推荐
智能SQL优化与改写方案.pptx
总的来说，智能SQL优化与改写是数据库管理的重要组成部分，通过利用先进的算法和工具，可以有效地解决查询优化的痛点和难点，提升系统的整体性能。
【数据库优化】达梦SQL性能调优关键技术：银行业T+0高频流水查询的分区裁剪与语句改写实践
内容概要：本文深入探讨了达梦数据库在银行业T+0高频流水查询场景下的SQL优化实战技巧，聚焦于如何通过分区策略、SQL改写与执行计划调优来显著提升查询性能。文章以手机银行实时交易明细查询为例，揭示了传
罗炳森-SQL等价改写核心思想
罗炳森，一位在数据库设计与开发、性能优化和SQL优化方面有着丰富经验的专业人士，强调了SQL等价改写在优化过程中的重要性。
MySQL 性能优化知识站源码 HTML+CSS+JavaScript+ECharts B+树与慢SQL改写（离线可用）
基于 HTML5+CSS3+原生 JavaScript 与本地化 ECharts 的 MySQL 性能优化交互式知识站：性能优化金字塔、B+ 树查找路径动画、最左前缀六场景试错、EXPLAIN 逐字段
小米智能SQL优化与改写工具开源.pdf
小米智能SQL优化与改写工具开源是一个由小米公司推出的工具，其名称为SOAR，主要用于SQL查询的性能优化和改写，它具有如下特点和功能：1.
