Stored Procedures
先破一下對 SP 的迷思。
- 使用 SP 不會有 SQL injection,所以比較安全。
我的回應:
沒有這回事。不信你想拿多少份雞排跟我賭?
(雞:怎也是我輸好嗎!!!)
- SP 執行效能比較高。
我的回應:
SP 執行效能比一般 SQL 高,主因都是那個不能說的 M 什麼的 SQL Server 引起的都市傳說……
SP 比一般 SQL 快的唯一原因,是 SP 是早已經 compiled 放在 DB 內。而 SQL 要執行時要先做 parsing 工作。
但是,今天 CPU 速度很很高,對一般的 SQL statement 的 parsing 所需時間。在一般的 OLTP 可以安全地忽略不考慮。
另外重點,像 Oracle 這些比較正常的 RDBMS,會對常用的 SQL statement 的 parsing result 作 caching。在 Oracle 上,SP 和普通的 SQL 的執行速度是沒差別的。
小結論:
在99%的情況下,你不應該還使用 SP。
少量 SP 還有生存價值的空間1:
你想像一下,如果你要在一個 graph 找出二個點的 shortest path,然後你用上 dijkstra’s algorithm。
(dijkstra’s algorithm 不懂自己看 wiki,我這篇不是教演算法的)
如果你把 dijkstra’s algorithm 寫在 App. Server。
那麼,你每一個 iteration,你都要對 Node X 下 Query 找出其最短的 Edge。
如果最終你的 dijkstra’s algorithm 要跑100個 iteration,你就需要等待100個 network latency。
先說一下古人(謎之聲:誰?)的傳統智慧:
Computation near the data。
意思是:你的運算要盡可能地接近你的資料。
即是說:像這種你要每一個 iternation 都拿一點點資料。然後你很可能會跑很很多 iteration 的情況。你有2個方案來省下 network latency:
方案1:把整份資料在開始時從 DB 拿到 App. Server 的 Memory
方案2:把 dijkstra’s algorithm 以 SP 的方式,放到 RDBMS 上跑。
在20年前記憶體還是以 MB 來算的情況,方案1不是一個可行選擇,所以那時一律是走 SP 路線的。
而今天嘛,看你的資料量有多大,如果你的資料量很大搬不動,用 SP 直接在 RDBMS 上跑還是一個可以諒解的選擇。
少量 SP 還有生存價值的空間2:
現在你有一台機器,偶然會跑一些 batch job 的。沒跑 batch job 時這台機器是完全 idle 的。
現在你在資料庫有:
1 這台機器在 aws 的開機和關機時間。
2 每一個 batch job 的開始和結束時間。
你老闆現在想要你產生一份報表:這台機器在這個月的 idle time period
雖然,我有說過:SQL 是 set-based language,你不應該輕易用 for-loop 的。
而這例子,應該用 recursive query 還是可以一句地寫出來的。(謎之聲:你肯定?)
但是這例子……
我應該會把 machine start/end time 和 batch job start/end time 全都拿到 App. Server 再用2層 loop 來算好了。
這樣子應該比較直覺和少一些 recursive query 的潛在地雷
我只能說:
如果資料量少。你當然可以把全部資料都搬到 App. Server 來慢慢算。
但是,如果這是有大量商業邏輯的報表,而且 raw data 是 GB 級。
然後中間你其中一步,你沒法用簡單 Query 寫出來要用上 for-loop 自己來算……
在這些條件都不幸地滿足下:
把 GB 級的 raw data 從 DB 先搬到 App. Server,算好後再丟回 DB 是笨蛋主意。
這情況,也許是用 SP 直接在 RDBMS 上跑 looping,然後把結果丟到 Temp table,再作進一步的運算。
這樣子在得出結果前,Data 和一切運算都留在 RDBMS 上。(更大重點:同一台物理機器上)