查詢計(jì)劃

Sql Server在執(zhí)行一條查詢語(yǔ)句之前都對(duì)對(duì)它進(jìn)行“編譯”并生成“查詢計(jì)劃”，查詢計(jì)劃告訴Sql Server的查詢引擎應(yīng)該用什么方式進(jìn)行工作。Sql Server會(huì)根據(jù)當(dāng)前它可以收集到的各種信息（例如內(nèi)存大小，索引的統(tǒng)計(jì)等等）把一條查詢語(yǔ)句編譯成它認(rèn)為“最優(yōu)”的查詢計(jì)劃。很顯然，得到這樣一個(gè)查詢計(jì)劃需要消耗CPU資源，而大部分的查詢語(yǔ)句每次經(jīng)過(guò)編譯所得到的查詢計(jì)劃往往是相同的，因此除非指定了RECOMPILE選項(xiàng)，Sql Server在執(zhí)行查詢語(yǔ)句時(shí)，會(huì)對(duì)查詢計(jì)劃進(jìn)行緩存——也就是說(shuō)，如果是相同的查詢語(yǔ)句，Sql Server只會(huì)對(duì)它進(jìn)行一次編譯操作，然后在每次執(zhí)行時(shí)對(duì)查詢計(jì)劃進(jìn)行復(fù)用。查詢計(jì)劃如果無(wú)法復(fù)用，則會(huì)在相當(dāng)程度上降低數(shù)據(jù)庫(kù)性能——因?yàn)檫^(guò)多的CPU被消耗在查詢語(yǔ)句的編譯上。各種提及數(shù)據(jù)庫(kù)查詢優(yōu)化的資料上大都會(huì)提到這一點(diǎn)，我們往往通過(guò)查看性能計(jì)數(shù)器的某些統(tǒng)計(jì)，或者Sql Server系統(tǒng)表中的一些記錄，就可以判定您的數(shù)據(jù)庫(kù)應(yīng)用是否出現(xiàn)了這個(gè)問(wèn)題。

對(duì)于存儲(chǔ)過(guò)程來(lái)說(shuō)，復(fù)用查詢計(jì)劃是輕而易舉的。不過(guò)對(duì)于那些喜歡在程序代碼中拼接Sql字符串的朋友來(lái)說(shuō)，日子就有些不好過(guò)了。Sql Server是根據(jù)您傳入的Sql語(yǔ)句來(lái)緩存查詢計(jì)劃的，如果您“強(qiáng)行”拼接了Sql字符串并交給Sql Server執(zhí)行，那么查詢計(jì)劃被復(fù)用的可能性微乎其微。因此，我們絕對(duì)應(yīng)該杜絕拼接字符串的行為，因?yàn)檫@不僅僅造成了傳統(tǒng)的Sql注入！而那些習(xí)慣相對(duì)較好的朋友，則會(huì)使用帶參數(shù)的Sql語(yǔ)句，在交給Sql Server執(zhí)行時(shí)就可能復(fù)用查詢計(jì)劃。因?yàn)楹驼{(diào)用存儲(chǔ)過(guò)程相比，發(fā)送帶參數(shù)的Sql語(yǔ)句只是將使用了sp_executesql命令而已，每次執(zhí)行的查詢語(yǔ)句還是相同的。

問(wèn)題何在？

對(duì)于復(fù)用查詢計(jì)劃的問(wèn)題，在上文中我說(shuō)了這么一句話：“……使用帶參數(shù)的Sql語(yǔ)句，在交給Sql Server執(zhí)行時(shí)就可能復(fù)用查詢計(jì)劃……”。我為什么要說(shuō)“可能”？因?yàn)榧磿r(shí)使用帶參數(shù)的Sql語(yǔ)句，在某些情況下我們還是無(wú)法對(duì)查詢計(jì)劃進(jìn)行復(fù)用。這是怎么一回事兒呢？我們還是直接從Linq to Sql來(lái)產(chǎn)生Sql語(yǔ)句，然后觀察Sql Server的行為吧。

請(qǐng)看以下的代碼（示例所操作的數(shù)據(jù)表與《在Linq to Sql中管理并發(fā)更新時(shí)的沖突（2）：引發(fā)更新沖突》一文相同）：

LinqToSqlDemoDataContext dataContext = new LinqToSqlDemoDataContext();dataContext.Log = Console.Out;Video video1 = dataContext.Videos.SingleOrDefault(    v => v.Introduction == "Hello");Video video2 = dataContext.Videos.SingleOrDefault(    v => v.Introduction == "Hello World");Console.ReadLine();

還是查看輸出：

SELECT [t0].[VideoID], [t0].[Introduction], [t0].[SiteID]FROM [dbo].[Video] AS [t0]WHERE [t0].[Introduction] = @p0-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Hello]-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21004.1SELECT [t0].[VideoID], [t0].[Introduction], [t0].[SiteID]FROM [dbo].[Video] AS [t0]WHERE [t0].[Introduction] = @p0-- @p0: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [Hello World]-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21004.1 

兩句Sql語(yǔ)句完全相同，按我們剛才的說(shuō)法，Sql Server應(yīng)該緩存了查詢計(jì)劃。但是我們通過(guò)查看sys.syscacheobjects的相關(guān)數(shù)據(jù)可以看出，事情并非如同我們想象的那樣：

SELECT cheacobjtype, sql FROM sys.syscacheobjects;DBCC freeproccache;

請(qǐng)注意上圖中被選中的兩條記錄，它表明了Sql Server并沒(méi)有緩存執(zhí)行計(jì)劃。

為什么？這兩次執(zhí)行究竟有什么區(qū)別？通過(guò)Linq to Sql很容易看出，兩次執(zhí)行所用到的參數(shù)不同。更進(jìn)一步，如果對(duì)比Linq to Sql輸出的緩存以及sys.syscacheobjects視圖中的記錄，就會(huì)發(fā)現(xiàn)：其實(shí)僅僅是參數(shù)的尺寸不同。

沒(méi)錯(cuò)，就是這個(gè)原因。在使用ADO.NET時(shí)，如果SqlParameter的Type是nvarchar，并且沒(méi)有指定Size屬性，則可能就會(huì)因?yàn)榫唧w參數(shù)的尺寸不同而造成查詢計(jì)劃無(wú)法復(fù)用的結(jié)果。這一點(diǎn)，很多人都忽視了。

優(yōu)化方案

在使用ADO.NET進(jìn)行開(kāi)發(fā)時(shí)，該問(wèn)題其實(shí)很容易解決。我們只要指定SqlParameter的Size屬性即可。由于每次指定了一個(gè)固定的參數(shù)尺寸，Sql Server就能夠復(fù)用查詢計(jì)劃了。

不過(guò)我們現(xiàn)在在使用Linq to Sql，又該怎么做呢？嗯，我們可以為XXXXDataContext重寫(xiě)（override）SubmitChanges方法，在其中獲得需要執(zhí)行的SqlCommand對(duì)象（具體方法請(qǐng)參考《在Linq to Sql中管理并發(fā)更新時(shí)的沖突（1）：預(yù)備知識(shí)》一文），獲得其中的SqlParameter參數(shù)，并設(shè)定它們的Size屬性。我們可以使用Custom Attribute來(lái)標(biāo)注應(yīng)該為哪個(gè)屬性設(shè)置什么樣的Size，如果再結(jié)合AOP，哈哈……

等等，先別想那么遠(yuǎn)。即使得到了SqlCommand對(duì)象，它所生成的Sql語(yǔ)句是以@p0、@p1作為參數(shù)名，您知道該修改哪個(gè)SqlParameter對(duì)象嗎？再者，SubmitChanges方法只是提交我們做出的修改，但是在一般的系統(tǒng)中，查詢操作的次數(shù)和性能消耗大大超過(guò)修改操作，而重寫(xiě)了SubmitChangeds方法又不能影響我們的優(yōu)化操作……

因此，我想在這里說(shuō)的是：這個(gè)問(wèn)題我們沒(méi)法進(jìn)行優(yōu)化。

不過(guò)我們還是幸運(yùn)的，因?yàn)槲腋鶕?jù)我的經(jīng)驗(yàn)，似乎在查詢條件中使用長(zhǎng)度不等的字符串作為參數(shù)的情況并不多見(jiàn)。不是么？

it知識(shí)庫(kù)：LINQ to SQL的執(zhí)行可能無(wú)法復(fù)用查詢計(jì)劃，轉(zhuǎn)載需保留來(lái)源！

鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請(qǐng)第一時(shí)間聯(lián)系我們修改或刪除，多謝。