亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

寫有效率的SQL查詢(V)

系統(tǒng) 1832 0

先站在應(yīng)用程序的角度說說它們的不同。

1、? 直接拼 SQL

就像大家了解的那樣,直接拼 SQL 帶來了 SQL 注入攻擊,帶來了拼時些許的性能損失,但是拼不用添加 SqlParameter ,會少寫很多代碼——很多人喜歡直接拼,也許就因?yàn)檫@點(diǎn)。這種做法會把你拼好的 SQL 原樣直接發(fā)送到 DB 服務(wù)器去執(zhí)行。(注意類似 ”exec yourproc ‘param1’, 12” 的語句不在此范疇,這是調(diào)用存儲過程的一種方式)

2、? 參數(shù)化 SQL

所謂的“參數(shù)化 SQL ”就是在應(yīng)用程序側(cè)設(shè)置 SqlCommand.CommandText 的時候使用參數(shù)(如: @param1 ),然后通過 SqlCommand.Parameters.Add 來設(shè)置這些參數(shù)的值。這種做法會把你準(zhǔn)備好的命令通過 sp_executesql 系統(tǒng)存儲過程來執(zhí)行。通過參數(shù)化 SQL ,和直接拼 SQL 相比,最直接的好處就是沒有 SQL 注入攻擊了。

3、? 調(diào)用存儲過程

直接調(diào)用存儲過程其實(shí)和參數(shù)化 SQL 非常相似。唯一的本質(zhì)不同在于你發(fā)送到 DB 服務(wù)器的指令不再是 sp_executesql ,而是直接的存儲過程調(diào)用而已。

?

很多人非常非常厭惡在應(yīng)用程序中使用存儲過程,而寧愿使用拼 SQL 或者參數(shù)化 SQL ,理由是它們提供了更好的靈活性——這個理由其實(shí)非常非常的發(fā)指(俺現(xiàn)在喜歡上這個詞了)。

現(xiàn)在做設(shè)計(jì),一般都是從上到下來,重心都在業(yè)務(wù)邏輯上。傳說中的領(lǐng)域模型設(shè)計(jì)完,測試用例都通過之后,才會考慮數(shù)據(jù)持久化方式。數(shù)據(jù)持久化是系統(tǒng)的一部分,但絕對不是最重要的部分,設(shè)計(jì)應(yīng)該圍繞業(yè)務(wù)邏輯開展,持久化應(yīng)該僅僅是個附件。至少,高層應(yīng)用應(yīng)該盡可能的不關(guān)心處于最底層的物理存儲結(jié)構(gòu)(如:表)和數(shù)據(jù)持久、反持久方式(是拼 SQL 還是存儲過程),所以用不用存儲過程根本不重要。很多人害怕存儲過程,其實(shí)是害怕存儲過程中包括業(yè)務(wù)邏輯——真實(shí)情況是,如果存儲過程中包含了業(yè)務(wù)邏輯,那一定最初需求分析不夠?qū)е掠美崛〔蛔悖瑢?dǎo)致測試用例覆蓋不夠,導(dǎo)致領(lǐng)域模型設(shè)計(jì)不充分,要不就是偷懶。

?

=====

站在 DB 角度討論它們的不同,主要從 cpu 、內(nèi)存方面來考慮,其他諸如安全性, msdn 上都有, google 也能拿到一堆資料,不再贅述。

首先是查詢計(jì)劃。

SQL 編譯完一條 SQL 之后,會把它緩存起來(可以通過 sys.syscacheobjects 系統(tǒng)視圖查看),以后再有相同的查詢過來(注意 sys.syscacheobjects 視圖中的 sql 字段,和它存儲的東西完全一樣才能稱為“相同的查詢”),會直接使用緩存,而不再重新編譯。

?? 存儲過程,伊只編譯一遍(如果沒有指定 with recompile 選項(xiàng)的話,如果指定了,根本就不會生成計(jì)劃緩存)。

?? 參數(shù)化 SQL ,和存儲過程基本一樣,只要是相同的查詢,也都是只編譯一次,以后重用(當(dāng)然,指定了 option(recompile) 的除外)。這里不得不提 .NET SqlClient 組件的一個齷齪:如果你的參數(shù)中包含 varchar 或者 char 類型的參數(shù),你在 Parameters.Add 的時候又沒有指定長度,它都會根據(jù)你實(shí)際傳入的字符串長度 ( 假設(shè)是 n) 給你重新定義成 nvarchar(n) 。如: select * from mytable where col1 = @p1 ,你設(shè)置 @p1 ’123456’ ,實(shí)際傳到 sql 這邊的命令是: exec sp_executesql N'select * from mytable where col1 = @p1',N'@p1 nvarchar(6)',@p1=N'123456' 。這樣,系統(tǒng)緩存中實(shí)際存儲的 sql 是: (@p1 nvarchar(6))select * from mytable where col1 = @p1 。看到了吧?如果你的輸入?yún)?shù)變動比較多,那么看起來同樣的一條語句,會被編譯很多次,在緩存中存儲很多份。 cpu 和內(nèi)存都浪費(fèi)了。這也是在《寫有效率的 SQL 查詢 IV 》中建議的使用最強(qiáng)類型參數(shù)匹配的原因之一。

?? SQL 。到這里不說大家也猜的出來,拼 SQL 要浪費(fèi)大量的 cpu 進(jìn)行編譯,浪費(fèi)大量緩存空間來存儲只用一次的查詢計(jì)劃。

?

服務(wù)器的物理內(nèi)存有限, SQLServer 的緩存空間也有限。有限的空間應(yīng)該被充分利用。通過性能計(jì)數(shù)器 SQL Server:Buffer Manager"Buffer Cache hit ratio 來觀察緩存命中率。如果它小于百分之 90 ,你就得研究研究了。關(guān)注一把諸如 sys.dm_os_memory_cache_counters sys.dm_os_memory_cache_entries sys.dm_os_memory_cache_hash_tables sys.syscacheobjects 等視圖,基本可以確定問題出在哪兒。

cpu 方面需要關(guān)注三個性能計(jì)數(shù)器: SQLServer:SQL Statistics"Batch Requests/Sec SQLServer:SQL Statistics" SQLCompilations/sec SQLServer:SQL Statistics" SQL Re-Compilations/sec 。如果 compilations 數(shù)目超過 batch 請求數(shù)目的百分之 10 ,或者 recompilations 數(shù)目超過 compilations 數(shù)目的百分之 10 ,那基本可以說明 cpu 消耗了太多在編譯查詢計(jì)劃上面。

??? 最后,我的建議是:
????1、DB中的所有操作都盡可能的使用存儲過程,哪怕只是一句簡單的select。
??? 2、鄙視拼SQL。

btw:MSDN中對拼SQL稱為"ad hoc",呵呵。

==================
補(bǔ)充一點(diǎn),說明一下N'@p1 nvarchar(6)'換成N'@p1 nvarchar(30)'會重新編譯:)。
程序代碼如下:

1 //
2 SqlCommand?cmd? = ? new ?SqlCommand( " select?*?from?myt?where?data?=?@d " ,?conn);
3 cmd.Parameters.Add( new ?SqlParameter( " @d " ,? " 1234567890 " ));
4 cmd.ExecuteNonQuery();
5
6 cmd? = ? new ?SqlCommand( " select?*?from?myt?where?data?=?@d " ,?conn);
7 cmd.Parameters.Add( new ?SqlParameter( " @d " ,? " 123 " ));
8 cmd.ExecuteNonQuery();
9


執(zhí)行完這段程序,可以觀察觀察sys.syscacheobjects:
寫有效率的SQL查詢(V)
???
上圖中的5、6行標(biāo)記了緩存的查詢計(jì)劃。
=======

另外,再來說個更應(yīng)該注意的地方:

?1 //
?2 SqlCommand?cmd? = ? new ?SqlCommand( " select?*?from?myt?where?data?=?@d " ,?con);
?3 cmd.Parameters.Add( new ?SqlParameter( " @d " ,? " 1234567890 " ));
?4 cmd.ExecuteNonQuery();
?5
?6 cmd? = ? new ?SqlCommand( " select?*?from?myt?where?data?=?@d " ,?con);
?7 cmd.Parameters.Add( new ?SqlParameter( " @d " ,? " 123 " ));
?8 cmd.ExecuteNonQuery();
?9
10 cmd? = ? new ?SqlCommand( " select?*?from?myt?where?data?=?@a " ,?con);
11 cmd.Parameters.Add( new ?SqlParameter( " @a " ,? " 123 " ));
12 cmd.ExecuteNonQuery();
13

注意,上述代碼中最后一次操作我把@d參數(shù)重命名成了@a,然后再來看看sys.syscacheobjects里面有啥:
寫有效率的SQL查詢(V)

注意第六行。

================
稍微提一下“簡單參數(shù)化”(SQL2k中稱為自動參數(shù)化)和“強(qiáng)制參數(shù)化”。在簡單參數(shù)化下,SQL會試圖參數(shù)化你的語句,以減少查詢計(jì)劃編譯和重編譯,但是可以被參數(shù)化的語句非常有限。這個東東可以通過一條簡單的insert語句測試到,偶就不貼圖了。簡單參數(shù)化是SQLServer的默認(rèn)行為。

強(qiáng)制參數(shù)化可以通過設(shè)置庫的屬性PARAMETERIZATION為FORCED實(shí)現(xiàn)。強(qiáng)制參數(shù)化會在很大程度上參數(shù)化你的語句。但是它有很多的限制(見MSDN)。

但是要注意,由于查詢計(jì)劃不會有兩種和兩種以上的副本,所以SQL可能會選擇一個不合適的計(jì)劃來執(zhí)行你的查詢。這也是偶一再的說,如果你的輸入?yún)?shù)引起選擇性劇烈變化,最好指定recompile選項(xiàng)的原因。
0
0
(請您對文章做出評價)

寫有效率的SQL查詢(V)


更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯(lián)系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發(fā)表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 久久精品国产福利 | 国产精品欧美亚洲韩国日本不卡 | 日本3p视频在线看高清 | 久久久久久久久影院 | 羞羞视频免费观看网站 | 成人精品视频一区二区在线 | 97se色在在线视频 | 成年女人午夜毛片免费看 | 国产精品欧美在线观看 | 在线亚洲一区二区 | 婷婷狠狠 | 亚洲人成在线观看 | 性做久久久久久蜜桃花 | 久久影院视频 | 四虎影院最新入口 | 久久精品成人一区二区三区 | 国产一区日韩二区欧美三 | 四虎影视亚洲精品 | 亚洲综合国产一区二区三区 | 伦理不卡 | 久久久在线视频 | 亚州精品永久观看视频 | 国产精品久久久久精 | 欧美高清在线视频一区二区 | 日本精品不卡 | 久久久久四虎国产精品 | 日本中文字幕一区二区有码在线 | 国产毛片视频 | 久久一区二区精品综合 | 亚洲欧洲国产成人精品 | 擼擼色在线看观看免费 | 国产欧美高清 | 伊人五月在线 | 国产精品美女一级在线观看 | 亚洲性夜夜综合久久麻豆 | 国产青草亚洲香蕉精品久久 | 日本大片久久久高清免费看 | 国产日韩亚洲欧洲一区二区三区 | 久草热草 | 久久久99精品免费观看 | 狠狠色综合网 |