A little IT experience/study/share garden

假設我們有一個文字檔, 要寫入 table: StList 中, 且 StList schema 如下:

文字檔型態一:

這個例子較簡單, 因此我們直接用下述語法:

文字檔型態二:

這個例子就比較麻煩了, 若直接用以下語法,

因為每個欄位的資料前後都有 double quote, 若直接用 "," 分開, 將造成"最前面"和"最後面"的double quote 拿不掉, 也就是會變成:

可改用以下程式處理:

T-SQL 裡的 Error Handle 比較少人提到, 其實在撰寫 stored procedure 可以幫助除錯, 也可以自訂回饋給前端的訊息.

基本語法:

.TRY 及 CATCH 區塊必須搭配使用。

.TRY 及 CATCH 區塊間不可有其他的 T-SQL

.在 TRY 區塊間的 T-SQL，若發生錯誤，則會跳至 CATCH 區塊執行，然後中斷；若沒有錯誤，則會跳過 CATCH 區塊，然後繼續其後的敍述。

錯誤處理, 可用函數:

int ERROR_LINE();    --傳回發生錯誤的行數

int ERROR_NUMBER();    --傳回系統錯誤代號

(早期的 MS SQL 版本,可用 @@ERROR)

nvarchar ERROR_MESSAGE();    --傳回系統錯誤訊息

int ERROR_SEVERITY();

ps: 這並非 Error 的嚴重程度, 只是標明 Error 的分類而已.

最後, 擷取自 MSDN 上的範例:

USE AdventureWorks;
GO

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

參考資料:

http://www.functionx.com/sqlserver/Lesson09.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHT/tsqlref9/html/50228f2f-6949-4d2e-8e43-fad11bf973ab.htm

原文: Linked Server performance tips

重點摘要:

透過linked server 所執行的遠端存取/交易，可能耗用大量的頻寬，所以在使用上，要注意能避免的overhead 要盡量避免。

當查詢裡包括遠端主機的資料時，用linked server 將比使用 OPENROWSET 或 OPENDATASOURCE 來得有效率。

當需要遠端主機回傳資料時，盡量讓資料先在遠端過濾完畢後再回傳；亦即，只取回必要的資料就好，以避免額外的網路傳輸。OPENQUERY 可作到這樣的效果。

當遠端 DB 與近端 DB 的 character set 與 sort order (collation) 相同時，可將 SP_SERVEROPTION "collation compatible"選項打開，可增加效能;尤其當使用 WHERE 時，若選項不開啓，則 MS SQL 必須將遠端所有資料傳輸至近端後才能比對；若開啓，則可在遠端比對。

遠端主機最好與近端主機接同一台 switch，或至少在同一個 subnet 裡。

當以 Enterprise Manager 或 Management Studio 建立一個 linked server 時，在 Server Option tab 裡，可以考慮設定 Connection Timeout 以及 Query Timeout 的值;預設值 0 表示不會 time out，設定該值可以幫助我們找出效能不彰的 Query 。

在預設的情形下， MS SQL 會將查詢盡量放在遠端，並只取回必要的資料， 但在下述情形下， MS SQL 可能必須先將所有資料取回：

• Data conversion operations (eg: cast/convert)
• 當用到 bit， timestamp， 或 uniqueidentifier 等資料型態
• 用到 "TOP" keyword
• INSERTS， UPDATES， 或 DELETES
• 所以最好避免執行上述的遠端查詢/交易

最後， 善用 Query Analyzer 查看 query plan， 以確保大多數的資料查詢是在遠端上執行，而非近端 DB。

原文 - http://www.sqlteam.com/article/temporary-tables

暫存表(Temporary Tables)

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

表格變數(Table Variables)

當我們使用 SQL Server 2000 或以後的版本, 則可以考慮使用 "Table Variables" (表格變數); 使用方式如下例:

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakName
FROM dbo.Yaks
WHERE YakType = 'Tibetan'

-- Do some stuff with the table

• 它和暫存表類似, 但它更加彈性, 且不會存在於tempdb 中(完全存在於記憶體).
• 使用完畢後, 不須手動去 drop它

兩者的使用時機

• 當暫存的資料筆數小於100筆時, 使用表格變數, 否則, 可使用暫存表, 因為針對表格變數, SQL Server 不會去解析/最佳化它的效能.
• 當我們須要對表格建立索引(Index)時, 則必須使用暫存表.
• 在使用暫存表時, 最好能在建立後一併建立索引, 這能增加效能 (SQL Server 2005後, 這方面已改善, 所以可以不建索引; 但建立它仍是一個好習慣)

全域暫存表(Global Temporary Tables)

在表格名字前面, 加入兩個"#", 比如"##YakHerders", 則表示這是個全域暫存表, 也就是說, 這個表格和一般表格一樣, 可以被所有連線(connections/sessions)使用; 在SQL Server 中, 這樣的應用並不多見.

原文 - http://www.sqlteam.com/article/sql-server-versions

Dirty Read (以下簡稱DR)在MS SQL 的Help 裡解釋如下:
"第二筆交易選擇的資料列已經被其他交易更新時，會發生未確認依存性 (Uncommitted Dependency)。此時，第二筆交易讀取的是尚未認可且可能被更新資料列的交易變更之資料。

例如，有一位編輯人員正在修改一份電子文件。進行變更時，第二位編輯人員複製了包含目前所有變更的文件，並將文件散發給預期的讀者。此時，第一位編輯人員認為截至目前的變更均有誤，所以移除了原先的變更後儲存文件。散發的文件包含不存在的修改，但這些修改不應該存在。如果能在第一位編輯人員決定不再進行變更後才允許其他人員讀取修改過的文件，即可避免這個問題。"

坦白說, 我一向看不太懂這些詏口的翻譯文, 不過大致可以推斷出, 當 DR 發生時, 表示我們的select 的資料, "可能"是錯的.

想對MS SQL 的鎖定機制有進一步認識的人, 可以參考這一篇

為了避免這種錯誤發生, 我們只有運用lock 的機制去避免, 但這往往必須用系統效能的降低來交換, 只不過, 在一個設計良好的系統中, 這種兩難可以達成一個平衡(trade-off balance).

但如果是發生在一個已經每天都滿載甚至超載(overload)的MS SQL 資料庫上呢? 我們會發現, 由於大量的Lock, 將會導致系統始終遊走於崩潰邊緣!

我在 sql-server-performance.com 這個網站上翻閱了一些文章, 其中這篇寫得不錯; 作者針對上述問題(row lock/page lock/table lock/...), 提供了兩點方案:
1) turn option "NOLOCK" on while SELECT
2) turn option "ROWLOCK" on while UPDATE and DELETE

關於"NOLOCK"及"ROWLOCK", 在此不再詳述, MSDN 裡都翻得到.

由於這種方法可能會造成DR, 筆者也特別說明, 進行重要的交易(如會計結帳), 不可用此方案.

以一個 ERP 而言, 絕大多數(往往超過50%)的查詢或報表, 並不一定需要"最及時"(updated)的資料; 如果我要查目前的庫存量, 那麼30秒前的庫存, 和目前的庫存, 即使有所差異, 那差異量和整月的交易量比起來也是微乎其微; 諸如此類的select statement, 其實是可以忽略 DR 的風險! 如果我們對這些 sql statement 加上 "NOLOCK"及"ROWLOCK", 可以使 DB 的效能超死回生, 那麼何妨一試!?

曾與人爭論"資料正確性"與"效能", 孰重孰輕!? 理論上, 提供100% 的資料正確性是MIS 的天職, 但實務上若遇到設計不佳的 DB 存取方法, 常造成使用者操作時要忍受無窮無盡的等待, 三不五時的"作業逾時",換算成全公司浪費的人力, 絕對比DR 的成本更加驚人 (何況前者是"一定會有"的顯性成本, 後者只是潛在成本),遑論使用者對系統的抗拒感造成日後的怒氣爆發! MIS 宜從多個角度思考何者為佳(對公司最好), 而非固執地抱著理論不放.