最近發現現有框架的通用查詢存儲過程的性能慢，於是仔細研究了下代碼：
代碼如下：
Alter PROCEDURE [dbo].[AreaSelect]
@PageSize int=0,
@CurrentPage int=1,
@Identifier int=NULL,
@ParentId int=NULL,
@AreaLevel int=NULL,
@Children int=NULL,
@AreaName nvarchar(50)=NULL,
@Path nvarchar(MAX)=NULL,
@Status int=NULL,
@Alt int=NULL
AS
BEGIN
SET NOCOUNT ON;
IF (NOT @AreaName IS NULL) SET @AreaName='%'+@AreaName+'%'
IF (NOT @Path IS NULL) SET @Path='%'+@Path+'%'
IF (@PageSize>0)
BEGIN
DECLARE @TotalPage int
Select @TotalPage=Count(Identifier) FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
IF(@TotalPage%@PageSize=0)
BEGIN
SET @TotalPage=@TotalPage/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@TotalPage/@PageSize,0)+1
END
Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt,@TotalPage as totalPage FROM Area Where
Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc)
AND
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
END
ELSE
BEGIN
Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
END
END
發現每次查詢都需要按條件查詢依次Area表，性能太低，於是利用臨時表將符合條件的記錄取出來，然後針對臨時表進行查詢，代碼修改如下：
Alter PROCEDURE [dbo].[AreaSelect]
@PageSize int=0,
@CurrentPage int=1,
@Identifier int=NULL,
@ParentId int=NULL,
@AreaLevel int=NULL,
@Children int=NULL,
@AreaName nvarchar(50)=NULL,
@Path nvarchar(MAX)=NULL,
@Status int=NULL,
@Alt int=NULL
AS
BEGIN
SET NOCOUNT ON;
IF (NOT @AreaName IS NULL) SET @AreaName='%'+@AreaName+'%'
IF (NOT @Path IS NULL) SET @Path='%'+@Path+'%'
IF (@PageSize>0)
BEGIN
--創建臨時表
Select
Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
INTO #temp_Area
FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
DECLARE @TotalPage int
DECLARE @SumCount int
--取總數
Select @SumCount=Count(Identifier) FROM #temp_Area
IF(@SumCount%@PageSize=0)
BEGIN
SET @TotalPage=@SumCount/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@SumCount/@PageSize,0)+1
END
Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
FROM #temp_Area
Where
Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
END
ELSE
BEGIN
Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
END
END
經過使用臨時表的確提高性能，不過有發現一個問題，就是count(Identifier)的確很耗性能，於是又進行修改了
：
Alter PROCEDURE [dbo].[AreaSelect]
@PageSize int=0,
@CurrentPage int=1,
@Identifier int=NULL,
@ParentId int=NULL,
@AreaLevel int=NULL,
@Children int=NULL,
@AreaName nvarchar(50)=NULL,
@Path nvarchar(MAX)=NULL,
@Status int=NULL,
@Alt int=NULL
AS
BEGIN
SET NOCOUNT ON;
IF (NOT @AreaName IS NULL) SET @AreaName='%'+@AreaName+'%'
IF (NOT @Path IS NULL) SET @Path='%'+@Path+'%'
IF (@PageSize>0)
BEGIN
--創建中記錄數
DECLARE @SumCount int
--創建臨時表
Select
Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
INTO #temp_Area
FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
--設置總記錄數為剛操作的記錄數
SET @SumCount=@@RowCount
DECLARE @TotalPage int
IF(@SumCount%@PageSize=0)
BEGIN
SET @TotalPage=@SumCount/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@SumCount/@PageSize,0)+1
END
Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
FROM #temp_Area
Where
Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
END
ELSE
BEGIN
Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
END
END
