第一篇：SQL語句性能優(yōu)化

我也做了很長時間醫(yī)療軟件，也寫過不少sql優(yōu)化，沒有詳細記錄下來，個人感覺下面轉載的更符合醫(yī)院醫(yī)療軟件實際業(yè)務，很認可大部分所寫的原則，固轉載過來，以作借鑒。軟件的根本還是在于更細更精，在于從客戶的實際使用考慮問題。

性能優(yōu)化原則1:永遠避免困境

利用緩存把字典數(shù)據(jù)取到中間服務器或是客戶端替代直接sql查詢,如,門診醫(yī)生站把字典下載到客戶端,減少執(zhí)行次數(shù)。

一次性取數(shù)據(jù)到客戶端，然后再逐條處理，而不是分次取數(shù)據(jù)，處理好一條數(shù)據(jù)再取下一條再處理。例：門診收費取hjcfmxk例子，原來是一張?zhí)幏綏l明細都查詢一次，查詢后再處理，現(xiàn)改為一次把所有明細都取過來，然后一條條處理

盡量減少光標，看能不能用臨時表

性能優(yōu)化原則2:kiss原則

對于where 條件中的左邊可以利用索引的字段Keep it simple stupid，左邊盡量避免用函數(shù)(substring,isnull,upper,lower)，參加計算+,-*/

例子1：select * from ZY_BRFYMXK where substring(zxrq,1,8)='20081212‘

select * from ZY_BRFYMXK where zxrq between '2008121200' and '2008121224' 例子2：

select * from zy_detail_charge where SUBSTRING(patient_id,1,10)=

substring('000005090600',1,10)這句耗時30秒以上

select * from zy_detail_charge where patient_id like substring('000005090600',1,10)+'%' 這句耗時2秒以內(nèi)

性能優(yōu)化原則3:盡可能利用到索引

例：select * from ZY_BRFYMXK a(nolock),VW_LSYZK b(nolock)where a.syxh=3 and a.yzxh=b.xh and a.fylb=0

select * from ZY_BRFYMXK a(nolock),VW_LSYZK b(nolock)where a.syxh=3 and a.yzxh=b.xh and a.fylb=0 and b.syxh=3

性能優(yōu)化原則4:or,避而遠之

對于索引字段盡力避免用or，普通字段可以用or，解決要么分解成多個sql,要么用業(yè)務規(guī)則避免，例：declare @rq1 ut_rq16,@syxh ut_syxh

select @rq1='20081201'

select @syxh=157

性能優(yōu)化原則5:避免大批量數(shù)據(jù)取到前臺

例： select * from ZY_BRSYK cyrq between ‘20080901’ and ‘20081201‘,對于大醫(yī)院每天100多人，90天是9000條數(shù)據(jù)

性能優(yōu)化原則6:事務,盡可能的短吧

所有計算、對臨時表的更新都應但放在事務外，事務中最好只有更新和插入正式表操作.因為事務中產(chǎn)生的鎖只有在commit tran是才會釋放。

性能優(yōu)化原則7:熱表，留在最后吧

熱表是頻繁調(diào)用的表。如：sf_mzcfk,zy_brfymxk,bq_fyqqk.對于熱表盡量放在事務最后：這樣鎖的時間短。大家都堅持這樣，死鎖的可能性就小。如果都是熱表各個存儲過程更新表的順序應當一樣這樣可以避免死鎖

性能優(yōu)化原則8:創(chuàng)建臨時表一定要避免在事務中作

如create #tempXX(…)

Select * into #tempXX from …

因為創(chuàng)建臨時表會鎖tempdb的系統(tǒng)表

例：生成#temp1放在事務內(nèi)外，用sp_lock2 ‘’觀察結果

if object_id('tempdb..#temp1','U')is not null

drop table #temp1

begin tran

select * into #temp1 from ZY_BRSYK where ryrq>'20080901‘

select * from #temp1

waitfor delay '00:00:10'

commit

性能優(yōu)化原則9:大的報表查詢避免與正常業(yè)務碰撞

如果沒有查詢服務器，那要在存儲過程中限制不能操作加上如：

declare @rq1 ut_rq16,@rq2 ut_rq16,@now ut_rq16

select @rq1=convert(varchar(8),getdate(),112)+'08:00:00'

select @rq1=convert(varchar(8),getdate(),112)+'11:30:00'

select @now=convert(char(8),getdate(),112)+convert(char(8),getdate(),8)

if @now>@rq1 and @now<@rq2

begin

select '上午繁忙時間段不能作此查詢'

return

end

性能優(yōu)化原則10:存儲過程避免大的if…else…

這個常出項在業(yè)務相同表不同的存儲過程中，因為這樣常到if …else …原來醫(yī)技接口中很多這種存儲過程，當時把門診住院業(yè)務放在一個存儲過程中。這樣最大的問題是sql server會根據(jù)sql語句來compile存儲，這個過程會生成優(yōu)化計劃，決定用那個索引。如果存儲過程用到門診表compile一下，到用到住院表是發(fā)現(xiàn)不對，又會compile一下，這樣不停compile.compile很號時間要1-2秒，而且一個存儲過成在compile是，所有調(diào)用這個存儲過程的進程都要在排隊等候,因為他會獨占鎖這個存儲過程

例：usp_yjjk_getwzxxm_old.sql,后改為：

usp_yjjk_getwzxxm.sql, usp_yjjk_getwzxxm_mz.sql,usp_yjjk_getwzxxm_zy.sql

性能優(yōu)化原則11:進攻是最好的防守

在普通編程語句對于數(shù)據(jù)校驗總是用防守辦法先判斷,后再作相應處理。而在sql中先處理再判斷性能會好很多。

--更新藥品庫存。

If exists(select 1 from YK_YKZKC WHERE idm=100 and kcsl>50)

begin

update YK_YKZKC set kcsl=kcsl-50 where idm=100

End

Else begin

rollback tran

Select ‘F庫存不夠’

return

end

--改為

update YK_YKZKC set kcsl=kcsl-50 where idm=100 and kcsl>50

If @@rowcount<=0

Begin

Rollbakc tran

Select ‘F庫存不夠’

end

--取未執(zhí)行的醫(yī)技項目,日表沒有數(shù)據(jù)就到年表中查找

if exists(select a.* from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

begin

select a.* into #temp1 from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

end

else begin

select a.* into #temp1 from SF_NMZCFK a(nolock),SF_NCFMXK b(nolock)

end

--改為

Insert into #temp1 select a.*

from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

If @@rowcount=0

Begin

Insert into #temp1 select a.*

from SF_NMZCFK a(nolock),SF_NCFMXK b(nolock)

end

性能優(yōu)化原則12:trig最后的手段

Trig（觸發(fā)器）的處理的處理機制是滿足條件時就會在源語句后面加上trig中的代碼進行執(zhí)行。

它有兩個致命的弊端：（1）不清楚有trig的人會對于執(zhí)行結果感到迷惑。如常有在插入一張表如果主鍵是indentity的值常取用select @@identity。但如是有trig,tring中有表插入操作，這時的@@identity可能就不是想要的值。（2）trig會束縛選擇。如：有一套單據(jù)主表和明細表，當明細表的金額更新時，要同步主表的金額，當程序是一條條更新明細時用trig的作法是每當更新一條明細記錄時都算一處所有明細表的總金額，再去更新主表的金額。這樣有多少條明細就要算多少次，好的作法是不要trig,直接在sql語句中明細更新完明后，一次性算出總金額每條單據(jù)的總金額，再更新主表的金額。

對于trig如果有其他手段就一定要避免用trig.性能優(yōu)化原則13:用戶說好才是真的好

1）有時sql語句性能難以優(yōu)化，但用戶對于系統(tǒng)響應速度還是不滿意。這時可以從業(yè)務分析處理。

如：我們退費模塊錄入發(fā)票號原來是用fph like ‘XXX%’。用戶報怨慢，后來改為先用fph=‘XXX’來查，如查不到再fph like ‘XXX%’。這樣在絕大部情況下速度都非?？?，同時也滿足小部分情況下模糊查詢的需求。

如：我們的程序要查日表和年表。如果通過日表union表視圖去查會非常慢，性能也難以優(yōu)化。程序改為普通情況下不查年表，用戶勾上年表標志時才查年表。

（2）查詢統(tǒng)計很多數(shù)據(jù)時間比較長，就以查詢完一部分數(shù)據(jù)后可以顯示這部分數(shù)據(jù)或是用提示，這樣用戶清楚系統(tǒng)在作事情也知道大概進度。這樣情緒上會好很多。

（3）查詢模塊常有一進入時也默認一個查詢，如果性能好，查詢又合用戶心意，這種設計非常好，如果性能不好，那就不是好的設計。用戶對于進入都困難的模塊是沒有好感的。

（4）有戶的耐心與查詢出的記錄成正比。用戶痛恨等待很久卻沒有查詢出記錄。

對于非常慢的查詢，如果有些子查詢非?？炜梢韵茸鬟@樣查詢以避免查詢很久卻沒有數(shù)據(jù)出來的情況。如：按病歷號查在院病人所有費有明細，可以先查一下這個病歷是不是有對應病人。

實戰(zhàn)技巧1:用exists、in代替distinct

Distinct實際上是先收集再刪除這樣兩步都耗資源。

Exists,in會隱式過濾掉重復的記錄

例查自2009年以來有金額大于100的藥品的病人

select distinct a.blh,a.hzxm from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock)where a.patid=b.patid and b.syxh=c.syxh and c.zxrq>'2009' and c.zje>100--改為

select a.blh,a.hzxm from ZY_BRXXK a where exists(select 1 from ZY_BRSYK

b(nolock),ZY_BRFYMXK c(nolock)where a.patid=b.patid and b.syxh=c.syxh and

c.zxrq>'2009'and c.zje>100)

實戰(zhàn)技巧2:縮短union

select …from A,B,C,D,E1

where(E1的條件)

and(其他表聯(lián)接條件)

union

select …from A,B,C,D,E2

where(E2的條件)

and(其他表接接條件)

改為

select …from A,B,C,D,(select...from E1where(E1條件)

union

select …from E2where(E2條件))E where(其他條件)

當涉及ABCD表部分耗資源而E1,E2不耗資源時是這樣，如果反過來則改后的性能不一定好。查2009年4月后入院的在院病人在2905病區(qū)發(fā)生的所有費用明細

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),YK_YPCDMLK d where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.idm=d.idm

union all

select a.hzxm,b.cyrq,d.name,d.xmgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),YY_SFXXMK d where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.ypdm=d.id and c.idm=0

--改為

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),(select ypmc,ypgg,ypdm,idm idm from YK_YPCDMLK union select name,xmgg,id,0 from YY_SFXXMK)d

where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.idm=d.idm and c.ypdm=d.ypdm

實戰(zhàn)技巧3:合并sql

把表和where條件類似的兩個或是多個sql合并為一個sql.--查2009年以后的普通、急診、專家掛號人數(shù)

declare @ptghs int,@jzghs int,@zjghs int

select @ptghs=0,@jzghs=0,@zjghs=0

select @ptghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=0

select @jzghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=1

select @zjghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=2

select @ptghs,@jzghs,@zjghs

--改為

select @ptghs=0,@jzghs=0,@zjghs=0

select @ptghs=sum(case when ghlb=0 then 1 else 0 end),@jzghs=sum(case when ghlb=1 then 1 else 0 end), @zjghs=sum(case when ghlb=2 then 1 else 0 end)

from GH_GHZDK where ghrq>'2009'

select @ptghs,@jzghs,@zjghs

實戰(zhàn)技巧4:去掉游標

把游標當作編程語言的for,do---while的方式，很多情況下都可以去掉,如果光標中間sql語句只有一條一般都是可以去掉光標改為一句sql。

--查當天出院出院日期在2009年4月1到9日間病人的zfdj,zfje置為0

declare @syxh ut_syxh

declare cur1 cursor for select syxh from ZY_BRSYK where cyrq>='20090401' and cyrq<'20090410'

open cur1

fetch cur1 into @syxh

while @@fetch_status=0

begin

fetch cur1 into @syxh

end

close cur1

deallocate cur1

--改為

update ZY_BRFYMXK set zfdj=0,zfje=0

from ZY_BRFYMXK a,ZY_BRSYK b

where a.syxh=b.syxh and b.cyrq>='20090401' and b.cyrq<'20090410'

實戰(zhàn)技巧5:取代count

利用內(nèi)部函數(shù)代替

declare @count int

select * into #tmep1 from ZY_BRFYMXK WHERE zxrq>'200901'

select @count=@@rowcount—可以得到count值

select @count

select @count=count(*)from #tmep1—可以被取代

select @count

利用exists而不count判斷有沒有記錄

declare @count int

Select @count=count(1)from ZY_BRFYMXK WHERE zxrq>'2009‘

If @count>0 … else ….--改為

If exists(Select 1 from ZY_BRFYMXK WHERE zxrq>'2009’)… else ….

第二篇：LoadRunner測試SQL語句性能

本次通過loadRunner錄制SQLServer介紹一下如何測試一個sql語句或存儲過程的執(zhí)行性能。主要分如下幾個步驟完成：

第一步、測試準備

第二步、配置ODBC數(shù)據(jù)源

第三步、錄制SQL語句在Sql Server查詢分析器中的運行過程

第四步、優(yōu)化錄制腳本，設置事務

第五步、改變查詢數(shù)量級查看SQL語句的性能

第六步、在controller中運行腳本

下面開始具體的介紹：

測試準備階段我們首先要確認測試數(shù)據(jù)庫服務器：我們可以在本地安裝SQL SERVER數(shù)據(jù)庫服務端及客戶端，也可以確定一臺裝好的SQL SERVER服務器。

接下來，準備測試數(shù)據(jù)：對數(shù)據(jù)庫測試時我們要考慮的不是SQL語句是否能夠正確執(zhí)行，而是在某數(shù)量級的情況下SQL語句的執(zhí)行效率及數(shù)據(jù)庫服務的運行情況，所以我們分別準備不同數(shù)量級的測試數(shù)據(jù)，即根據(jù)實際的業(yè)務情況預估數(shù)據(jù)庫中的記錄數(shù)，在本次講解中我們不考慮業(yè)務邏輯也不考慮數(shù)據(jù)表之間的關系，我們只建立一張表，并向此表中加入不同數(shù)量級的數(shù)據(jù)，如分別加入1000條、10000條、50000條、100000條數(shù)據(jù)查看某SQL語句的執(zhí)行效率。在查詢分析器中運行如下腳本：

--創(chuàng)建測試數(shù)據(jù)庫

create database loadrunner_test;

use loadrunner_test

--創(chuàng)建測試數(shù)據(jù)表

create table test_table

(username varchar(50),sex int,age int,address varchar(100),post int)

--通過一段程序插入不同數(shù)量級的記錄，具體的語法在這里就不多說了

declare@iint

set@i=0

while@i<1000//循環(huán)1000次，可以根據(jù)測試數(shù)據(jù)情況改變插入條數(shù)

begin

BEGIN TRAN T1

insert into test_table(username,sex,age,address,post)values('戶瑞海'+cast(@i as varchar),@i-1,@i+1,'北京市和平里'+cast(@i as varchar)+'號',123456);

IF @@ERROR <> 0

begin

rollback;

select @@error

end

else

begin

commit;

set@i=@i+1

end

end

好了，執(zhí)行完上述語句后，建立的數(shù)據(jù)表中已經(jīng)有1000條記錄了，下面進行第二步的操作，配置ODBC數(shù)據(jù)源，為了能讓loadrunner能夠通過ODBC協(xié)議連接到我們建立的SQL SERVER數(shù)據(jù)路，我們需要在本機上建立ODBC數(shù)據(jù)源，建立方法如下：

控制面板—性能和維護—管理工具—數(shù)據(jù)源（ODBC）--添加，在列表中選擇SQL SERVER點擊完成，根據(jù)向導輸入數(shù)據(jù)源名稱，鏈接的服務器，下一步，輸入鏈接數(shù)據(jù)庫的用戶名和密碼，更改鏈接的數(shù)據(jù)庫，完成ODBC的配置，如果配置正確的話，在最后一步點擊“測試數(shù)據(jù)源”，會彈出測試成功的提示。

配置好ODBC數(shù)據(jù)源后就要錄制SQL語句在查詢分析器中的執(zhí)行過程了：

1、打開loadrunner，選擇ODBC協(xié)議

2、在start recording中的application type 選擇win32 application；program to record中錄入SQL SERVER查詢分析器的路徑“..安裝目錄isqlw.exe”

3、開始錄制，首先通過查詢分析器登錄SQL SERVER，在打開的查詢分析器窗口中輸入要測試的SQL語句，如“select * from test_table;”

4、在查詢分析器中執(zhí)行該語句，執(zhí)行完成后，結束錄制

好了，現(xiàn)在就可以看到loadrunner生成的腳本了（由于腳本過長，在這里就不粘貼了，有需要的朋友可以加我QQ，我把腳本發(fā)給你們），通過這些語句，我們可以看出，登錄數(shù)據(jù)庫的過程、執(zhí)行SQL語句的過程。

接下來，我們來優(yōu)化腳本，我們分別為數(shù)據(jù)庫登錄部分和執(zhí)行SQL語句的部分加一個事物，在增加一個double的變量獲取事務執(zhí)行時間，簡單內(nèi)容如下：

Action()

{double trans_time;//定義一個double型變量用來保存事務執(zhí)行時間

lr_start_transaction(“sqserver_login”);//設置登錄事務的開始

lrd_init(&InitInfo, DBTypeVersion);//初始化鏈接（下面的都是loadrunner生成的腳本了，大家可以通過幫助查到每個函數(shù)的意思）

lrd_open_context(&Ctx1, LRD_DBTYPE_ODBC, 0, 0, 0);

lrd_db_option(Ctx1, OT_ODBC_OV_ODBC3, 0, 0);

lrd_alloc_connection(&Con1, LRD_DBTYPE_ODBC, Ctx1, 0 /*Unused*/, 0);

………………

trans_time=lr_get_transaction_duration(“sqserver_login”);//獲得登錄數(shù)據(jù)庫的時間lr_output_message(“sqserver_login事務耗時 %f 秒”, trans_time);//輸出該時間

lr_end_transaction(“sqserver_login”, LR_AUTO);//結束登錄事務

lr_start_transaction(“start_select”);//開始查詢事務

lrd_cancel(0, Csr2, 0 /*Unused*/, 0);

lrd_stmt(Csr2, “select * from test_table;rn”,-1, 1, 0 /*None*/, 0);//此句為執(zhí)行的SQL lrd_bind_cols(Csr2, BCInfo_D42, 0);

lrd_fetch(Csr2,-10, 1, 0, PrintRow24, 0);

……………..trans_time=lr_get_transaction_duration(“start_select”);//獲得該SQL的執(zhí)行時間

lr_output_message(“start_select事務耗時 %f 秒”, trans_time);//輸出該時間

lr_end_transaction(“start_select”, LR_AUTO);//結束查詢事務

優(yōu)化后，在執(zhí)行上述腳本后，就可以得到登錄到數(shù)據(jù)庫的時間及運行select * from test_table這條語句的時間了，當然我們也可以根據(jù)實際情況對該條語句進行參數(shù)化，可以測試多條語句的執(zhí)行時間，也可以將該語句改為調(diào)用存儲過程的語句來測試存儲過程的運行時間。

接下來把該腳本在controller中運行，設置虛擬用戶數(shù)，設置集合點，這些操作我就不說了，但是值得注意的是，沒有Mercury 授權的SQL SERVER用戶license，在運行該腳本時回報錯，提示“You do not have a license for this Vuser type.Please contact Mercury Interactive to renew your license.”我們公司窮啊買不起loadrunner，所以我也無法繼續(xù)試驗，希望有l(wèi)icense朋友們監(jiān)控一下運行結果！

最起碼在VUGen中運行該腳本我們可以得到任意一個SQL語句及存儲過程的執(zhí)行時間，如果我們測試的B/S結構的程序，我們也可以通過HTML協(xié)議錄制的腳本在CONTROLLER中監(jiān)控SQL SERVER服務器的性能情況，這樣兩方面結合起來就可以對數(shù)據(jù)庫性能做一個完整的監(jiān)控了。

第三篇：SQL語句的優(yōu)化方法

SQL語句的優(yōu)化方法

1.1注釋使用

在語句中多寫注釋，注釋不影響SQL語句的執(zhí)行效率。增加代碼的可讀性。

1.2對于事務的使用

盡量使事務處理達到最短，如果事務太長最好按功能將事務分開執(zhí)行(如：可以讓用戶在界面上多幾步操作)。事務太長很容易造成數(shù)據(jù)庫阻塞，用戶操作速度變慢或死機情況。

1.3對于與數(shù)據(jù)庫的交互

盡量減少與數(shù)據(jù)庫的交互次數(shù)。如果在前端程序寫有循球訪問數(shù)據(jù)庫操作，最好寫成將數(shù)據(jù)一次讀到前端再進行處理或者寫成存儲過程在數(shù)據(jù)庫端直接處理。

1.4對于SELECT *這樣的語句，不要使用SELECT *這樣的語句，而應該使用SELECT table1.column1這樣的語句，明確指出要查詢的列減少數(shù)據(jù)的通訊量并且這樣的代碼可讀性好，便于維護。

1.5盡量避免使用游標

它占用大量的資源。如果需要row-by-row地執(zhí)行，盡量采用非光標技術,如：在客戶端循環(huán)，用臨時表，Table變量，用子查詢，用Case語句等等。如果使用了游標，就要盡量避免在游標循環(huán)中再進行表連接的操作。

1.6盡量使用count(1)

count函數(shù)只有在統(tǒng)計表中所有行數(shù)時使用，而且count(1)比count(*)更有效率。

1.7IN和EXISTS

EXISTS要遠比IN的效率高。里面關系到full table scan和range scan。幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。

1.8注意表之間連接的數(shù)據(jù)類型

避免不同類型數(shù)據(jù)之間的連接。

1.9盡量少用視圖

對視圖操作比直接對表操作慢,可以用stored procedure來代替她。特別的是不要用視圖嵌套,嵌套視圖增加了尋找原始資料的難度。我們看視圖的本質(zhì)：它是存放在服務器上的被優(yōu)化好了的已經(jīng)產(chǎn)生了查詢規(guī)劃的SQL。對單個表檢索數(shù)據(jù)時，不要使用指向多個表的視圖，直接從表檢索或者僅僅包含這個表的視圖上讀，否則增加了不必要的開銷,查詢受到干擾。

1.10沒有必要時不要用DISTINCT和ORDER BY

這些動作可以改在客戶端執(zhí)行，它們增加了額外的開銷。

1.11避免相關子查詢

一個列的標簽同時在主查詢和where子句中的查詢中出現(xiàn)，那么很可能當主查詢中的列值改變之后，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。

1.1注意UNion和`UNion all 的區(qū)別

UNION all執(zhí)行效率高。

1.1外鍵關聯(lián)的列應該建立索引

(如子表id)主子表單據(jù)肯定要建視圖，2個表的關聯(lián)以2個表中的MainID為關系，所以，需要給子表的MainID單獨建索引，這將很大地提高視圖的速度。例如Gy_InOutSub中的InoutMainid增加索引。

第四篇：.Net+SQL Server企業(yè)應用性能優(yōu)化筆記3——SQL查詢語句

在上一篇文章中我們使用了幾種方法來確定瓶頸，找到瓶頸，下面再回顧一下：

LoadRunner壓力測試+Windows計數(shù)器，這種方法主要是找出大概的性能問題是在哪臺服務器，主要是哪個資源緊張。

? ANTS Profiler+SQL Server Profiler，這兩個工具的完美搭配可以準確的定位性能是出在哪個函數(shù)，哪個SQL語句上。?

如果性能問題是出在程序上，那么就要根據(jù)業(yè)務對程序中的函數(shù)進行調(diào)整，可能是函數(shù)中的寫法有問題，算法有問題，這種調(diào)整如果不能解決問題的話，那么 就要從架構上進行考慮，我們是不是應該使用這種技術，有沒有替代的方案來實現(xiàn)同樣的業(yè)務功能？舉個簡單的例子，假設經(jīng)過跟蹤發(fā)現(xiàn)，一個負責生成圖表的函數(shù) 存在性能問題，尤其是在壓力測試情況下性能問題尤為嚴重。原來的圖表生成是完全基于GDI+在Web服務器上根據(jù)數(shù)據(jù)進行復雜的繪圖，然后將繪出的圖片保 存在磁盤上，然后在HTML中添加Img標簽來引用圖片的地址?，F(xiàn)在使用GDI+會消耗大量內(nèi)存和CPU，而算法上也沒有太大的問題，那么這種情況下我們 就需要考慮修改架構，不使用GDI+ 繪圖的方式，或者是使用異步繪圖的方式。既然繪圖會消耗大量的服務器資源，那么一種解決辦法就是將繪圖的操作從服務器轉移到客戶端。使用 SilverLight技術，在用戶打開網(wǎng)頁是只是下載了一個SilverLight文件，該文件負責調(diào)用Web服務器的Web服務，將繪圖所需的數(shù)據(jù)獲 取下來，然后在客戶端繪圖展現(xiàn)出來。這樣服務器只提供WebService的數(shù)據(jù)訪問接口，不需要做繪圖操作。

.net上的優(yōu)化我暫時不表，今天主要講數(shù)據(jù)庫的優(yōu)化。使用ANTS Profiler+SQL Server Profiler我們可以精確定位某個業(yè)務操作對應的數(shù)據(jù)庫腳本或者存儲過程。ANTS Profiler告訴我們一個方法在調(diào)用的時候花了10秒的時間，那么我們就可以使用VS打開源代碼，找到該放入，然后找到對應調(diào)用的存儲過程，這里也許 一個方法里面調(diào)用了多個數(shù)據(jù)層方法，調(diào)用了多個存儲過程。將調(diào)用的這些存儲過程記下了，然后在SQL Server Provider的跟蹤文件里面去找調(diào)用該存儲過程花費的Duration。

據(jù)的時間

一般企業(yè)應用或小型應用中數(shù)據(jù)庫服務器和Web服務器要不是就在同一個機房，同一個局域網(wǎng)，或者干脆是同一臺機器，這種情況下網(wǎng)絡傳輸速度是很快 的，所以我們不考慮網(wǎng)絡傳送上面的時間。那么就得出：的存儲過程的Duration)

代碼中的時間得到了，SQL Server中的時間（也就是Duration字段）得到了，那么就可以判斷出打開該頁面各個服務器所花費的時間，從而找到我們要優(yōu)化的方向，是存儲過程 還是C#代碼。如果是存儲過程，那么通過查詢SQL Server Profiler中內(nèi)容可以找到具體是哪一個存儲過程消耗的時間最長。

“射人先射馬，擒賊先擒王。”多個存儲過程被調(diào)用，如果性能出在數(shù)據(jù)庫服務器上，那么進行性能優(yōu)化時首先要調(diào)優(yōu)的是最大Duration最大的存儲 過程，另外還有就是Reads很大的存儲過程。如果Duration很大但是Reads和Writes都不算特別大，那么有可能是以下原因：

1.這個存儲過程相關的資源正在被其他事務占用，也就是說該存儲過程被阻塞所以才花了那么多時間。這種情況只需要把該存儲過程提出，多執(zhí)行幾 次，看是不是仍然Duration很大但Reads不大。

2.存儲過程本身很復雜，里面的T-SQL語句就是五六百行，編譯出的執(zhí)行計劃也是一堆，里面進行了大量的邏輯判斷、大量函數(shù)的調(diào)用，這種情 況下進行調(diào)優(yōu)就比較痛苦了。實際上這次我調(diào)優(yōu)的這個項目就是如此，抓取出來的存儲過程盡是復雜的邏輯，少則兩三百行代碼，多則五六百行，里面還有大量的用 戶定義函數(shù)的調(diào)用。對于這種存儲過程，我接下來會專門寫篇博客介紹下我們這個項目是如何調(diào)優(yōu)的。

3.程序讀取的數(shù)據(jù)不多，但是需要對數(shù)據(jù)進行大量的運算。哈希聯(lián)接、聚合函數(shù)、DISTINCT、UNION等都是比較耗CPU的。如果是這 種情況那就看能不能建立索引或者改寫法進行調(diào)優(yōu)。

前面說的是Duration大而Reads小的情況，當然更常見的情況是Duration和Reads都很大。那么我們就將主要精力集中在如何減小 Reads上。造成Reads很多的原因大概有以下幾種：

1.沒有建立相應的索引。對表t1進行查詢，條件是where c2=abc返回c1,c2,c3三個字段，那么這種情況下如果沒有對c2建立非聚集索引（c1是主鍵，建立了聚集索引），那么這個查詢將會進行“聚集索 引掃描”，本來可能只查出幾條記錄的，結果要把表的所有記錄都掃描一篇，自然Reads就高了。解決辦法就是建立相應的索引，比如這里只需要對c2字段建 立非聚集索引，然后將c3字段作為包行列就行了。如果只是最c2字段建立非聚集索引，那么前面說到的查找在進行了“非聚集索引查找”后還會進行“鍵查找” 來找到c3列的值，所以要建立的正確的索引才行。

2.不符合SARG原則。查詢?nèi)绻环蟂ARG原則，那么即使建立了索引也沒法使用。SARG就是查詢參數(shù)的意思，具體怎么寫才符合 SARG，大家可以百度，已經(jīng)有很多相關文章了，我就不累述。

3.涉及的業(yè)務數(shù)據(jù)量大。也就是說即使建立了正確的索引，查詢也符合SARG使用到了該索引，但是由于涉及的數(shù)據(jù)量太大了，所以Reads仍 然很大。這種情況就不能再從索引和查詢?nèi)胧郑荒軓臄?shù)據(jù)庫的設計入手。是否能夠增加適當?shù)娜哂嘧侄危瑢?shù)據(jù)庫進行反范式化，或者如果數(shù)據(jù)的實時性要

求不 高的話則可以建立中間匯總表，使用SQL作業(yè)來維護這個中間匯總表，查詢的時候只查詢該中間匯總表即可。或者是否可以建立索引視圖或者計算列，然后在計算 列中建立索引的方式進行一個預運算，減小實際查詢時涉及的數(shù)據(jù)量。

4.使用了不當?shù)囊晥D。如果對視圖的定義很復雜，涉及的表很多，在查詢的時候使用了該視圖，但是實際上只用到了視圖中的一張或兩張表，對視圖 的查詢會造成系統(tǒng)根據(jù)視圖定義查詢其他與該查詢不相關的表。所以在使用視圖的時候一定要知道視圖的定義，不用貪圖一時的方便而隨便使用視圖。

5.不正確的使用了用戶定義函數(shù)。一個存儲過程中幾百行代碼，出于編寫方便，大量的調(diào)用了一個用戶定義表值函數(shù)，而該函數(shù)是進行了復雜的查詢 和運算才返回結果的。如果數(shù)次或者數(shù)十次的調(diào)用該用戶定義表值函數(shù)，那么就會進行很多這種復雜的查詢和運算，自然Reads也就很大了。解決辦法是盡量減 少對這種復制函數(shù)的調(diào)用，比如一次調(diào)用后就將解決保存在表變量或臨時表中，接下來再使用的話就使用該表變量或臨時表即可。

如果Duration并不大，但是Reads卻很大的查詢?nèi)匀恍枰枰M行優(yōu)化。雖然表現(xiàn)出來消耗的時間并不大，但是由于Reads很多，那么說明 要進行大量的IO，在高并發(fā)的情況下大量的IO處理不過來會加重磁盤的負擔，造成CPU占用率上升，性能降低，這時其Duration就會變大。關于 Duration不大但是Reads很大的情況仍然是前面說到的幾點情況，建立相關索引、修改查詢語句等便可解決。

第五篇：sql常用語句

//創(chuàng)建臨時表空間

create temporary tablespace test_temp

tempfile 'E:oracleproduct10.2.0oradatatestservertest_temp01.dbf'size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

//創(chuàng)建數(shù)據(jù)表空間

create tablespace test_data

logging

datafile 'E:oracleproduct10.2.0oradatatestservertest_data01.dbf'size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

//創(chuàng)建用戶并指定表空間

create user username identified by password

default tablespace test_data

temporary tablespace test_temp;

//給用戶授予權限

//一般用戶

grant connect,resource to username;

//系統(tǒng)權限

grant connect,dba,resource to username

//創(chuàng)建用戶

create user user01 identified by u01

//建表

create table test7272（id number(10),name varchar2(20),age number(4),joindate date default sysdate,primary key(id));

//存儲過程

//數(shù)據(jù)庫連接池

數(shù)據(jù)庫連接池負責分配、管理和釋放數(shù)據(jù)庫連接

//

//創(chuàng)建表空間

create tablespace thirdspace

datafile 'C:/Program Files/Oracle/thirdspace.dbf' size 10mautoextend on;

//創(chuàng)建用戶

create user binbin

identified by binbin

default tablespace firstspace

temporary tablespace temp;

//賦予權限

GRANT CONNECT, SYSDBA, RESOURCE to binbin

//null與""的區(qū)別

簡單點說null表示還沒new出對象，就是還沒開辟空間

個對象裝的是空字符串。

//建視圖

create view viewname

as

sql

//建索引

create index indexname on tablename(columnname)

//在表中增加一列

alter table tablename add columnname columntype

//刪除一列

alter table tablename drop columnname

//刪除表格內(nèi)容，表格結構不變

truncate table tableneme

//新增數(shù)據(jù)

insert into tablename()values()

//直接新增多條數(shù)據(jù)

insert into tablename()

selecte a,b,c

from tableabc

//更新數(shù)據(jù) new除了對象，但是這“”表示

update tablename set columnname=? where

//刪除數(shù)據(jù)

delete from tablename

where

//union語句

sql

union

sql

//case

case

when then

else

end