Two Database Tips
4 |
#別用浮點數算錢 Don't use float in currency calculation
DECLARE @f1 FLOAT, @f2 FLOAT
DECLARE @f3 FLOAT, @f4 FLOAT
SET @f1=6481.45
SET @f2=6000
SET @f3=0.1
SET @f4=(@f1-@f2)*@f3
SELECT (6481.45-6000)*0.1 AS V1
SELECT @f4 AS V2
SELECT ROUND(@f4, 2) AS V3
SELECT 48.145 - @f4 AS V4
又到了猜迷時間,V1=48.145 V2=48.145 所以V3=ROUND(@f4, 2)=ROUND(48.145, 2)=48.15?? 錯!! V3會等於48.14,由V4的結果來看,@f4其實等於48.144999999999979,只是在顯示時會被視為48.145,但到了計算四捨五入時,卻被判定成<48.145而四捨五入成48.14。
總論: 要計算到分亳不差,乖乖用DECIMAL吧,別再用FLOAT了。
#別小看資料庫統計不準的殺傷力 Watch out for out-of-date index statistics
--Super Fast
SELECT Col1 FROM Table1
WHERE Col2 IN (
SELECT Col2 FROM Table2
WHERE Col3 LIKE 'Blah'
)
--Be Patient... ZZZzzzz
SELECT Col1 FROM Table1
WHERE Col2 IN (
SELECT Col2 FROM Table2
WHERE Col3 LIKE 'Blah%'
)
在我的認知裡,在有設Index的前題下,LIKE 'Blah'與LIKE 'Blah%'的效能差異不大,不慎使用LIKE '%Blah'才會導致Table Scan效能不佳。但今天遇到一個例子,用得好好的ORACLE查詢程式今天突然查不出資料來。追蹤下去,發現問題出在上述的SQL。LIKE 'Blah'只需1秒,LIKE 'Blah%'卻等上半個小時還看不到結果。DBA偵斷結果: 這個高達2G大小的Table大久沒做統計更新了,才搞出這樣的飛機。
結論: 千萬記得定期為資料庫更新統計資料!
Comments
# by steve
曾經遇到過一個情形 SQL Server已經設定自動更新統計資料 卻發現上次更新時間是一個月前 後來DBA找到,SQL好像資料異動超過20%才會更新統計資料 但是1000萬筆資料的20%就有200萬 導致查詢出問題,只好手動更新 只是後來沒找到這個20%藏在哪裡,不知道能不能改
# by Jeffrey
To steve, 這次遇到問題是發生在ORACLE,已屬番邦的領域,我就沒再深究。至於你說到20%的數字,我第一次聽到,結果剛才Google到了,SQL 2000的說明在http://support.microsoft.com/kb/195565,該篇KB的適用對象似乎不包含2005,因此SQL 2005的可參考 http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
# by steve
感謝 A statistics object is considered out of date in the following cases: • If the statistics is defined on a regular table, it is out of date if: 1. The table size has gone from 0 to >0 rows. 2. The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then. 3. The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered. • If the statistics object is defined on a temporary table, it is out of date as discussed above, except that there is an additional threshold for recomputation at 6 rows, with a test otherwise identical to test 2 in the previous list. 看來2005的規則是差不多的 只是2000算 row changed, 2005算column changed 至於那20%的數字能不能改小一點,還沒看到 看英文真累.... 我又不是DBA,為什麼要替DBA操心阿......QQ
# by Ryan
這問題挺奇妙, SQL 2005 那個20%並非沒道理的, 以統計的觀點來看, 如果少於20%被更動, 那影響統計結果應該不大, 這時候查詢起來的速度也應該不至於有重大差異呀! 如果確定statistics自動更新沒有被關閉, 又希望確保統計資料維持最新狀態, 也許可以請DBA安排Maintenance Plan, 執行Update Statistics Task, 排程則視需要而定. 這件事情並非額外的管理任務, 原本為了performance考量就該趁系統空檔予以安排, 根據我精密的觀察, 大多數的DBA只是把maintenance plan當作備份排程而已. 我個人感覺SQL 2005與前版最大理念上的不同, 是要縮減DBA與Programmer之間的灰色地帶, 以前都是DBA玩自己的rountine jobs, programmer寫自己密密麻麻的codes, 各自把自己的事情做完就對了, 日子好過也天天過, 至於database performance有問題, 一定是對方的錯. SQL 2005把管理跟開發的介面整合在一起, 就是要努力消除這種藩籬. 不過這也不是水到渠成之事, 畢竟各據山頭已久, 聚嘯扯旗早就劃清界線, 手上的權力不願意分享, 不干我權責的事情也懶的撈過界, performance的問題交給user去擔心就好. 我想, 這個問題反映的其實是面對新的technology, 有沒有新的IT管理思維與人力培養的難題, 挑戰著工程師的自身定位, 也挑戰著IT主管的管理智慧.