欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

[數(shù)據(jù)庫] 通用分頁存儲(chǔ)過程第3/5頁

 更新時(shí)間：2007年02月09日 00:00:00   作者：  

升序-降序

這個(gè)方法在子查詢中使用默認(rèn)排序，在主查詢中使用反向排序，原理是這樣的：

復(fù)制代碼 代碼如下:

DECLARE @temp TABLE ( 
    PK  /* PK Type */ NOT NULL PRIMARY  
) 

INSERT INTO @temp  
SELECT TOP @PageSize PK FROM ( 
    SELECT TOP (@StartRow + @PageSize)  
    PK,  
    SortColumn /*If sorting column is defferent from the PK, SortColumn must  
                 be fetched as well, otherwise just the PK is necessary */  
    ORDER BY SortColumn /* default order – typically ASC */)  
ORDER BY SortColumn /* reversed default order – typically DESC */ 

SELECT   FROM Table JOIN @Temp temp ON Table.PK = temp.PK  
ORDER BY SortColumn /* default order */ 

行計(jì)數(shù)

這個(gè)方法的基本邏輯依賴于SQL中的SET ROWCOUNT表達(dá)式，這樣可以跳過不必要的行并且獲得需要的行記錄

復(fù)制代碼 代碼如下:

DECLARE @Sort /* the type of the sorting column */ 
SET ROWCOUNT @StartRow 
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn 
SET ROWCOUNT @PageSize 
SELECT   FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn 

子查詢

還有兩個(gè)方法也是我考慮過的，他們的來源不同。第一個(gè)是眾所周知的三角查詢(Triple Query)或者說自查詢方法，我找的一個(gè)比較透徹的方法在下面的文章中有描述

SQL Server服務(wù)器端分頁

雖然你需要訂閱，但是可以下載一個(gè)包含子查詢存儲(chǔ)過程定義的zip文件。列表4 SELECT_WITH_PAGINGStoredProcedure.txt文件包含一個(gè)完整的通用的動(dòng)態(tài)SQL。在本文中，我也用一個(gè)類似的包含所有其他存儲(chǔ)過程的通用邏輯。這里的原理是連接到整個(gè)過程中，我對(duì)原始代碼做了一些縮減，因?yàn)閞ecordcount在我的測(cè)試中不需要）
復(fù)制代碼 代碼如下:

SELECT   FROM Table WHERE PK IN  
    (SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN 
        (SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn) 
    ORDER BY SortColumn) 
ORDER BY SortColumn 

游標(biāo)

在看google討論組的時(shí)候，我找到了最后一個(gè)方法，你可以點(diǎn)這里查看原始帖子。該方法是用了一個(gè)服務(wù)器端動(dòng)態(tài)游標(biāo)。許多人試圖避免使用游標(biāo)，因?yàn)橛螛?biāo)沒有關(guān)系可言，以及有序性導(dǎo)致其效率不高，但回過頭來看，分頁其實(shí)是一個(gè)有序的任務(wù)，無論你使用哪種方法，你都必須回到開始行記錄。在之前的方法中，先選擇所有在開始記錄之前的所有行，加上需要的行記錄，然后刪除所有之前的行。動(dòng)態(tài)游標(biāo)有一個(gè)FETCH RELATIVE選項(xiàng)可以完成魔法般的跳轉(zhuǎn)。基本的邏輯如下：
復(fù)制代碼 代碼如下:

DECLARE @PK /* PK Type */ 
DECLARE @tblPK TABLE ( 
    PK /* PK Type */ NOT NULL PRIMARY KEY 
) 

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR 
SELECT @PK FROM Table ORDER BY SortColumn 

OPEN PagingCursor 
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK 

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0 
BEGIN 
    INSERT @tblPK(PK) VALUES(@PK) 
    FETCH NEXT FROM PagingCursor INTO @PK 
    SET @PageSize = @PageSize - 1 
END 

CLOSE PagingCursor 
DEALLOCATE PagingCursor 

SELECT   FROM Table JOIN @tblPK temp ON Table.PK = temp.PK  
ORDER BY SortColumn 

復(fù)雜查詢的通用化

我在之前指出，所有的存儲(chǔ)過程都是用動(dòng)態(tài)SQL實(shí)現(xiàn)通用性的，因此，理論上它們可以用任何種類的復(fù)雜查詢。下面有一個(gè)基于Northwind數(shù)據(jù)庫的復(fù)雜查詢例子。
復(fù)制代碼 代碼如下:

SELECT Customers.ContactName AS Customer,  
       Customers.Address + ', ' + Customers.City + ', ' +  
                                                Customers.Country AS Address,  
       SUM([Order Details].UnitPrice*[Order Details].Quantity) AS  
                                                          [Total money spent]  
FROM Customers 
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID 
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico' 
GROUP BY Customers.ContactName, Customers.Address, Customers.City,  
         Customers.Country  
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000 
ORDER BY Customer DESC, Address DESC 
返回第二個(gè)頁面的分頁存儲(chǔ)調(diào)用如下： 
EXEC ProcedureName 
/* Tables */ 
'Customers 
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID', 
/* PK */ 
'Customers.CustomerID', 
/* ORDER BY */ 
'Customers.ContactName DESC, Customers.Address DESC', 
/* PageNumber */ 
2, 
/* Page Size */ 
10, 
/* Fields */ 
'Customers.ContactName AS Customer, 
Customers.Address + '', '' + Customers.City + '', '' + Customers.Country  
                                                                  AS Address,  
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS [Total money spent]', 
/* Filter */ 
'Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''', 
/*Group By*/ 
'Customers.CustomerID, Customers.ContactName, Customers.Address,  
Customers.City, Customers.Country  
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000' 

值得注意的是，在原始查詢中在ORDER BY語句中使用了別名，但你最好不要在分頁存儲(chǔ)過程中這么做，因?yàn)檫@樣的話跳過開始記錄之前的行是很消耗時(shí)間的。其實(shí)有很多種方法可以用于實(shí)現(xiàn)，但原則是不要在一開始把所有的字段包括進(jìn)去，而僅僅是包括主鍵列（等同于RowCount方法中的排序列），這樣可以加快任務(wù)完成速度。只有在請(qǐng)求頁中，才獲得所有需要的字段。并且，在最終查詢中不存在字段別名，在跳行查詢中，必須提前使用索引列。

行計(jì)數(shù)(RowCount)存儲(chǔ)過程有一個(gè)另外的問題，要實(shí)現(xiàn)通用化，在ORDER BY語句中只允許有一個(gè)列，這也是升序-降序方法和游標(biāo)方法的問題，雖然他們可以對(duì)幾個(gè)列進(jìn)行排序，但是必須保證主鍵中只有一個(gè)字段。我猜如果用更多的動(dòng)態(tài)SQL是可以解決這個(gè)問題的，但是在我看來這不是很值得。雖然這樣的情況很有可能發(fā)生，但他們發(fā)生的頻率不是很高。通常你可以用上面的原理也獨(dú)立的分頁存儲(chǔ)過程。
性能測(cè)試

在測(cè)試中，我使用了四種方法，如果你有更好的方法的話，我很有興趣知道。不管如何，我需要對(duì)這些方法進(jìn)行比較，并且評(píng)估它們的性能。首先我的第一個(gè)想法就是寫一個(gè)asp.net包含分頁DataGrid的測(cè)試應(yīng)用程序，然后測(cè)試頁面結(jié)果。當(dāng)然，這無法反映存儲(chǔ)過程的真實(shí)響應(yīng)時(shí)間，所以控制臺(tái)應(yīng)用程序顯得更加適合。我還加入了一個(gè)Web應(yīng)用程序，但不是為了性能測(cè)試，而是一個(gè)關(guān)于DataGrid自定義分頁和存儲(chǔ)過程一起工作的例子。這兩個(gè)應(yīng)用程序都可以在 Paging Test Solution中找到。

在測(cè)試中，我使用了一個(gè)自動(dòng)生成得大數(shù)據(jù)表，大概插入了500000條數(shù)據(jù)。如果你沒有一張這樣的表來做實(shí)驗(yàn)，你可以點(diǎn)擊這里下載一段用于生成數(shù)據(jù)的表設(shè)計(jì)和存儲(chǔ)過程腳本。我沒有使用一個(gè)自增的主鍵列，而是用一個(gè)唯一識(shí)別碼來識(shí)別記錄的。如果我使用上面提到的腳本，你可能會(huì)考慮在生成表之后添加一個(gè)自增列，這些自增數(shù)據(jù)會(huì)根據(jù)主鍵進(jìn)行數(shù)字排序，這也意味著你打算用一個(gè)帶有主鍵排序的分頁存儲(chǔ)過程來獲得當(dāng)前頁的數(shù)據(jù)。

為了實(shí)現(xiàn)性能測(cè)試，我是通過一個(gè)循環(huán)多次調(diào)用一個(gè)特定的存儲(chǔ)過程，然后計(jì)算平均相應(yīng)時(shí)間來實(shí)現(xiàn)的?？紤]到緩存的原因，為了更準(zhǔn)確地建模實(shí)際情況——同一頁面對(duì)于一個(gè)存儲(chǔ)過程的多次調(diào)用獲得數(shù)據(jù)的時(shí)間通常是不適合用來做評(píng)估的，因此，我們?cè)谡{(diào)用同一個(gè)存儲(chǔ)過程時(shí)，每一次調(diào)用所請(qǐng)求的頁碼應(yīng)該是隨機(jī)的。當(dāng)然，我們必須假設(shè)頁的數(shù)量是固定的，10-20頁，不同頁碼的數(shù)據(jù)可能被獲取很多次，但是是隨機(jī)獲取的。

有一點(diǎn)我們很容易注意到，相應(yīng)時(shí)間是由要獲取的頁數(shù)據(jù)相對(duì)于結(jié)果集開始的位置的距離決定的，越是遠(yuǎn)離結(jié)果集的開始位置，就有越多的記錄要跳過，這也是我為什么不把前20也包括進(jìn)我的隨機(jī)序列的原因。作為替換，我會(huì)使用2的n次方個(gè)頁面，循環(huán)的大小是需要的不同頁的數(shù)量*1000，所以，每個(gè)頁面幾乎都被獲取了1000次（由于隨機(jī)原因，肯定會(huì)有所偏差）

相關(guān)文章

最新評(píng)論