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

数据库 SQL Server 数据库运维
有一个获取客户数据的SQLSERVER 表值函数,如果使用管理员登录,这个函数会返回150W行记录,大概需要30秒左右,但如果将TOP语句放到表值函数外,效率异常低下,需要约3分钟,为什么呢?

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

  1. select top 20  * from GetFrame_CustomerSerch('admin','1'

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

  1. ALTER FUNCTION [dbo].[GetFrame_CustomerSerch]  
  2. (      
  3.     -- Add the parameters for the function here  
  4.     @WorkNo varchar(38)  
  5.     ,@SerchChar varchar(500)  
  6. )  
  7. RETURNS TABLE   
  8. AS 
  9. RETURN   
  10. (  
  11.     -- Add the SELECT statement with parameter references here  
  12.     select a.GUID,a.CustomerName,a.CustomerIDcard,a.CustomerPhone,a.CustomerMobile from 
  13.     (  
  14.    --具体子查询略  
  15.     )  
  16.     ) a union all 
  17.     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  
  18.     --where a.WorkNo=@WorkNo  
  19.     WHERE a.WorkNo IN 
  20.     (  
  21. --具体子查询略  
  22.     )  
  23.     ) 

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

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

  1. declare @WorkNo varchar(38)  
  2. declare @SerchChar varchar(500)  
  3. set @WorkNo='admin' 
  4. set @SerchChar='1' 
  5. select top 20 a.GUID,a.CustomerName,a.CustomerIDcard,a.CustomerPhone,a.CustomerMobile from 
  6.  (  
  7.   --具体子查询略  
  8.  )  
  9.  ) a union all 
  10.  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  
  11.    
  12.  WHERE a.WorkNo IN 
  13.  (  
  14.  --具体子查询略  
  15.  ) 

为什么会有这么大的差异?

我分析可能有如下原因:

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

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

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

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

原文链接:http://www.cnblogs.com/bluedoctor/archive/2011/04/27/2030305.html

【编辑推荐】

  1. 双TOP二分法生成分页SQL类
  2. SQL Server数据库中简单的SELECT TOP
  3. 利用top构造Sql Server分页查询
责任编辑:艾婧 来源: 博客园
相关推荐

2009-09-09 13:53:21

Linq表值函数

2018-04-19 08:40:37

OracleJOB异常中断

2013-04-17 10:50:51

北美大型数据中心能源效率

2020-03-23 10:42:56

团队协作阿里

2021-01-04 05:46:00

AI人工智能深度学习

2012-10-11 10:21:33

数据中心CPU利用率服务器效率

2010-11-25 13:32:57

MySQL系统效率

2009-07-20 15:51:45

JDBC连接Oracl

2010-09-16 14:38:55

Sql server表

2009-09-04 17:31:32

C# SQL语句执行函

2018-07-31 15:53:00

数据库MySQLi-o

2010-09-25 11:39:37

SQL语句

2009-11-02 16:14:53

2010-09-17 16:03:17

锁定SQL表

2011-06-14 18:02:26

外链网站降权

2010-11-10 11:37:29

SQL Server删

2011-06-29 18:37:12

外链

2011-08-01 16:31:29

MySQL数据库外键

2010-11-15 16:46:49

Oracle查询效率

2010-11-22 10:08:03

Mysql外键用法
点赞
收藏

51CTO技术栈公众号