select top 20 * from GetFrame_CustomerSerch('admin','1')
将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
(
select * from WFT_Customer a where a.FinancialPlannerWorkNO IN
(SELECT gml.workno FROM dbo.GetManagerList(@WorkNo) gml)
and (
a.CustomerName like '%'+@SerchChar+'%'
or a.CustomerIDcard like '%'+@SerchChar+'%'
or a.CustomerMobile like '%'+@SerchChar+'%'
or a.CustomerPhone like '%'+@SerchChar+'%'
)
) 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
(SELECT gml.workno FROM dbo.GetManagerList(@WorkNo) gml)
and (
b.CustomerName like '%'+@SerchChar+'%'
or b.CustomerIDcard like '%'+@SerchChar+'%'
or b.CustomerMobile like '%'+@SerchChar+'%'
or b.CustomerPhone like '%'+@SerchChar+'%'
)
declare @SerchChar varchar(500)
set @WorkNo='admin'
set @SerchChar='1'
select top 20 a.GUID,a.CustomerName,a.CustomerIDcard,a.CustomerPhone,a.CustomerMobile from
(
select * from WFT_Customer a where a.FinancialPlannerWorkNO IN
(SELECT gml.workno FROM dbo.GetManagerList(@WorkNo) gml)
and (
a.CustomerName like '%'+@SerchChar+'%'
or a.CustomerIDcard like '%'+@SerchChar+'%'
or a.CustomerMobile like '%'+@SerchChar+'%'
or a.CustomerPhone like '%'+@SerchChar+'%'
)
) 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
(SELECT gml.workno FROM dbo.GetManagerList(@WorkNo) gml)
and (
b.CustomerName like '%'+@SerchChar+'%'
or b.CustomerIDcard like '%'+@SerchChar+'%'
or b.CustomerMobile like '%'+@SerchChar+'%'
or b.CustomerPhone like '%'+@SerchChar+'%'
)
为什么会有这么大的差异？
我分析可能有如下原因：
1，在表值函数外使用Top或者其它条件，SQLSERVER 的查询优化器无法针对此查询进行优化，比如先返回所有记录，然后再在临时表中选取前面的20条记录；
2，虽说该表值函数使用了“表变量”，它是内存中的，但如果这个“表”结果很大，很有可能内存放不下（并非还有物理内存就会将结果放到物理内存中，数据库自己还会有保留的，会给其它查询预留一定的内存空间），使用虚拟内存，而虚拟内存实际上就是磁盘页面文件，当记录太多就会发生频繁的页面交换，从而导致这个查询效率非常低。
本文转自深蓝医生博客园博客，原文链接：http://www.cnblogs.com/bluedoctor/archive/2012/01/20/2327957.html，如需转载请自行联系原作者