建站
咨詢
售后
建站咨詢
新聞中心
SQLSERVER的表值函數(shù)是SQLSERVER 2005以來的新特性，由于它使用比較方便，就像一個(gè)單獨(dú)的表一樣，在我們的系統(tǒng)中大量使用。有一個(gè)獲取客戶數(shù)據(jù)的SQLSERVER 表值函數(shù)，如果使用管理員登錄，這個(gè)函數(shù)會(huì)返回150W行記錄，大概需要30秒左右，但如果將TOP語句放到表值函數(shù)外，效率異常低下，需要約3分鐘：
- select top 20 * from GetFrame_CustomerSerch('admin','1')
下面是該存儲(chǔ)過程的定義：
- 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
- (
- --具體子查詢略
- )
- )
這個(gè)語句放在PDF.NET數(shù)據(jù)開發(fā)框架的SQL-MAP文件中，開始還以為是框架引起的，將這個(gè)語句直接在查詢分析器中查詢，仍然很慢。
將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
- (
- --具體子查詢略
- )
為什么會(huì)有這么大的差異？
我分析可能有如下原因：
1，在表值函數(shù)外使用Top或者其它條件，SQLSERVER 的查詢優(yōu)化器無法針對(duì)此查詢進(jìn)行優(yōu)化，比如先返回所有記錄，然后再在臨時(shí)表中選取前面的20條記錄；
2，雖說該表值函數(shù)使用了“表變量”，它是內(nèi)存中的，但如果這個(gè)“表”結(jié)果很大，很有可能內(nèi)存放不下（并非還有物理內(nèi)存就會(huì)將結(jié)果放到物理內(nèi)存中，數(shù)據(jù)庫自己還會(huì)有保留的，會(huì)給其它查詢預(yù)留一定的內(nèi)存空間），使用虛擬內(nèi)存，而虛擬內(nèi)存實(shí)際上就是磁盤頁面文件，當(dāng)記錄太多就會(huì)發(fā)生頻繁的頁面交換，從而導(dǎo)致這個(gè)查詢效率非常低。
看來，“表值函數(shù)”也不是傳說中的那么好，不知道大家是怎么認(rèn)為的。
最近還遇到一個(gè)怪異的問題，有一個(gè)存儲(chǔ)過程，老是在系統(tǒng)運(yùn)行1-2天后變得極其緩慢，但重新修改一下又很快了（只是加一個(gè)空格之類），不知道大家遇到過沒有，什么原因？
分享標(biāo)題：分析TOP語句放到表值函數(shù)外，效率異常低下的原因
當(dāng)前鏈接：http://m.fisionsoft.com.cn/article/dpcdjjc.html
