首页 > 代码库 > 优化陕西重汽通话记录心得
优化陕西重汽通话记录心得
1 作业维护
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[BasicCallDB].[dbo].[shx_BasicCallDB]‘) AND type in (N‘U‘))truncate TABLE [BasicCallDB].[dbo].[shx_BasicCallDB]drop TABLE [BasicCallDB].[dbo].[shx_BasicCallDB]GOSELECT [PhoneCode] ,[AppId] ,[BeginDate] ,[EndDate] ,[ServiceBeginDate] ,[ServiceEndDate] ,[TalkDur] ,[CallLength] ,[UserSN] ,[ExtNO] ,[InOutSign] ,[AutoSign] ,[CallResult] ,[FileName] ,[ChainIndex] ,[ServiceEvaluation] ,[CPT] into [BasicCallDB].[dbo].[shx_BasicCallDB] FROM [BasicCallDB].[dbo].[v_BasicCallDB] where (DATEDIFF(dd, BeginDate, GETDATE()) > 0) order by BeginDate descIF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N‘[BasicCallDB].[dbo].[shx_BasicCallDB]‘) AND name = N‘shx_index‘) drop INDEX [shx_index] ON [BasicCallDB].[dbo].[shx_BasicCallDB] WITH ( ONLINE = OFF )GOCREATE NONCLUSTERED INDEX [shx_index] ON [BasicCallDB].[dbo].[shx_BasicCallDB] ( [PhoneCode] ASC, [BeginDate] DESC, [EndDate] DESC, [ServiceBeginDate] ASC, [ServiceEndDate] ASC, [TalkDur] ASC, [CallLength] ASC, [UserSN] ASC, [InOutSign] ASC, [AutoSign] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO
2 视图修改
优化为当天的通话记录
USE [BasicCallDB]GOIF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N‘[dbo].[v_shx_AGENTLOG1]‘))DROP VIEW [dbo].[v_shx_AGENTLOG1]GOCREATE VIEW [dbo].[v_shx_AGENTLOG1]ASSELECT COID, EmployeeID, StartTime, TimeLengthFROM dbo.AGENTLOG1WHERE (DATEDIFF(dd, StartTime, GETDATE()) = 0)GOIF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N‘[dbo].[v_shx_AGENTRECFILE]‘))DROP VIEW [dbo].[v_shx_AGENTRECFILE]GOCREATE VIEW [dbo].[v_shx_AGENTRECFILE]ASSELECT COID, ServiceEvaluation, FileName, ExtensionNumber, CreateTimeFROM dbo.AGENTRECFILEWHERE (DATEDIFF(dd, CreateTime, GETDATE()) = 0)GOIF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N‘[dbo].[v_shx_COLOG]‘))DROP VIEW [dbo].[v_shx_COLOG]GOCREATE VIEW [dbo].[v_shx_COLOG]ASSELECT COID, CallerNumber, COID AS Expr1, CreateTime, ReleaseTime, ReleaseTime AS Expr2, TimeLength, OUTCALLERID, CallType, TransResult, CallResult, CalledNumberFROM dbo.COLOGWHERE (DATEDIFF(dd, CreateTime, GETDATE()) = 0)GOIF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N‘[dbo].[v_shx_BasicCallDB]‘))DROP VIEW [dbo].[v_shx_BasicCallDB]GOCREATE VIEW [dbo].[v_shx_BasicCallDB]ASSELECT COALESCE (NULLIF (dbo.v_shx_COLOG.CallerNumber, ‘‘), dbo.v_shx_COLOG.CalledNumber) AS PhoneCode, dbo.v_shx_COLOG.COID AS AppId, dbo.v_shx_COLOG.CreateTime AS BeginDate, dbo.v_shx_COLOG.ReleaseTime AS EndDate, dbo.v_shx_COLOG.ReleaseTime AS ServiceEndDate, dbo.v_shx_COLOG.TimeLength AS TalkDur, (CASE v_shx_AGENTLOG1.EmployeeID WHEN NULL THEN v_shx_COLOG.OUTCALLERID ELSE v_shx_AGENTLOG1.EmployeeID END) AS UserSN, dbo.v_shx_COLOG.CallType AS InOutSign, dbo.v_shx_COLOG.TransResult AS AutoSign, dbo.v_shx_COLOG.CallResult, dbo.v_shx_AGENTLOG1.StartTime AS ServiceBeginDate, dbo.v_shx_AGENTLOG1.TimeLength AS CallLength, - 1 AS ChainIndex, dbo.v_shx_AGENTRECFILE.ExtensionNumber AS ExtNO, dbo.v_shx_AGENTRECFILE.FileName, dbo.v_shx_AGENTRECFILE.ServiceEvaluation, ‘AGT‘ AS CPTFROM dbo.v_shx_COLOG LEFT OUTER JOIN dbo.v_shx_AGENTRECFILE ON dbo.v_shx_COLOG.COID = dbo.v_shx_AGENTRECFILE.COID LEFT OUTER JOIN dbo.v_shx_AGENTLOG1 ON dbo.v_shx_AGENTLOG1.COID = dbo.v_shx_COLOG.COIDGOIF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N‘[dbo].[v_BasicCallDB_New]‘))DROP VIEW [dbo].[v_BasicCallDB_New]GOCREATE VIEW [dbo].[v_BasicCallDB_New]ASSELECT PhoneCode, AppId, BeginDate, EndDate, ServiceBeginDate, ServiceEndDate, TalkDur, CallLength, UserSN, ExtNO, InOutSign, AutoSign, CallResult, FileName, ChainIndex, ServiceEvaluation, CPTFROM dbo.shx_BasicCallDBUNION ALLSELECT PhoneCode, AppId, BeginDate, EndDate, ServiceBeginDate, ServiceEndDate, TalkDur, CallLength, UserSN, ExtNO, InOutSign, AutoSign, CallResult, FileName, ChainIndex, ServiceEvaluation, CPTFROM [BasicCallDB].[dbo].[v_shx_BasicCallDB]GO
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。