存储过程优化

原来的分页的存储过程

/*
作用：获取用户会议列表
描述：会议包含已召开、预召开、正在召开的
*/


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)