醫(yī)院數(shù)據(jù)庫(kù)系統(tǒng)SQL語(yǔ)句優(yōu)化

select top 1CFSB from MS_CF01 where FYRQ>=”2004.3.1 00:00:00”?
//得到該時(shí)間段內(nèi)最小的CFSB,例如3198724
select top 1CFSB from MS_CF01 where FYRQ<=”2004.3.30 00:00:00”?
order by CFSB desc //得到該時(shí)間段內(nèi)最大的CFSB，例為4178763
select sum(MS_CF02.YPSL*MS_CF02.YPDJ*MS_CF02.CFTS) as total
from MS_CF01,MS_CF02
where MS_CF01.CFSB=MS_CF02.CFSB and MS_CF01.CFLX=1
and MS_CF02.CFSB>=3198724 and MS_CF02.CFSB<=4178763
and (MS_CF01.FYBZ=1 or MS_CF01.FYBZ=3)
and MS_CF01.ZFPB=0

所有語(yǔ)句執(zhí)行完畢后，結(jié)果不超過(guò)18秒。

3.2案例分析二

醫(yī)院工作人員反映在藥庫(kù)系統(tǒng)自動(dòng)計(jì)劃模塊執(zhí)行速度很慢，有時(shí)大約需要半個(gè)小時(shí)才能跳出窗口。

經(jīng)過(guò)分析發(fā)現(xiàn),在w_yk_plan.cb_auto.clicked() 18行開(kāi)始的代碼如下：
ls_select+=" select YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL,0 "
ls_select+=" from YK_TYPK,YK_KCMX "
ls_select+=" where XTSB="+string(base_info.syscode)+" and DCSL>0 and GCSL>DCSL and YKZF=0 "
ls_select+=" and YK_TYPK.YPXH not in (select YPXH from YK_KCMX) "
ls_select+=" group by YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL "

顯然是NOT IN語(yǔ)句導(dǎo)致速度很慢,然后把該語(yǔ)句改成:
ls_select+=" select YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL,0 "
ls_select+=" from YK_TYPK "
ls_select+=" where XTSB="+string(base_info.syscode)+" and DCSL>0 and GCSL>DCSL and YKZF=0 "
ls_select+=" and? not exists ( select YPXH FROM YK_KCMX WHERE? YK_TYPK.YPXH = YK_KCMX.YPXH ) "
ls_select+=" group by YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL “

結(jié)果5秒內(nèi)執(zhí)行完成。

3.3案例分析三

以下SQL的功能是醫(yī)院用于作日?qǐng)?bào)時(shí)顯示哪些收費(fèi)員還沒(méi)有做過(guò)當(dāng)天的個(gè)人日?qǐng)?bào)，速度非常慢，每次都需要好幾分鐘才能出來(lái)數(shù)據(jù)。

顯然這個(gè)問(wèn)題是由于不合理的子查詢?cè)斐傻模?jīng)分析后我們把子查詢從where子句中去除，語(yǔ)句改成如下后，2秒內(nèi)數(shù)據(jù)庫(kù)出來(lái)了。

某些HIS系統(tǒng)中病區(qū)系統(tǒng)中醫(yī)囑提交、醫(yī)囑執(zhí)行、醫(yī)技提交速度慢問(wèn)題原因分析ZY_BQYZ 這個(gè)表中出院數(shù)據(jù)沒(méi)有轉(zhuǎn)出到ZY_BQYZ_CY 表中。

沒(méi)有建立正確的索引，SQL 語(yǔ)句本身存在的問(wèn)題，在醫(yī)囑提交中的WHERE子句：
WHERE ( ZY_BQYZ.ZYH = ZY_BRRY.ZYH AND ZY_BRRY.CYPB = 0 )
AND ( ZY_BQYZ.SRKS = :al_hsql)
AND ( ZY_BQYZ.LSBZ=0 OR ZY_BQYZ.LSBZ=2)
AND ( ZY_BQYZ.SYBZ = 0)
AND ( ZY_BQYZ.XMLX<4 )
AND ( ZY_BQYZ.YZPB=0 )
AND ( ZY_BQYZ.FYSX<2)?
AND ( ZY_BQYZ.JFBZ<2)
AND ( ZY_BQYZ.YSBZ = 0 OR (ZY_BQYZ.YSBZ = 1 AND ZY_BQYZ.YSTJ = 1))

在醫(yī)院執(zhí)行模塊的數(shù)據(jù)窗口SQL：
SELECT DISTINCT 　ZY_BQYZ.ZYH,ZY_BRRY.BRCH,ZY_BRRY.ZYHM,ZY_BRRY.BRXM,ZY_BRRY.BRXZ
FROM ZY_BQYZ, ZY_BRRY?
WHERE ( ZY_BQYZ.ZYH = ZY_BRRY.ZYH )
AND ( ZY_BRRY.CYPB = 0 )
AND ( ZY_BQYZ.SRKS = :al_hsql )
AND ( ZY_BQYZ.SYBZ = 0 )
AND ( ZY_BQYZ.LSBZ = 0 OR ZY_BQYZ.LSBZ = 2 )
AND ( ZY_BQYZ.QRSJ < :ad_today OR ( ZY_BQYZ.QRSJ IS NULL ) )
AND ( ZY_BQYZ.JFBZ = 2 OR ZY_BQYZ.JFBZ = 9 )
AND ( ZY_BQYZ.XMLX > 3 )
AND ( ZY_BQYZ.YZPB = 0 )
ORDER BY ZY_BRRY.BRCH

4小結(jié)

從以上這些例子可以看出，SQL優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識(shí)別的語(yǔ)句，充份利用索引，減少表掃描的1/O 次數(shù)，盡量避免表搜索的發(fā)生。其實(shí)SQL的性能優(yōu)化是一個(gè)復(fù)雜的過(guò)程，上述這些只是在SQL語(yǔ)句應(yīng)用時(shí)的一種具體實(shí)例和平時(shí)應(yīng)用中的總結(jié)。總之，數(shù)據(jù)庫(kù)的性能優(yōu)化是一個(gè)系統(tǒng)工程，涉及的方面很多。數(shù)據(jù)庫(kù)管理員需要綜合運(yùn)用多種方法，認(rèn)真分析數(shù)據(jù)庫(kù)運(yùn)行過(guò)程中出現(xiàn)的各種問(wèn)題，才能保證數(shù)據(jù)庫(kù)高效地運(yùn)行。

參考文獻(xiàn)：

[1] ?陳倩文.醫(yī)院數(shù)據(jù)庫(kù)系統(tǒng)的性能分析及優(yōu)化經(jīng)驗(yàn)[J].中國(guó)數(shù)字醫(yī)學(xué)，2009.4(8)：66-68

[2] ?馬辰.醫(yī)院信息系統(tǒng)數(shù)據(jù)庫(kù)的性能優(yōu)化和故障維修[J].醫(yī)療裝備,2009.6:69-70

[3] ?王勇.基于SQL 數(shù)據(jù)庫(kù)的性能優(yōu)化問(wèn)題分析[J]. 電腦知識(shí)與技術(shù),2009.15:1004-1007

[4] ?殷美桂.談關(guān)系數(shù)據(jù)庫(kù)查詢優(yōu)化方法[J].電腦知識(shí)與技術(shù),2008.16:1188-1189

[5] ?駱正云,宋念東,俞國(guó)霖.醫(yī)院HIS系統(tǒng)SQL SERVER數(shù)據(jù)庫(kù)性能優(yōu)化[J].醫(yī)療衛(wèi)生裝備,2005.26(2):32-33

[6] ?劉兆良,陳中舉,劉方.SQL SERVER數(shù)據(jù)庫(kù)查詢優(yōu)化技術(shù)[J].科技信息,2007.24:377-378

作者：上海長(zhǎng)海醫(yī)院 劉于惠；上海市浦東醫(yī)院 夏新

?