iT邦幫忙

DAY 6
4

ASP.NET Web技術小技巧分享系列 第 6

[MS SQL]將多筆資料合併欄位,減少不必要的連線

之所以會有這篇文章,是在改寫公司老舊的ASP報表
主要的邏輯是撈出全部資料後,逐筆判斷該資料在另一個Table是否有相似的資料
如果有則Response出來,秀在此筆資料的下方,但如此簡單的邏輯,卻要Run很久才能跑出來
仔細看他的寫法是這樣,我以C#改寫,以使用者登入紀錄為例,我建了兩個資料表:

當然這裡舉例的不是那麼恰當,登入紀錄應該是記錄到時分秒單位
重點是在下面這段程式:

string connString = WebConfigurationManager.ConnectionStrings["TestDBconn"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
 
string Sql_User = "select * from [User]";
SqlDataAdapter da = new SqlDataAdapter(Sql_User, conn);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        //依使用者撈出登入紀錄
        string Sql_Login = "select * from LoginRecord where UserID = " + dt.Rows[i]["sn"].ToString();
        da = new SqlDataAdapter(Sql_Login, conn);
        DataTable dt_login = new DataTable();
        da.Fill(dt_login);
        if (dt_login.Rows.Count > 0)
        {
            for (int k = 0; k < dt_login.Rows.Count; k++)
            {
                /* 
                 * Show Login Data . . . . .
                 */
            }
        }
    }
}

乍看之下結果沒什麼問題,但很可怕的是逐筆連線去執行select
因如果資料可能上千上萬,甚至DB是用 Link Server的方式,
原本連線要兩秒這樣加下來就會Run到天荒地老
接著就會換來使用者的一句,程式怎麼寫那麼爛Q_Q
所以將這支程式做一點改寫,使用SQL的for XML Path語法

現在已經把該使用者登入紀錄全篩到一個新的欄位LoginData,
但這樣還是不太好閱讀,故我們用一些小技巧,把它轉為varchar
並用逗號分隔,再用Substring去除開頭的逗號:

如此已經完成抓出每個使用者的登入紀錄了,
接下來只要把這個子查詢,加入主表裡就改寫成功了:

經過這樣改寫已經把連線此數降為1次,
接下來只需要在程式裡用Split切割逗號,去秀出要的資料,
這種寫法應該是程式老手不太會犯的錯誤,
活生生的案例在實務上遇到了,也許一開始資料量小沒care這麼多
但還是養成code review的習慣以免害到後人阿!


上一篇
[ASP.NET]點選下載PDF,跳出下載視窗
下一篇
[Jquery]使用Cycle Plugin輕鬆做出輪播功能
系列文
ASP.NET Web技術小技巧分享27
0
summertw
iT邦好手 1 級 ‧ 2013-09-24 10:27:41

這是一個不錯的分享..
這份資料的比喻,要說它是二維,又好像比二維還要複雜一點...
要說它是三維資料,又覺得它沒有三維的完整...
那就它的2.5維好了...

把2.5維的資料完全併成二維來處理,算是個好典範。
...
不過這裡樓主用C#寫的前端,好像是寫WebForm用的程式,那那那..可能就不會是一件Good的事囉...
如果能將【string Sql_User = "select * from [User]";及 string Sql_Login = "select * from LoginRecord where UserID = " + dt.Rows[i]["sn"].ToString();】這行改寫成SQL Procedure,那程式的隱藏性會更佳,你的端程式會更精簡的...

謝謝你們讚美,我承認舉例不是那麼恰當..Orz
標題有說是維護舊有系統遇到的實例,及解決方式,寫到SP裡當然是個好辦法,可將商業邏輯跟資料邏輯拆開,但那時候,我們程式設計師是沒有那麼大的權限能碰DB(雖然這是很奇怪的事但還是遇到了..),所以就不能考慮SP了,如果硬要程式端的話,這個範例有兩個缺點:1.沒注意Sql Injection 2.應該將商業邏輯再抽出來(MVC架構),程式會更簡潔,也更容易維護

謝謝您的提醒,讓我發現程式有些缺點可能會誤導別人觀念,但我這邊想表達的是寫程式要注意與DB的連線方式 ^_^

0
summertw
iT邦好手 1 級 ‧ 2013-10-01 22:16:04

我們程式設計師是沒有那麼大的權限能碰DB(雖然這是很奇怪的事但還是遇到了..)
確實很怪,也很不妥..
直接開放遠端的Web程式使用到資料表的Select權限,本就已實際佈下了Sql Injection的風險,那要從外部引發伺服器的Buffer溢位是隨時會發生的事...

若如你所說【我們程式設計師是沒有那麼大的權限能碰DB】,那就不是你們的問題,而是資料庫端規劃的人該死,居然會把資料庫最基本的風險給暴露了來,不過台灣這種該死的人還真是一堆啊...

種花電信裡就有一堆...

程式員本來就不該直接碰觸資料庫深層的部份(比如 SP)

開發程式有個 Developer 環境 是很基本的要求

以前我都要求程式員,除了 Select 這種不改變資料內容的指令可以自己寫在程式碼中(不過也盡量鼓勵寫成 SP)
其餘會改到資料庫的指令(如 Delete, Insert DROP 等),一律要寫成 Stored Procedure, 並存成 Script 檔交給我,再放入 Production 的 Database 中

我不給程式員有 Production 中 Stored Procedure 的新增/修改權 , 所以如果他們沒給出 Script 檔,一更新版本,程式自然報錯,有類似的報錯,次數多了,我就會給他口頭警告

而我也不定期的去檢查程式碼 , 如果有哪個人不使用 SP , 而直接在程式碼中使用 Delete , Insert 等等關鍵字,也會給與警告

我要留言

立即登入留言