sql優化:開發人員應該要細心

客戶反映

執行某個報表要45秒以上

select id, role_id, login
                          from tb_user e
                         where role_id in (Select ID
                                  From tb_role f
                                  Connect By Prior Id = parent_id
                                 Start With Id = 1);

直接拿

以上語句到資料庫上執行只要 2秒鐘左右

那現在問題只能定位到開發人員寫的程式

檢查sql

select id, role_id, login
                          from tb_user e
                         where role_id in (Select ID
                                  From tb_role f

                                where e.role_id=f.id
                                  Connect By Prior Id = parent_id
                                 Start With Id = 1);

多了紅色部分,去掉

再次執行恢復到2秒正常

以前該sql 是用exists 的寫法

select id, role_id, login
                          from tb_user e
                         where exists  (Select ID
                                  From tb_role f

                                where e.role_id=f.id
                                  Connect By Prior Id = parent_id
                                 Start With Id = 1);

再改寫成用in時開發人員忘記去掉紅色程式碼部分導致執行時間過長

所以開發人員要細心,做為dba要更加嚴格稽核sql才可提交出去

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7199859/viewspace-238016/，如需轉載，請註明出處，否則將追究法律責任。