首页 > 代码库 > 优化陕西重汽通话记录心得

优化陕西重汽通话记录心得

1 作业维护

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N[BasicCallDB].[dbo].[shx_BasicCallDB]) AND type in (NU))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 = Nshx_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