| 導購 | 订阅 | 在线投稿
分享
 
 
 

當數據積累到一定時間後執行會越來越慢

來源:互聯網網民  2008-07-26 07:41:55  評論

最近很長一段時間都在優化一個項目,這個項目存在許多問題,從數據架構到工作流程,我一直在思考一些有關oracle性能優化的問題,我跟開發實施人員進行過多次交流,發現存在許多交流障礙,許多問題實施人員覺得sql語句執行很快,並不存在性能問題,我花了大量時間要他看執行計劃,說明當數據積累到一定時間後,會執行越來越慢。

我舉一個例子來說明問題:

SELECT v2.*

FROM consultationrecorddoctor t1

RIGHT OUTER JOIN

(SELECT cr.*, crc.modifydate, crc.modifyuserid, crc.consultationtime,

crc.applyconsultationdeptid, crc.askconsultationdeptid,

crc.consultationdeptid, crc.casehistory_right,

crc.consultationorder_right, crc.consultationidea_right,

crc.otherhospital_right, crc.consultationdate,

crc.maindoctorid, c1.deptname_vchr AS applydeptname,

c2.deptname_vchr AS askdeptname,

c3.deptname_vchr AS deptname,

f_getempnamebyno (crc.maindoctorid) AS maindocname

FROM consultationrecord cr,

consultationrecordcontent crc,

t_bse_deptdesc c1,

t_bse_deptdesc c2,

t_bse_deptdesc c3,

(SELECT cr.inpatientid, cr.inpatientdate, cr.opendate,

cr.createdate,

MAX (crc.modifydate) AS maxmodifydate

FROM consultationrecord cr,

consultationrecordcontent crc

WHERE (crc.applyconsultationdeptid = '0000208')

AND cr.inpatientid = crc.inpatientid

AND cr.inpatientdate = crc.inpatientdate

AND cr.opendate = crc.opendate

AND cr.status = 0

GROUP BY cr.inpatientid,

cr.inpatientdate,

cr.opendate,

cr.createdate) v1

WHERE (crc.applyconsultationdeptid = '0000208')

AND cr.inpatientid = crc.inpatientid

AND cr.inpatientdate = crc.inpatientdate

AND cr.opendate = crc.opendate

AND cr.status = 0

AND crc.applyconsultationdeptid = c1.deptid_chr

AND crc.askconsultationdeptid = c2.deptid_chr

AND crc.consultationdeptid = c3.deptid_chr

AND cr.inpatientid = v1.inpatientid

AND cr.inpatientdate = v1.inpatientdate

AND cr.opendate = v1.opendate

AND v1.maxmodifydate = crc.modifydate

AND crc.maindoctorid IS NOT NULL) v2

ON t1.inpatientid = v2.inpatientid

AND t1.inpatientdate = v2.inpatientdate

AND t1.opendate = v2.opendate

AND t1.modifydate = v2.modifydate

AND t1.employeeflag = 1

WHERE t1.employeeid IS NULL

ORDER BY t1.employeeid DESC, v2.askdeptname, v2.consultationdate;

查詢使用了一個右連接,查詢的表並沒有包含t1表的字段,而且在實際的t1.employeeid中

根本不存在NULL的值,實際上是從一個大的結果集剔除一個大結果集的而獲得一個小的結

果集,隨著crc表數據量加大,會越來越慢!

 
特别声明:以上内容(如有图片或视频亦包括在内)为网络用户发布,本站仅提供信息存储服务。
 
最近很長一段時間都在優化一個項目,這個項目存在許多問題,從數據架構到工作流程,我一直在思考一些有關oracle性能優化的問題,我跟開發實施人員進行過多次交流,發現存在許多交流障礙,許多問題實施人員覺得sql語句執行很快,並不存在性能問題,我花了大量時間要他看執行計劃,說明當數據積累到一定時間後,會執行越來越慢。 我舉一個例子來說明問題: SELECT v2.* FROM consultationrecorddoctor t1 RIGHT OUTER JOIN (SELECT cr.*, crc.modifydate, crc.modifyuserid, crc.consultationtime, crc.applyconsultationdeptid, crc.askconsultationdeptid, crc.consultationdeptid, crc.casehistory_right, crc.consultationorder_right, crc.consultationidea_right, crc.otherhospital_right, crc.consultationdate, crc.maindoctorid, c1.deptname_vchr AS applydeptname, c2.deptname_vchr AS askdeptname, c3.deptname_vchr AS deptname, f_getempnamebyno (crc.maindoctorid) AS maindocname FROM consultationrecord cr, consultationrecordcontent crc, t_bse_deptdesc c1, t_bse_deptdesc c2, t_bse_deptdesc c3, (SELECT cr.inpatientid, cr.inpatientdate, cr.opendate, cr.createdate, MAX (crc.modifydate) AS maxmodifydate FROM consultationrecord cr, consultationrecordcontent crc WHERE (crc.applyconsultationdeptid = '0000208') AND cr.inpatientid = crc.inpatientid AND cr.inpatientdate = crc.inpatientdate AND cr.opendate = crc.opendate AND cr.status = 0 GROUP BY cr.inpatientid, cr.inpatientdate, cr.opendate, cr.createdate) v1 WHERE (crc.applyconsultationdeptid = '0000208') AND cr.inpatientid = crc.inpatientid AND cr.inpatientdate = crc.inpatientdate AND cr.opendate = crc.opendate AND cr.status = 0 AND crc.applyconsultationdeptid = c1.deptid_chr AND crc.askconsultationdeptid = c2.deptid_chr AND crc.consultationdeptid = c3.deptid_chr AND cr.inpatientid = v1.inpatientid AND cr.inpatientdate = v1.inpatientdate AND cr.opendate = v1.opendate AND v1.maxmodifydate = crc.modifydate AND crc.maindoctorid IS NOT NULL) v2 ON t1.inpatientid = v2.inpatientid AND t1.inpatientdate = v2.inpatientdate AND t1.opendate = v2.opendate AND t1.modifydate = v2.modifydate AND t1.employeeflag = 1 WHERE t1.employeeid IS NULL ORDER BY t1.employeeid DESC, v2.askdeptname, v2.consultationdate; 查詢使用了一個右連接,查詢的表並沒有包含t1表的字段,而且在實際的t1.employeeid中 根本不存在NULL的值,實際上是從一個大的結果集剔除一個大結果集的而獲得一個小的結 果集,隨著crc表數據量加大,會越來越慢!
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有