SQL Server 2005中使用临时表和@@RowCount提高分页查询存储流程性能

SQL Server 2005中使用临时表和@@RowCount提高分页查询存储流程性能
①②③④⑤⑥
最近发觉现有框架的通用查询存储流程的性能慢，于是仔细研究了下代码：
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,@Tota lPage 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
DataGrid 服务器端分页、排序的实现
先看一下效果。

美工不好，见笑。

呵呵。

Silverlight DataGrid 本身是不带分页功能的，同时他的排序也是针对当前页面的内容进行排序的，而这两样功能在实际的项目中都是必须带的。

上网搜索了一下，好像目前还没有那篇文章介绍过如何实现这两个常用的功能，看样子只要自己动手了。

呵呵。

下面我们来一步一步实现这些功能。

（以下都以NorthWind数据库为演示）第一步，我们先实现分页。

要分页，首先要在WCF上加上一个分页获取数据的方法。

先上代码。

代码比较简单，用了sql2005的分页方法，就不做解释了，至于这分页方法的效率怎么样不是本次学习的重点，先不管了。

数据库访问使用LinqToSQL做的，本来想用Linq实现分页功能的，但是忙活了半天，最终以失败告终，主要的问题是，我没办法在Linq中实现动态排序，网上也没有找到一个合适的方法，只能写n个ifelse进行排序，就像下面的代码：
这样写的话，虽然功能能实现，但是实在是有点弱智。

要写上一大堆的代码，真的不如写sql呢。

最终，还是没有搞定这个问题，只好写sql语句了。

如果大家有什么好的办法能实现的话，请留言告诉我。

谢谢了！
ok，到此分页的基本功能搞定了，DataGrid已经可以获取分页的数据了。

但是，这个DataGrid和Web上的不一样，没有Pager，晕，只要手工写一个Pager控件了。

第二步，实现Pager。

动手之前，老办法。

先上网找现成的，呵呵，不错，这次终于有点结果了，网上不少这样的例子，在上找了一个Pager，先用着再说。

呵呵。

这个是地址/page/A-Generic-Pager-Control.aspx，大家自己去看吧，用法很简单。

现在把代码整合起来，ok，已经可以正确的分页了。

哈哈。

接下来，我们来实现排序。

第三步，实现排序。

要排序，当然是要点击ColumnHeader了，在DataGrid中找ColumnHeader的点击事件，晕，没有。

不会吧，这个微软，怎么搞的啊，虽然Silverlight都2.0了，但是怎么看都还是像个半成品啊，这样常用的事件都没有。

半成品就半成品吧，继续手工实现吧。

又是老办法，上网找现成的，呵呵，两种方案，一种是把Header改成模板，然后里面放上Button，这样就可以实现了。

还有一种，有人提到了用DataGrid的HitTest方法。

呵呵，第一个有点费事，每个Header都要改，第二个看起来酷一点，所以我选择了第二种。

试验了一下，晕，HitTest怎么成了不可访问，受保护级别限制。

再查MSDN，呵呵，原来2.0里面该成了VisualTreeHelper.FindElementsInHostCoordinates了。

ok，下面上代码：
在DataGrid的MouseLeftButtonDown写。

这里有个奇怪的问题，点击ColumnHeader 并不会触发MouseLeftButtonUp事件。

真的搞不明白。

好，到此，整个分页、排序的功能就基本上完成了。

呵呵，仔细用一下程序发现，我们在实现了排序之后，DataGrid 然而会自作聪明的帮我们的数据又排了一下，并且，DataGrid默认是支持列拖动的，当我们拖动列时，会触发排序，导致结果和用户预计的不一样。

那怎么解决呢，
直接在DataGrid中设置CanUserSortColumns="False"和CanUserReorderColumns="False"。

就可以解决了。

再在测试一下，发现在排序之后，ColumnHeader上面并没有显示标示排序方向的小箭头，查了一下，MSDN上面在介绍DataGrid 样式和模板的时候，提到过DataGridColumnHeader 状态里面有SortAscending和SortDescending这两种状态，ok，我们就接着写了个方法，来设置Header的状态。

在上面MouseLeftButtonDown中我们曾经把排序的Header保存了下来，就是为了在这里使用的。

代码如下：
很简单，但是有个奇怪的问题，就是在排序之后，设置了Header的状态并不能保存下来，鼠标移动，那个小箭头就会消失，具体原因我也不明白，我不太清楚
VisualStateManager.GoToState在设置了Header状态是不是和DataGrid自带的排序功能有关联，才导致了排序的小箭头会自动消失，或者在这里根本就不应该用这个方法。

暂时我没有去考虑这个问题，为了让排序小箭头能正确显示，我只好在Grid的MouseMove和LayoutUpdated事件里都调用了setColumnSortState()，用来保证小箭头可以正确显示。

至此，整个分页、排序的功能全部完成。

除了那个小箭头显示的处理不完美之外，其它到也没有什么大问题。

我会在后面的学习中，来解决这个问题。