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