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

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]
GO

SELECT  [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 desc

IF  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 )
GO


CREATE 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]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_shx_AGENTLOG1]'))
DROP VIEW [dbo].[v_shx_AGENTLOG1]
GO
CREATE VIEW [dbo].[v_shx_AGENTLOG1]
AS
SELECT     COID, EmployeeID, StartTime, TimeLength
FROM         dbo.AGENTLOG1
WHERE     (DATEDIFF(dd, StartTime, GETDATE()) = 0)

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_shx_AGENTRECFILE]'))
DROP VIEW [dbo].[v_shx_AGENTRECFILE]
GO


CREATE VIEW [dbo].[v_shx_AGENTRECFILE]
AS
SELECT     COID, ServiceEvaluation, FileName, ExtensionNumber, CreateTime
FROM         dbo.AGENTRECFILE
WHERE     (DATEDIFF(dd, CreateTime, GETDATE()) = 0)

GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_shx_COLOG]'))
DROP VIEW [dbo].[v_shx_COLOG]
GO
CREATE VIEW [dbo].[v_shx_COLOG]
AS
SELECT     COID, CallerNumber, COID AS Expr1, CreateTime, ReleaseTime, ReleaseTime AS Expr2, TimeLength, OUTCALLERID, CallType, TransResult, CallResult, 
                      CalledNumber
FROM         dbo.COLOG
WHERE     (DATEDIFF(dd, CreateTime, GETDATE()) = 0)

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_shx_BasicCallDB]'))
DROP VIEW [dbo].[v_shx_BasicCallDB]
GO

CREATE VIEW [dbo].[v_shx_BasicCallDB]
AS
SELECT     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 CPT
FROM         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.COID

GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_BasicCallDB_New]'))
DROP VIEW [dbo].[v_BasicCallDB_New]
GO

CREATE VIEW [dbo].[v_BasicCallDB_New]
AS
SELECT     PhoneCode, AppId, BeginDate, EndDate, ServiceBeginDate, ServiceEndDate, TalkDur, CallLength, UserSN, ExtNO, InOutSign, AutoSign, CallResult, FileName, 
                      ChainIndex, ServiceEvaluation, CPT
FROM         dbo.shx_BasicCallDB
UNION ALL
SELECT     PhoneCode, AppId, BeginDate, EndDate, ServiceBeginDate, ServiceEndDate, TalkDur, CallLength, UserSN, ExtNO, InOutSign, AutoSign, CallResult, FileName, 
                      ChainIndex, ServiceEvaluation, CPT
FROM         [BasicCallDB].[dbo].[v_shx_BasicCallDB]

GO