最近萬年議題又被拿出來討論了,「該把程式碼寫在 Stored Procedure,還是寫在 Application 裡面?」
⠀
以下是個人的看法以及選擇,僅供參考。
⠀
我個人的背景是這樣的,都是以開發 Web 應用程式為主,資料庫的使用經驗都在 SQL Server,其他的資料庫領域我不了解,在這樣的背景之下我個人傾向不使用 Stored Procedure 的,原因如下:
⠀
1. 對 DB 的操作已經是在應用程式生命週期的末端,這時候關注的應該是「效能」,如何運用最少的資源(CPU、Memory、Network IO、Disk IO、Money...)最快的速度拿到想要取得的資料,如果把程式碼都寫在 Stored Procedure,為了效能這件事,就會開始在商業邏輯中混入一些 Temp Table、額外定義 Table Type、因效能而使用的特殊語法...等,隨著時間流逝這個 Stored Procedure 就慢慢變成了哥吉拉。
⠀
2. 由於 SQL Server 的改進,Stored Procedure precompiled 的優點早已消失。
(https://www.codeproject.com/Articles/414272/Stored-Procedures-DO-NOT-increase-performance)
⠀
3. 被 Parameter Sniffing 搞得很煩。
⠀
4. 身為那條龍,我為何要將原始碼放在兩個地方?
⠀
雖然我不想使用 Stored Procedure 但不表示可以忽視查詢效能這件事,基本對 Index 的了解、各種 JOIN 差異、查詢計劃的解讀、...等,會影響查詢效能的要略懂,也順便跟還在跟哥吉拉奮戰的朋友說聲辛苦了。
同時也有1部Youtube影片,追蹤數超過8,320的網紅Campus TV, HKUSU 香港大學學生會校園電視,也在其Youtube影片中提到,週年大選提名期突然結束 同學未能遞表參選 As Annual Election Nomination Closed in a Sudden, Students Failed to Nominate Themselves 校園電視今日下午三時接到同學通知表示無法在學生會訂下的限期遞交本年度週年大選...
「stored procedures」的推薦目錄:
stored procedures 在 軟體廚房 Facebook 的最讚貼文
要用到時偏偏就忘記
.
Database Engine Tuning Advisor
https://dotblogs.com.tw/supershowwei/2017/03/09/144518
stored procedures 在 Campus TV, HKUSU 香港大學學生會校園電視 Youtube 的最佳解答
週年大選提名期突然結束 同學未能遞表參選
As Annual Election Nomination Closed in a Sudden, Students Failed to Nominate Themselves
校園電視今日下午三時接到同學通知表示無法在學生會訂下的限期遞交本年度週年大選提名表格,根據學生會室門外的告示,選委會所定提名期已經完結。暫時未知學生會室門外的告示何時張貼。
The Campus TV reporter received the news from students this afternoon at 3pm that the Annual Election Nomination is closed already, according to the notification outside the Union office. Many students failed to submit the nomination forms within the nomination deadline which is 12 am on Dec 31. Up to now, no information is given on when the notification outside the Union office was put up.
根據學生會憲章及選舉守則(According to the constitution and the election regulation of the HKUSU), Nomination for the next Union Session shall be opened on the first day of the First Semester, and the nominations shall be closed at 12 noon on any day which is at least four weeks before but not longer than six weeks before the Election Day.
然而,有關本年度選舉安排之評議會仍未召開,而本年度之選舉日以及本年度選舉規則仍亦未通過,所以未知學生會室門外之有關通知是否有效。
However, Annual Election Council Meeting concerning this year's Union Session has not yet been held. Neither have the regulations upon this year's Annual Election procedures and Election Day been passed. The validity of this notification of nomination deadline outside the Union office is subject to great doubts.
根據有意參選同學表示,學生會職工起初表示學生會室因除夕而已經關門,亦不清楚表格位置,其後在同學要求下協助致電學生會會長陳冠康,唯無人接聽,再過幾分鐘後該名職工便給予該位同學四張提名表格,但該同學亦無法遞交表格。
According to a student who wants to participate in the nomination, a staff of Student Union excused that the Union office should be closed on Dec 31st due to the public holiday and did not know where the nomination forms were stored. Demanded by the student, the staff contacted the Chairperson of Student Union, Dan Chan, yet with no one picking up the phone. The staff, after a while, gave the student four nomination forms, but, already exceeding the nomination deadline, the student could not submit the nomination form.
而校園電視記者到達學生會大樓看時曾見到學生會內務副會長張楚晞進入學生會室,但敲門後無人回應,而記者亦曾致電學生會會長陳冠康,評議會主席譚振聲,常務秘書柯文俊以及內務副會長張楚晞,均無人回應。而時事秘書連嘉欣則表示無法聯絡相關人士。
However, Internal Vice-chairperson, Ernest Cheung, was seen by Campus TV reporter entering the Student Union office today, yet he did not answer the door after continuous knocking by the reporter. Also, the reporter has made many urgent calls in attempt to contact Union Council Chairperson, Tam Chun Sing, General Secretary, Allen Or, and Internal Vice-chairperson Ernest Cheung, yet with no response at all. Meanwhile,Current Affairs Secretary, Bernice Lin, excused saying that she could not contact any responsible staff.
Translated by: Mandy Huang
Department of News, Campus TV, HKUSU, Session 2012
facebook.com/hkucampustv
stored procedures 在 Creating stored procedures - IBM's GitHub repository 的推薦與評價
Creating stored procedures¶. This article covers the basics of using Db2 Developer Extension to create a simple stored procedure and then explains some of ... ... <看更多>
stored procedures 在 What is a stored procedure? - Stack Overflow 的推薦與評價
... <看更多>
相關內容