Linq時代的分頁使用關係 (C#)

       在早期，我們在完成一個網頁表單會使用DataGird這個物件來實現，而DataGird這個物件本身也就支援著分頁的功能了。不過，我們應該都知道DataGird實現分頁的方式就是將資料透過ViewState保留後，再從中篩選要顯示的資料給User來檢閱，也因此在大量資料下，除了會造成資料庫負擔外，更會因為ViewState過大的關係，使得網頁停止回應，導致整個掛掉。

      所以我們會利用資料篩選的方式，只透過回應少量且必要的資料來減低資料庫負擔並提高效能，而通常會透過一連串的SQL語法去達成，至今亦是如此。

      在SQL2000之前，我們可能會透過cursor的方式叫用sp_cursoropen及sp_cursorfetch來達成，而在SQL2005之後多了ROW_NUMBER()這個函數，讓我們方便替每筆資料列做編號，之後只要透過where語法去取用需要的編號範圍，即可達成分頁。

      例如我們有一語法取得資料表的幣別種類並替它編號
SELECT ROW_NUMBER() OVER (ORDER BY [Currency]) AS [ROW_NUMBER], [Currency], [CurrencyName] FROM [dbo].[Currency]
      結果大概長這樣

    如果我們打算將它分成兩頁並取第一頁前5筆時

select * from
( SELECT top 5 ROW_NUMBER() OVER (ORDER BY [Currency]) AS [ROW_NUMBER], [Currency], [ CurrencyName ] FROM [dbo].[Currency] )  AS   a
where [ROW_NUMBER] > 0
    結果自然長這樣

     而至於我為什麼要講那麼多SQL呢，是因為使用Linq有著更令人一目瞭然的寫法，如：
var data = (from i in db.Currency select i).Skip(0).Take(5).ToList();

    好了....解決了(註:Skip是略過幾筆，Take是取得幾筆)，當你使用SQL Profiler時會發現，Linq會自動幫你轉成類似語法，來達到資料分頁，不過這其實不是啥了不起的事...，因為這已經好幾年前的舊聞了，我這裡會講這個，主要只是要表達使用Linq後，在背後的執行狀況。

    在VS2005還是VS2008時，DataGird被捨棄了，取而代之的是GridView，而GridView可以搭配著各種DataSource容器，只要指定好DataSourceID，並在畫面上拖拖拉拉後，再將AllowPaging打開，分頁輕輕鬆鬆便做好了！而且更重要的是，透過Profiler去追蹤，你會發現DataSource傳送的SQL語法就是剛才所使用的ROW_NUMBER，簡直棒透了不是？

    這當然也不是新聞。

   問題在於一點，GridView必需搭配DataSource容器才得以自動完成[資料分頁]，但是在分散式專案的架構下，想單單靠著指定DataSourceID來完成表單似乎有點難度，故在這情況下只好又做回原來的GridView.DataSource&DataBind()來製作表單，但不知道是不是由於DataSourceID太好用的關係吧?又或著有些人認為GridView搭配Linq會聰明的自己作分頁..?之類的，你可能會看到如下程式碼：

public void Page_Load(object sender, EventArgs e)
  {
     GridView1.DataSource = getData();
     GridView1.DataBind();
  }
  public IEnumerable<TBX> getData()
  {
     var data = from i in DB.Table select i;
     return data;
  }
        然後在介面上設定AllowPaging=true


        這是一個不知道該說什麼好的一件事，似乎會以為這樣就會替你完成[資料分頁]?那當然是不可能的...，GirdView並沒有你想像的聰明，它只會將資料全數取回後送給GridView1，存放在GridView1的ViewState裡，緊接著在裡頭進行著分頁動作，就又回到N年前的DataGird問題裡(即使你用別人開發的控件亦同)。

        要記得一個大原則，只要透過DataSource&DataBind()就代表你放棄好用的工具，所以所有功能你都得自己寫！！

       是故，請好好利用Skip和Take進行分頁吧。

        另外在Google時發現一篇文章，文章裡問道，為何我用Skip&Take後，出來的語法和想像的不大一樣？

       其實這問題很簡單，因為你下的就是告訴它我要先Skip(跳過)再Take(取用)，所以出來的資料截取方式當然是要先等資料讀過後，才知道要where和Top哪邊，如果要像此文章裡的想法下，只Top一定數再where範圍的話...。

       只要反過來先Take再Skip就好囉！分頁程式如下：

public List<TheTable> ddd(int PageSize, int PageCount)
{
     Conn db = new Conn();
     var data = from i in conn.TheTable  select i;
     var Take = (PageSize * PageCount);
     var Skip = ( (PageCount - 1) * PageSize);
     return data.Take(Take).Skip(Skip).ToList();
}
之後只要去取用就好囉

public void Button1_Click(object sender, EventArgs e)
{
 GridView1.DataSource = getAList(GridView1.PageSize, int.Parse(PageCount.Text));
 GridView1.DataBind();
}


不過這個方法沒有詳細Debug，防呆做好的話理論上應該不會有問題....吧？
(甚至你想先Take再Take說不定也行?)

2012/4/19:
關於Take Skip，查執行計劃，效率上似乎一樣。

網站效能的追源溯本(Linq)

Linq真好用(廢話)

Linq搭配LinqToxxx or Entity的結合根本好用到不行

用不著寫一堆連線方案，也不用擔心SQL Injection，也不用因為寫SQL語句時，造成的一堆"字"亂七八糟的難維護，甚至連SQL都不用學了。

真是好用的東西

事不宜遲，就來寫個輸入一筆訂單，若存在符合訂單則修改數量，不存在則新增一筆，超過一筆以上則保留數量最多的並刪除其它多餘資料的標準Linq語句吧。

            var dbLink = new db();
            var catchOrder = from o in dbLink.OrderList
                             where o.OrderID == "J001"
                             select o;
            if (catchOrder.Count() == 0)
            {
                var newOrder = new OrderList();
                newOrder.OrderID = "J001";
                newOrder.OrderName = "新訂單01";
                newOrder.Quantity = 10;
                dbLink.OrderList.InsertOnSubmit(newOrder);
            }
            if (catchOrder.Count() == 1)
            {
                foreach (var upd in catchOrder)
                {
                    upd.Quantity = 11;
                }
            }
            if (catchOrder.Count() >= 2)
            {
                var MaxQuantity = catchOrder.Max(m => m.Quantity);
                var delSomeThing = from d in catchOrder
                                   where d < MaxQuantity
                                   select d;
                foreach (var delIt in catchOrder)
                {
                    dbLink.OrderList.DeleteOnSubmit(delIt);
                }
            }
            dbLink.SubmitChanges();

我們姑且不論是什麼情況會有這麼詭異的需求...那不是重點。

基本上類似這樣子的寫法應該很常見，但這語法裡有什麼問題存在?相信應該很容易知道。

Linq因為它是一個你要它做時，它才會動的東西，所以即使你在最開頭寫好了select from....，它並不會做任何事，只是放在那裡，等你找它做事，當只有在count、max....xxx等語句出現或foreach它時，才會與資料做連繫，所以在這一個簡單判斷的語法裡，總共至少需要叫資料庫做至少5~7次以上的動作，會不會太浪費資源了一點？

catchOrder.Count()catchOrder.Max()catchOrder.XXX()太好用了，又容易看懂，如果你覺得這沒什麼，結果瘋狂使用它，去試著想用的人多一點時，會發生什麼事吧？一個人是7次，1000個人就是7千次，千萬不要因為好用而濫用。

     

            var dbLink = new db();
            var catchOrder = from o in dbLink.OrderList
                             where o.OrderID == "J001"
                             select o;
            var bakOrder = catchOrder.ToList();
            int useCount = bakOrder.Count();
            if (useCount == 0)
            {
                var newOrder = new OrderList();
                newOrder.OrderID = "J001";
                newOrder.OrderName = "???01";
                newOrder.Quantity = 10;
                dbLink.OrderList.InsertOnSubmit(newOrder);
            }
            if (useCount == 1)
            {
                foreach (var upd in catchOrder)
                {
                    upd.Quantity = 11;
                }
            }
            if (useCount >= 2)
            {
                var MaxQuantity = bakOrder.Max(m => m.Quantity);
                var delSomeThing = from d in catchOrder
                                   where d < MaxQuantity
                                   select d;
                foreach (var delIt in catchOrder)
                {
                    dbLink.OrderList.DeleteOnSubmit(delIt);
                }
            }
            dbLink.SubmitChanges();

這邊做簡單的調整(但不代表最好，因為更好的有很多，這邊只是減少對資料庫溝通而已)
利用ToList先行將資料取得到記憶體中，接著計取Count保留，若有需要時亦用來計取Max，如此一來對資料庫要求的次數則從最少5~7次降為最少2次，如此便減少與資料庫之間來往，理論上而言效能應是比較好的。(實際情況請依狀況而定)

在撰寫時，最好搭配SQL Profiler瞭解你的Linq到底幹了什麼事。

網站效能的追源溯本(資料庫)

當你的網站慢了的時後，你第一時間會想什麼？

1.伺服器出問題?(IIS掛了?mem爆了?網域解析又出錯了? )
2.這支程式誰寫的?(又是xxx，快把他抓來罵)
3.大概是人太多了
4.大概是資料太多了
5.....
諸如此類的想法

如果這時候好死不死給上頭的知道了，一聲怒下，你就得開始狂查原因，翻遍所有程式，看遍所有server的設定，或狂打電話給中華電信，結果仍百思不得其解，最後上網GOOGLE，找到了許許多多網站調校的方法和建議，這當中往往都是叫你改程式，不然就再安裝新軟體，結果成本就又冒出一堆來了，只好冒死晉見老闆說要改程式(買軟體)，然後被老闆白眼一頓後才核準，最後當你如願以償之後，卻發現上頭的還是跟你說一聲"喂!XXX怎麼還是那麼慢?"，你可能會哭死

在你花時間改程式，加設備，搬資料，調整javascript,JPG,...,各種延遲載入,修改各介面的Response方式....諸如此類耗時費神的動作前,花個5~20分鐘重新看一下最根本的東西吧

資料庫

基本上我要講的對很多人而言是廢話，但卻又是最容易讓人遺忘的基本，資料庫是讓人塞資料用的，要塞資料一定會佔空間啊，所以你當時在建資料庫時給它多少空間?快滿時的成長空間又是多少?想想你從系統剛上線時到現在的大小差了多少?由此你就會知道你的資料存在硬碟裡到底有多散了...，越散當然越慢啊。

再來你的log檔(ldf)和資料庫檔(mdf)是不是放在同一個實體硬碟?如果是，把管資料庫的抓來痛揍一頓吧。

有無使用檔案群組(file group)功能?如果沒有，趕快用吧；如果有...麻煩看一下用到哪去了?你確定真的有正確使用?

多久沒重建你的索引了?

多久沒"完整"備份你的資料庫了?

最基本的基本...你用什麼東西當你的索引?都是varchar?甚至前面多個n? or table時不時有一堆null?別以為有些人建議varchar和char執行速度沒啥差別,null和非null也可省好多空間...當你增刪修改個N次後，你就又會知道你的資料到底又有多散了...

SQL的服務沒用到的裝了一堆?如MSSQL的Reporting Service之類的有真正用到再裝吧。

還有偶偶錄一下Profiler餵給Database Engine Tuning Advisor看看有啥要改善的吧

等到這些最基本的確定沒問題了，再去思考那些複雜的問題吧，因為資料破碎這種事，就跟燒開水一樣，你不燒它，你就沒水喝，你不燒它，你就沒電用(因為從那個放風箏被電到的人發現電以來，我們發電的方式依舊是燒開水)，你不去注意破碎，你永遠被罵。

噓:回資料太多的都是廢話...除非你的DB每個都不知道多少個G...而且又同在一個instance

分享複雜的問題的參考資料:
DB:
網站效能分析操作心法-第8回-從資料庫來調校
網站效能分析操作心法-第9回-從資料庫來調校
OTHER:
快速揪出網站效能不佳的罪魁禍首