【性能优化】SQL视图

peng2739956 2018-06-26 03:25:27
同志们,还是这个SQL,吗的160W数据量。 30多秒。太要命了。 用gameid查单人370条数据 要11秒。
...全文
215 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
吉普赛的歌 2018-06-26
引用 10 楼 peng2739956 的回复:
这个SQL 是整个项目 最大的一块SQL了。 涉及的东西特别多。 多余的一些整合我都在网站上弄了。 现在商务那块能接受这个速度了。


那就行, 没事就结贴吧
回复
peng2739956 2018-06-26
这个SQL 是整个项目 最大的一块SQL了。 涉及的东西特别多。 多余的一些整合我都在网站上弄了。 现在商务那块能接受这个速度了。
回复
吉普赛的歌 2018-06-26
索引方面, 你再仔细研究下, 应该还可以提高。
比如: create index index_name on tableName(columnName1, columnName2... ) include (c1, c2...)
的 include 有什么?

where 中的其它条件你也可以包含到索引中去。

这些做好了, 索引的效率还可以提高一点。
不过, 最终的结果集太大的话, 没办法提高多少。
回复
peng2739956 2018-06-26
引用 7 楼 yenange 的回复:
[quote=引用 6 楼 peng2739956 的回复:]
感谢。顺便问下,能否提供点 如何优化效率的资料看下。


随便搜索一下“SQL优化”就大把了, https://blog.csdn.net/jie_liang/article/details/77340905
不过, 关键还是看执行计划。另外, 看了优化的建议, 你不一定记得住, 记住了不一定相信, 相信了不一定 100% 就正确,这些都是实战中提炼出的, 最好是勤动手, 多实践。

现在多少秒出数据?[/quote]
现在限制ID<=100的情况下 在2秒左右。 查所有的话 40W数据 16秒。
回复
吉普赛的歌 2018-06-26
引用 6 楼 peng2739956 的回复:
感谢。顺便问下,能否提供点 如何优化效率的资料看下。


随便搜索一下“SQL优化”就大把了, https://blog.csdn.net/jie_liang/article/details/77340905
不过, 关键还是看执行计划。另外, 看了优化的建议, 你不一定记得住, 记住了不一定相信, 相信了不一定 100% 就正确,这些都是实战中提炼出的, 最好是勤动手, 多实践。

现在多少秒出数据?
回复
peng2739956 2018-06-26
引用 5 楼 yenange 的回复:
--1. 每个有 InsertTime 的表建立索引:
CREATE INDEX ix_RecordDrawInfo_InsertTime ON [dbo].RecordDrawInfo(InsertTime)
CREATE INDEX ix_RecordInsure_InsertTime ON [dbo].RecordInsure(InsertTime)
CREATE INDEX ix_RecordGoldBackSum_InsertTime ON [dbo].RecordGoldBackSum(InsertTime)
--2. 连接字段建立索引,这个你自己去完成。列出来的字段你可以 INCLUDE .
--3. 查询把 where 放在每个子项里,提前过滤,不要放外面。
--注意:要用 DATEADD 不要用 DATEDIFF, 比较时左边保持字段纯净无任何包装!
--4. 其它的你自己根据需要可以加或者弄成复合索引
SELECT row_number() OVER (ORDER BY InsertTime DESC) ID, *
FROM (
SELECT DrawID, KindID, ServerID, TableID, UserCount, AndroidCount, Waste, Revenue, UserMedal, StartTime, ConcludeTime, InsertTime, KindName, ServerName,
Score, BackScore, BackInsureScore, gameid, accounts, nickname, lastlogonip, LastLogonType, userID
FROM (SELECT A.DrawID, A.KindID, A.ServerID, A.TableID, A.UserCount, A.AndroidCount, A.Waste, A.Revenue, A.UserMedal, A.StartTime, A.ConcludeTime,
A.InsertTime, B.KindName, C.ServerName, D .Score AS Score, d .BackScore AS BackScore, D .BackInsureScore, E.gameid, E.accounts,
E.nickname, E.lastlogonip, E.LastLogonType, E.userID
FROM [QPTreasureDB].[dbo].RecordDrawInfo AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID JOIN
QPTreasureDB.dbo.RecordDrawScore AS D ON A.DrawID = D .DrawID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON D .UserID = E.userID) AS tab
WHERE Score > 0 AND InsertTime>= DateADD(dd, -7, getdate()) --Score!=0 如果没有负数改成 >0
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], a.[TradeType] AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime,
CASE a.[TradeType] WHEN 1 THEN '存款:' + CAST(a.swapscore AS nvarchar(200)) WHEN 2 THEN '取款:' + CAST(a.swapscore AS nvarchar(200))
WHEN 4 THEN '充值金币:' + CAST(a.swapscore AS nvarchar(200)) WHEN 6 THEN '邮件领取返利:' + CAST(a.swapscore AS nvarchar(200))
ELSE '转账:' + '收款人昵称:' + cast(e.NickName AS nvarchar(200)) + ';收款人游戏ID:' + cast(e.gameid AS nvarchar(200))
+ ';转账金额:' + CAST(a.swapscore AS nvarchar(200)) END AS KindName, '''' AS ServerName, a.swapscore AS Score,
CASE a.[TradeType] WHEN 1 THEN (a.[SourceGold] - a.swapscore) WHEN 2 THEN (a.[SourceGold] + a.swapscore) ELSE a.[SourceGold] END AS BackScore,
CASE WHEN (a.[TradeType] = 1 OR
a.[TradeType] = 4 OR
a.TradeType = 6)
THEN a.SourceBank + a.swapscore WHEN a.[TradeType] = 2 THEN a.SourceBank - a.swapscore WHEN a.[TradeType] = 3 THEN a.SourceBank - a.swapscore
ELSE a.SourceBank END AS BackInsureScore, D .GameID AS gameid, D .Accounts AS accounts, D .NickName AS nockname, a.[ClientIP] AS lastloginip,
d .LastLogonType AS LastLogonType, a.[SourceUserID] AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], 4 AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime, '转入:' + '转出人昵称:' + cast(D .NickName AS nvarchar(200))
+ ';转出游戏ID:' + cast(D .gameid AS nvarchar(200)) + ';转入金额:' + CAST(a.swapscore AS nvarchar(200)) AS KindName, '''' AS ServerName,
a.SwapScore AS Score, a.TargetGold AS BackScore, a.TargetBank + a.swapscore AS BackInsureScore, E.GameID AS gameid, E.Accounts AS accounts,
E.NickName AS nockname, a.[ClientIP] AS lastloginip, E.LastLogonType AS LastLogonType, a.TargetUserID AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
WHERE a.TradeType = 3 AND InsertTime>= DateADD(dd, -7, getdate())
UNION ALL
SELECT A.RecordID AS DrawID, 0 AS [KindID], 0 AS [ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, 0 AS [Revenue], 5 AS UserMedal,
A.CollectDate AS CollectDate, A.CollectDate AS ConcludeTime, A.CollectDate AS InsertTime, '提取返点:' + CAST(a.BackGold AS nvarchar(200))
AS KindName, '''' AS ServerName, a.BackGold AS Score, a.SourceScore AS BackScore, a.SourceInsureScore AS BackInsureScore, b.gameid AS GameID,
b.Accounts AS accounts, b.NickName AS nockname, a.ClientIP AS lastloginip, b.LastLogonType AS LastLogonType, b.userid AS userID
FROM dbo.RecordGoldBackSum AS A LEFT JOIN
QPAccountsDB.DBO.AccountsInfo AS B ON A.SourceGameID = B.GameID
WHERE a.IsTake = 1 AND InsertTime>= DateADD(dd, -7, getdate())
) AS tab

感谢。顺便问下,能否提供点 如何优化效率的资料看下。
回复
吉普赛的歌 2018-06-26
--1. 每个有 InsertTime 的表建立索引:
CREATE INDEX ix_RecordDrawInfo_InsertTime ON [dbo].RecordDrawInfo(InsertTime)
CREATE INDEX ix_RecordInsure_InsertTime ON [dbo].RecordInsure(InsertTime)
CREATE INDEX ix_RecordGoldBackSum_InsertTime ON [dbo].RecordGoldBackSum(InsertTime)
--2. 连接字段建立索引,这个你自己去完成。列出来的字段你可以 INCLUDE .
--3. 查询把 where 放在每个子项里,提前过滤,不要放外面。
--注意:要用 DATEADD 不要用 DATEDIFF, 比较时左边保持字段纯净无任何包装!
--4. 其它的你自己根据需要可以加或者弄成复合索引
SELECT row_number() OVER (ORDER BY InsertTime DESC) ID, *
FROM (
SELECT DrawID, KindID, ServerID, TableID, UserCount, AndroidCount, Waste, Revenue, UserMedal, StartTime, ConcludeTime, InsertTime, KindName, ServerName,
Score, BackScore, BackInsureScore, gameid, accounts, nickname, lastlogonip, LastLogonType, userID
FROM (SELECT A.DrawID, A.KindID, A.ServerID, A.TableID, A.UserCount, A.AndroidCount, A.Waste, A.Revenue, A.UserMedal, A.StartTime, A.ConcludeTime,
A.InsertTime, B.KindName, C.ServerName, D .Score AS Score, d .BackScore AS BackScore, D .BackInsureScore, E.gameid, E.accounts,
E.nickname, E.lastlogonip, E.LastLogonType, E.userID
FROM [QPTreasureDB].[dbo].RecordDrawInfo AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID JOIN
QPTreasureDB.dbo.RecordDrawScore AS D ON A.DrawID = D .DrawID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON D .UserID = E.userID) AS tab
WHERE Score > 0 AND InsertTime>= DateADD(dd, -7, getdate()) --Score!=0 如果没有负数改成 >0
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], a.[TradeType] AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime,
CASE a.[TradeType] WHEN 1 THEN '存款:' + CAST(a.swapscore AS nvarchar(200)) WHEN 2 THEN '取款:' + CAST(a.swapscore AS nvarchar(200))
WHEN 4 THEN '充值金币:' + CAST(a.swapscore AS nvarchar(200)) WHEN 6 THEN '邮件领取返利:' + CAST(a.swapscore AS nvarchar(200))
ELSE '转账:' + '收款人昵称:' + cast(e.NickName AS nvarchar(200)) + ';收款人游戏ID:' + cast(e.gameid AS nvarchar(200))
+ ';转账金额:' + CAST(a.swapscore AS nvarchar(200)) END AS KindName, '''' AS ServerName, a.swapscore AS Score,
CASE a.[TradeType] WHEN 1 THEN (a.[SourceGold] - a.swapscore) WHEN 2 THEN (a.[SourceGold] + a.swapscore) ELSE a.[SourceGold] END AS BackScore,
CASE WHEN (a.[TradeType] = 1 OR
a.[TradeType] = 4 OR
a.TradeType = 6)
THEN a.SourceBank + a.swapscore WHEN a.[TradeType] = 2 THEN a.SourceBank - a.swapscore WHEN a.[TradeType] = 3 THEN a.SourceBank - a.swapscore
ELSE a.SourceBank END AS BackInsureScore, D .GameID AS gameid, D .Accounts AS accounts, D .NickName AS nockname, a.[ClientIP] AS lastloginip,
d .LastLogonType AS LastLogonType, a.[SourceUserID] AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], 4 AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime, '转入:' + '转出人昵称:' + cast(D .NickName AS nvarchar(200))
+ ';转出游戏ID:' + cast(D .gameid AS nvarchar(200)) + ';转入金额:' + CAST(a.swapscore AS nvarchar(200)) AS KindName, '''' AS ServerName,
a.SwapScore AS Score, a.TargetGold AS BackScore, a.TargetBank + a.swapscore AS BackInsureScore, E.GameID AS gameid, E.Accounts AS accounts,
E.NickName AS nockname, a.[ClientIP] AS lastloginip, E.LastLogonType AS LastLogonType, a.TargetUserID AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
WHERE a.TradeType = 3 AND InsertTime>= DateADD(dd, -7, getdate())
UNION ALL
SELECT A.RecordID AS DrawID, 0 AS [KindID], 0 AS [ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, 0 AS [Revenue], 5 AS UserMedal,
A.CollectDate AS CollectDate, A.CollectDate AS ConcludeTime, A.CollectDate AS InsertTime, '提取返点:' + CAST(a.BackGold AS nvarchar(200))
AS KindName, '''' AS ServerName, a.BackGold AS Score, a.SourceScore AS BackScore, a.SourceInsureScore AS BackInsureScore, b.gameid AS GameID,
b.Accounts AS accounts, b.NickName AS nockname, a.ClientIP AS lastloginip, b.LastLogonType AS LastLogonType, b.userid AS userID
FROM dbo.RecordGoldBackSum AS A LEFT JOIN
QPAccountsDB.DBO.AccountsInfo AS B ON A.SourceGameID = B.GameID
WHERE a.IsTake = 1 AND InsertTime>= DateADD(dd, -7, getdate())
) AS tab
回复
吉普赛的歌 2018-06-26
SELECT    *
FROM (SELECT row_number() OVER (ORDER BY InsertTime DESC) ID, *
FROM (SELECT DrawID, KindID, ServerID, TableID, UserCount, AndroidCount, Waste, Revenue, UserMedal, StartTime, ConcludeTime, InsertTime, KindName, ServerName,
Score, BackScore, BackInsureScore, gameid, accounts, nickname, lastlogonip, LastLogonType, userID
FROM (SELECT A.DrawID, A.KindID, A.ServerID, A.TableID, A.UserCount, A.AndroidCount, A.Waste, A.Revenue, A.UserMedal, A.StartTime, A.ConcludeTime,
A.InsertTime, B.KindName, C.ServerName, D .Score AS Score, d .BackScore AS BackScore, D .BackInsureScore, E.gameid, E.accounts,
E.nickname, E.lastlogonip, E.LastLogonType, E.userID
FROM [QPTreasureDB].[dbo].RecordDrawInfo AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID JOIN
QPTreasureDB.dbo.RecordDrawScore AS D ON A.DrawID = D .DrawID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON D .UserID = E.userID) AS tab
WHERE Score != 0
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], a.[TradeType] AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime,
CASE a.[TradeType] WHEN 1 THEN '存款:' + CAST(a.swapscore AS nvarchar(200)) WHEN 2 THEN '取款:' + CAST(a.swapscore AS nvarchar(200))
WHEN 4 THEN '充值金币:' + CAST(a.swapscore AS nvarchar(200)) WHEN 6 THEN '邮件领取返利:' + CAST(a.swapscore AS nvarchar(200))
ELSE '转账:' + '收款人昵称:' + cast(e.NickName AS nvarchar(200)) + ';收款人游戏ID:' + cast(e.gameid AS nvarchar(200))
+ ';转账金额:' + CAST(a.swapscore AS nvarchar(200)) END AS KindName, '''' AS ServerName, a.swapscore AS Score,
CASE a.[TradeType] WHEN 1 THEN (a.[SourceGold] - a.swapscore) WHEN 2 THEN (a.[SourceGold] + a.swapscore) ELSE a.[SourceGold] END AS BackScore,
CASE WHEN (a.[TradeType] = 1 OR
a.[TradeType] = 4 OR
a.TradeType = 6)
THEN a.SourceBank + a.swapscore WHEN a.[TradeType] = 2 THEN a.SourceBank - a.swapscore WHEN a.[TradeType] = 3 THEN a.SourceBank - a.swapscore
ELSE a.SourceBank END AS BackInsureScore, D .GameID AS gameid, D .Accounts AS accounts, D .NickName AS nockname, a.[ClientIP] AS lastloginip,
d .LastLogonType AS LastLogonType, a.[SourceUserID] AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], 4 AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime, '转入:' + '转出人昵称:' + cast(D .NickName AS nvarchar(200))
+ ';转出游戏ID:' + cast(D .gameid AS nvarchar(200)) + ';转入金额:' + CAST(a.swapscore AS nvarchar(200)) AS KindName, '''' AS ServerName,
a.SwapScore AS Score, a.TargetGold AS BackScore, a.TargetBank + a.swapscore AS BackInsureScore, E.GameID AS gameid, E.Accounts AS accounts,
E.NickName AS nockname, a.[ClientIP] AS lastloginip, E.LastLogonType AS LastLogonType, a.TargetUserID AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
WHERE a.TradeType = 3
UNION ALL
SELECT A.RecordID AS DrawID, 0 AS [KindID], 0 AS [ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, 0 AS [Revenue], 5 AS UserMedal,
A.CollectDate AS CollectDate, A.CollectDate AS ConcludeTime, A.CollectDate AS InsertTime, '提取返点:' + CAST(a.BackGold AS nvarchar(200))
AS KindName, '''' AS ServerName, a.BackGold AS Score, a.SourceScore AS BackScore, a.SourceInsureScore AS BackInsureScore, b.gameid AS GameID,
b.Accounts AS accounts, b.NickName AS nockname, a.ClientIP AS lastloginip, b.LastLogonType AS LastLogonType, b.userid AS userID
FROM dbo.RecordGoldBackSum AS A LEFT JOIN
QPAccountsDB.DBO.AccountsInfo AS B ON A.SourceGameID = B.GameID
WHERE a.IsTake = 1) AS tab
WHERE DateDiff(dd, InsertTime, getdate()) <= 7) AS two


刚才向管理员反应了字数限制的问题, 现在改大到了 1 万。
先帮你贴上来吧。
回复
peng2739956 2018-06-26
大佬凑合下看。怎么优化下。
回复
peng2739956 2018-06-26
吗的,为何不能贴SQL了。 还TM 限制字数?
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2018-06-26 03:25
社区公告
暂无公告