问题描述:
很多同事反馈客户端登录很慢,很卡,查看后台消息记录,有20W条消息记录,这个很影响登录速度,所以后台要清理一下。
处理方法:
1、清理后台消息记录:
使用的数据库是【UFSystem】
select *from UA_Message select *into UA_Message_20210630 from UA_Message ----备份消息审批记录 delete UA_Message where datediff(day,dSend,getdate()) >15 ----保留近15天的消息记录
2、优化一下数据库索引
DECLARE @SQL nvarchar(1000) DECLARE @TableName nvarchar(100) if EXISTS(Select 1 Where @@version Like 'Microsoft SQL Server 2000 %') DECLARE crTmp CURSOR OPTIMISTIC FOR SELECT [NAME] FROM sysobjects WHERE [xtype] = 'U' Order By [NAME] ELSE DECLARE crTmp CURSOR OPTIMISTIC FOR SELECT [NAME] FROM sys.tables WHERE [type] = 'U' Order By [NAME] OPEN crTmp FETCH NEXT FROM crTmp INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN Print ' ' Print '=========================================================================' Print '当前表:' + @TableName SET @SQL = 'DBCC DBREINDEX([' + @TableName + '])' EXECUTE (@SQL) SET @SQL = 'UPDATE STATISTICS [' + @TableName + ']' EXECUTE (@SQL) Print ' ' Print @TableName + ' 执行完毕' Print '=========================================================================' FETCH NEXT FROM crTmp INTO @TableName END CLOSE crTmp DEALLOCATE crTmp GO