存储过程优化

原来的分页的存储过程

?

/*

作用：获取用户会议列表

描述：会议包含已召开、预召开、正在召开的

*/

 

 

ALTER Procedure [dbo].[WTC_SP_GetUserConfListPage]

  @SeqNo        int, 

  @PageIndex     int, 

  @PageSize      int,

  @Title    Varchar(100),

  @StartTime    Varchar(50),

  @EndTime    Varchar(50),

  @RecordCount   int output

As

Begin

    Create Table #UserConfList(

        confRoom uniqueidentifier,--会议标识

        SeqNo int,              --用户编号

        ConfTitle Varchar(256), --主题

        ConfTime dateTime,      --开始时间、预约时间、

        HoldTime int,           --持续时长(只针对已如开会议)

        ActorMember int,         --参与者人数

        IsRecord  int,          --是否有录音(只针对已如开会议)

        ConfState   int         --会议状态 1 已召开 2 正在召开 3 预约会议

         

    )

    Declare @Index int 

    SET @Index = @PageSize*@PageIndex 

    print @StartTime

    print @EndTime

--  Declare @Where Varchar(1000)

--  SET @Where =' '

--  IF @strQuery <>''

--  Begin

--  SET @Where = @strQuery;

--  End

    IF @Title>''

Begin

    Insert Into #UserConfList(confRoom,SeqNo,ConfTitle,ConfTime,HoldTime,ActorMember,IsRecord,ConfState)

    SELECT ConfRoom,SeqNo,ConfTitle,BookTime,0,dbo.WTC_FN_StatBookMembers(ConfRoom),0,3

    FROM WTC_TB_BOOKCONF  --预约会议

    Where  SeqNo=@SeqNo and ([status]=0 or [status]=4)  And  BookTime >=''+convert(varchar(19),@StartTime,120)+''And BookTime<=''+ Convert(Varchar(19),@EndTime,120)+' 23:59:59' and CONFTITLE like '%'+@Title+'%'

    union all --已召开的会议

    SELECT ConfRoom,SeqNo,ConfTitle,ConfTime,0,dbo.WTC_FN_StatMembers(ConfRoom),isrecord,1

    FROM WTC_TB_USERCONF

    WHERE SeqNo=@SeqNo And Flag in(0) And  ConfTime >=''+convert(varchar(19),@StartTime,120)+'' And ConfTime<=''+ Convert(Varchar(19),@EndTime,120)+' 23:59:59' And CONFTITLE like '%'+@Title+'%'

 

    union all --待召开的会议

    SELECT ConfRoom,SeqNo,ConfTitle,ConfTime,0,dbo.WTC_FN_StatMembers(ConfRoom),isrecord,4

    FROM WTC_TB_USERCONF

    WHERE SeqNo=@SeqNo And Flag=1  And  ConfTime >=''+convert(varchar(19),@StartTime,120)+'' And ConfTime<=''+ Convert(Varchar(19),@EndTime,120)+' 23:59:59' And CONFTITLE like '%'+@Title+'%'

End

Else

Begin

    Insert Into #UserConfList(confRoom,SeqNo,ConfTitle,ConfTime,HoldTime,ActorMember,IsRecord,ConfState)

    SELECT ConfRoom,SeqNo,ConfTitle,BookTime,0,dbo.WTC_FN_StatBookMembers(ConfRoom),0,3

    FROM WTC_TB_BOOKCONF  --预约会议

    Where  SeqNo=@SeqNo and ([status]=0 or [status]=4)  And  BookTime >=''+convert(varchar(19),@StartTime,120)+''And BookTime<=''+ Convert(Varchar(19),@EndTime,120)+' 23:59:59' --and CONFTITLE like '%'+@Title+'%'

    union all --已召开的会议

    SELECT ConfRoom,SeqNo,ConfTitle,ConfTime,0,dbo.WTC_FN_StatMembers(ConfRoom),isrecord,1

    FROM WTC_TB_USERCONF

    WHERE SeqNo=@SeqNo And Flag in(0) And  ConfTime >=''+convert(varchar(19),@StartTime,120)+'' And ConfTime<=''+ Convert(Varchar(19),@EndTime,120)+' 23:59:59' --And CONFTITLE like '%'+@Title+'%'

 

    union all --待召开的会议

    SELECT ConfRoom,SeqNo,ConfTitle,ConfTime,0,dbo.WTC_FN_StatMembers(ConfRoom),isrecord,4

    FROM WTC_TB_USERCONF

    WHERE SeqNo=@SeqNo And Flag=1  And  ConfTime >=''+convert(varchar(19),@StartTime,120)+'' And ConfTime<=''+ Convert(Varchar(19),@EndTime,120)+' 23:59:59' --And CONFTITLE like '%'+@Title+'%'

End

    Declare @Sql Nvarchar(2000)

    Declare @OrderBy Varchar(100)

    Set @OrderBy=' order by confTime desc'

  

--  IF @Where<>''

--  Begin

--      SET @Sql = N'SELECT @Total=count(*) FROM #UserConfList Where '+@Where

--     

--      execute sp_executesql @Sql, N'@Total int output', @RecordCount output

--

--      Set @Sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (' + @OrderBy +') rowid, * FROM #UserConfList Where ' + @Where + ') AS TEMP

--      WHERE rowid BETWEEN ' + Convert(varchar(8),(@Index-@PageSize+1)) + ' AND ' + Convert(varchar(8),@Index)

--      print @Sql

--      Exec(@Sql)

--  End

--  Else

--  Begin

        SET @Sql = N'SELECT @Total=count(*) FROM #UserConfList'

         

        execute sp_executesql @Sql, N'@Total int output', @RecordCount output

         

        Set @Sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (' + @OrderBy +') rowid, * FROM #UserConfList ) AS TEMP

        WHERE rowid BETWEEN ' + Convert(varchar(8),(@Index-@PageSize+1)) + ' AND ' + Convert(varchar(8),@Index)

        print @Sql

        Exec(@Sql)

--  End

End

 

优化后

?

--WTC_SP_GetUserConfListPage 创建索引

 

CREATE INDEX IX_WTC_TB_BOOKCONF_SeqNo_BOOKTIME ON dbo.WTC_TB_BOOKCONF

(

    SeqNo,

    BOOKTIME

) WITH(FILLFACTOR = 90)

GO

 

CREATE INDEX IX_WTC_TB_BOOKMEMBER_CONFROOM ON dbo.WTC_TB_BOOKMEMBER

(

    CONFROOM

) WITH(FILLFACTOR = 90)

GO

 

CREATE INDEX IX_WTC_TB_USERCONF_SeqNo_CONFTIME ON dbo.WTC_TB_USERCONF

(

    SeqNo,

    CONFTIME

) WITH(FILLFACTOR = 90)

GO

 

CREATE INDEX IX_WTC_TB_CONFMEMBERS_CONFROOM ON dbo.WTC_TB_CONFMEMBERS

(

    CONFROOM

) WITH(FILLFACTOR = 90)

GO

 

 

?

/*=============================================================

功能描述: 获取用户会议列表,会议包含已召开、预召开、正在召开的

修改记录: 性能优化

=============================================================*/

ALTER PROCEDURE [dbo].[WTC_SP_GetUserConfListPage]

    @SeqNo INT,

    @PageIndex INT,

    @PageSize INT,

    @Title VARCHAR(100),

    @StartTime VARCHAR(50),

    @EndTime VARCHAR(50),

    @RecordCount INT OUTPUT

AS

SET NOCOUNT ON

 

DECLARE @tableUserConfList TABLE

(

    rowid INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

    confRoom UNIQUEIDENTIFIER,--会议标识 

    SeqNo INT,    --用户编号 

    ConfTitle VARCHAR(256), --主题 

    ConfTime DATETIME,      --开始时间、预约时间、 

    HoldTime INT,   --持续时长(只针对已如开会议) 

    ActorMember INT,         --参与者人数 

    IsRecord INT,   --是否有录音(只针对已如开会议) 

    ConfState INT   --会议状态 1 已召开 2 正在召开 3 预约会议 

) 

 

INSERT INTO @tableUserConfList

(

    confRoom,

    SeqNo,

    ConfTitle,

    ConfTime,

    HoldTime,

    ActorMember,

    IsRecord,

    ConfState

)

SELECT

    A.ConfRoom,

    A.SeqNo,

    A.ConfTitle,

    ConfTime = A.BookTime,

    0,

    B.ActorMember,

    0,

    3

FROM dbo.WTC_TB_BOOKCONF A WITH(NOLOCK) --预约会议 

    CROSS APPLY

    (

        SELECT ActorMember = COUNT(1)

        FROM dbo.WTC_TB_BOOKMEMBER B WITH(NOLOCK)

        WHERE B.ConfRoom = A.CONFROOM

    ) B

WHERE A.SeqNo = @SeqNo

    AND A.BookTime >= CONVERT(CHAR(10), @StartTime, 120)

    AND A.BookTime <= CONVERT(CHAR(10), @EndTime, 120) + ' 23:59:59.999'

    AND (A.[status] = 0 OR A.[status] = 4)

    AND (@Title = '' OR A.CONFTITLE LIKE '%' + @Title + '%')

 

UNION ALL --已召开的会议 & 待召开的会议

 

SELECT

    A.ConfRoom,

    A.SeqNo,

    A.ConfTitle,

    A.ConfTime,

    0,

    B.ActorMember,

    A.isrecord,

    CASE a.Flag

        WHEN 0 THEN 1

        WHEN 1 THEN 4

    END

FROM dbo.WTC_TB_USERCONF A WITH(NOLOCK)

    CROSS APPLY

    (

        SELECT ActorMember = COUNT(1)

        FROM dbo.WTC_TB_CONFMEMBERS B WITH(NOLOCK)

        WHERE B.ConfRoom = A.CONFROOM

    ) B

WHERE A.SeqNo = @SeqNo

    AND A.ConfTime >= CONVERT(CHAR(10), @StartTime, 120)

    AND A.ConfTime <= CONVERT(CHAR(10), @EndTime, 120) + ' 23:59:59.999'

    AND A.Flag IN (0, 1)

    AND (@Title = '' OR A.CONFTITLE LIKE '%' + @Title + '%')

ORDER BY ConfTime DESC

 

--Total Count

SET @RecordCount = @@ROWCOUNT

 

--Paging

SELECT

    rowid,

    confRoom,

    SeqNo,

    ConfTitle,

    ConfTime, 

    HoldTime,

    ActorMember,

    IsRecord,

    ConfState

FROM @tableUserConfList

WHERE rowid BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND (@PageSize * @PageIndex)