TOP语句放到表值函数外，效率异常低下的原因分析

SQLSERVER的表值函数是SQLSERVER 2005以来的新特性，由于它使用比较方便，就像一个单独的表一样，在我们的系统中大量使用。有一个获取客户数据的SQLSERVER 表值函数，如果使用管理员登录，这个函数会返回150W行记录，大概需要30秒左右，但如果将TOP语句放到表值函数外，效率异常低下，需要约3分钟：

下面是该存储过程的定义：

ALTER   FUNCTION   [ dbo ] . [ GetFrame_CustomerSerch ]
(    
     --  Add the parameters for the function here
     @WorkNo   varchar ( 38 )
    , @SerchChar   varchar ( 500 )
)
RETURNS   TABLE  
AS
RETURN  
(
     --  Add the SELECT statement with parameter references here
     select  a.GUID,a.CustomerName,a.CustomerIDcard,a.CustomerPhone,a.CustomerMobile  from
    (
   --具体子查询略
    )
    ) a  union   all
     select  b.GUID,b.CustomerName,b.CustomerIDcard,b.CustomerPhone,b.CustomerMobile  from  WFT_ManagerCollectUsers a  left   join  WFT_Customer b  on  a.FundAccount = b.FundAccount
     -- where a.WorkNo=@WorkNo
     WHERE  a.WorkNo  IN
    (

--具体子查询略
    )
    )

这个语句放在PDF.NET数据开发框架的SQL-MAP文件中，开始还以为是框架引起的，将这个语句直接在查询分析器中查询，仍然很慢。

将GetFrame_CustomerSerch 中的SQL语句提取出来，直接加上Top查询，只需要6秒，快了N倍：

declare   @WorkNo   varchar ( 38 )
declare   @SerchChar   varchar ( 500 )
set   @WorkNo = ' admin '
set   @SerchChar = ' 1 '
select   top   20  a.GUID,a.CustomerName,a.CustomerIDcard,a.CustomerPhone,a.CustomerMobile  from
 (
   -- 具体子查询略
 )
 ) a  union   all
  select  b.GUID,b.CustomerName,b.CustomerIDcard,b.CustomerPhone,b.CustomerMobile  from  WFT_ManagerCollectUsers a  left   join  WFT_Customer b  on  a.FundAccount = b.FundAccount
 
  WHERE  a.WorkNo  IN
 (
  -- 具体子查询略
 )
 

为什么会有这么大的差异？
我分析可能有如下原因：

1，在表值函数外使用Top或者其它条件，SQLSERVER 的查询优化器无法针对此查询进行优化，比如先返回所有记录，然后再在临时表中选取前面的20条记录；

2，虽说该表值函数使用了“表变量”，它是内存中的，但如果这个“表”结果很大，很有可能内存放不下（并非还有物理内存就会将结果放到物理内存中，数据库自己还会有保留的，会给其它查询预留一定的内存空间），使用虚拟内存，而虚拟内存实际上就是磁盘页面文件，当记录太多就会发生频繁的页面交换，从而导致这个查询效率非常低。

看来，“表值函数”也不是传说中的那么好，不知道大家是怎么认为的。

最近还遇到一个怪异的问题，有一个存储过程，老是在系统运行1-2天后变得极其缓慢，但重新修改一下又很快了（只是加一个空格之类），不知道大家遇到过没有，什么原因？

    本文转自深蓝医生博客园博客，原文链接：http://www.cnblogs.com/bluedoctor/archive/2011/04/27/2030305.html，如需转载请自行联系原作者