好湿?好紧?好多水好爽自慰,久久久噜久噜久久综合,成人做爰A片免费看黄冈,机机对机机30分钟无遮挡

主頁 > 知識庫 > sqlserver2005利用臨時表和@@RowCount提高分頁查詢存儲過程性能示例分享

sqlserver2005利用臨時表和@@RowCount提高分頁查詢存儲過程性能示例分享
熱門標簽：海南自動外呼系統價格 松原導航地圖標注 九鹿林外呼系統怎么收費 滄州營銷外呼系統軟件 沈陽智能外呼系統代理 創業電銷機器人 浙江地圖標注 電銷機器人虛擬號碼 舞鋼市地圖標注app

最近發現現有框架的通用查詢存儲過程的性能慢，于是仔細研究了下代碼：

復制代碼 代碼如下:

Alter PROCEDURE [dbo].[AreaSelect]
    @PageSize int=0,
    @CurrentPage int=1,
    @Identifier int=NULL,
    @ParentId int=NULL,
    @AreaLevel int=NULL,
    @Children int=NULL,
    @AreaName nvarchar(50)=NULL,
    @Path nvarchar(MAX)=NULL,
    @Status int=NULL,
    @Alt int=NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF (NOT @AreaName IS NULL)    SET @AreaName='%'+@AreaName+'%'
    IF (NOT @Path IS NULL)    SET @Path='%'+@Path+'%'
    IF (@PageSize>0)
    BEGIN
        DECLARE @TotalPage int
        Select @TotalPage=Count(Identifier) FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
        IF(@TotalPage%@PageSize=0)
        BEGIN
            SET @TotalPage=@TotalPage/@PageSize
        END
        ELSE
        BEGIN
            SET @TotalPage=Round(@TotalPage/@PageSize,0)+1
        END
        Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt,@TotalPage as totalPage FROM Area Where
        Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
            order by AreaName asc)
        AND
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
            order by AreaName asc
    END
    ELSE
    BEGIN
        Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
            order by AreaName asc
    END
END

發現每次查詢都需要按條件查詢依次Area表，性能太低，于是利用臨時表將符合條件的記錄取出來，然后針對臨時表進行查詢，代碼修改如下：
Alter PROCEDURE [dbo].[AreaSelect]
    @PageSize int=0,
    @CurrentPage int=1,
    @Identifier int=NULL,
    @ParentId int=NULL,
    @AreaLevel int=NULL,
    @Children int=NULL,
    @AreaName nvarchar(50)=NULL,
    @Path nvarchar(MAX)=NULL,
    @Status int=NULL,
    @Alt int=NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF (NOT @AreaName IS NULL)    SET @AreaName='%'+@AreaName+'%'
    IF (NOT @Path IS NULL)    SET @Path='%'+@Path+'%'

   
    IF (@PageSize>0)
    BEGIN
        --創建臨時表
        Select
        Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
        INTO #temp_Area
        FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
        order by AreaName asc

        DECLARE @TotalPage int
        DECLARE @SumCount int

        --取總數
        Select @SumCount=Count(Identifier) FROM #temp_Area

        IF(@SumCount%@PageSize=0)
        BEGIN
            SET @TotalPage=@SumCount/@PageSize
        END
        ELSE
        BEGIN
            SET @TotalPage=Round(@SumCount/@PageSize,0)+1
        END
        Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
        Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
        FROM #temp_Area
        Where
        Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
    END
    ELSE
    BEGIN
        Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
            order by AreaName asc
    END
END

經過使用臨時表的確提高性能，不過有發現一個問題，就是count(Identifier)的確很耗性能，于是又進行修改了
：

Alter PROCEDURE [dbo].[AreaSelect]
    @PageSize int=0,
    @CurrentPage int=1,
    @Identifier int=NULL,
    @ParentId int=NULL,
    @AreaLevel int=NULL,
    @Children int=NULL,
    @AreaName nvarchar(50)=NULL,
    @Path nvarchar(MAX)=NULL,
    @Status int=NULL,
    @Alt int=NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF (NOT @AreaName IS NULL)    SET @AreaName='%'+@AreaName+'%'
    IF (NOT @Path IS NULL)    SET @Path='%'+@Path+'%'

   
    IF (@PageSize>0)
    BEGIN
        --創建中記錄數
        DECLARE @SumCount int

        --創建臨時表
        Select
        Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
        INTO #temp_Area
        FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
        order by AreaName asc
        --設置總記錄數為剛操作的記錄數
        SET @SumCount=@@RowCount

        DECLARE @TotalPage int

        IF(@SumCount%@PageSize=0)
        BEGIN
            SET @TotalPage=@SumCount/@PageSize
        END
        ELSE
        BEGIN
            SET @TotalPage=Round(@SumCount/@PageSize,0)+1
        END
        Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
        Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
        FROM #temp_Area
        Where
        Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
    END
    ELSE
    BEGIN

        Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
        order by AreaName asc
    END
END

您可能感興趣的文章:
  • sql server2008調試存儲過程的完整步驟
  • SQLServer2008存儲過程實現數據插入與更新
  • Sql Server 存儲過程調用存儲過程接收輸出參數返回值
  • SQLServer存儲過程創建和修改的實現代碼
  • 獲取SqlServer存儲過程定義的三種方法
  • SqlServer存儲過程實現及拼接sql的注意點
  • 淺析SQL Server的嵌套存儲過程中使用同名的臨時表怪像

標簽：寶雞 咸寧 商洛 海口 臺灣 西藏 日喀則 公主嶺

巨人網絡通訊聲明：本文標題《sqlserver2005利用臨時表和@@RowCount提高分頁查詢存儲過程性能示例分享》，本文關鍵詞  sqlserver2005,利用,臨時,表,；如發現本文內容存在版權問題，煩請提供相關信息告之我們，我們將及時溝通與處理。本站內容系統采集于網絡，涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《sqlserver2005利用臨時表和@@RowCount提高分頁查詢存儲過程性能示例分享》相關的同類信息！
  • 本頁收集關于sqlserver2005利用臨時表和@@RowCount提高分頁查詢存儲過程性能示例分享的相關信息資訊供網民參考！
    • 推薦文章
    主站蜘蛛池模板： 我的性启蒙袁老师| 亚洲欧美精品专区极品| 调教贱奴女警花带乳环小说| 国产精品久久久久久一级毛片内衣| 屁屁影院地址永久入口2022 | 极品透明情趣少妇| 男生肌肌插女生肌肌| 国产精品久久久一本精品重冂色情| 快点h| 久中文字幕中文字幕亚洲无线| 越南bbw| 娇妻在厨房被朋友躁得呻吟| 91精品国产情侣高潮露脸| 免费色片软件| 国产乱人伦精品一区二区| 久青草国产在线视频亚瑟影视| 漫画羞羞露出隐私古代| 5060午夜一级一片| 久久精品国产在热亚洲完整版| 好爽?好紧?宝贝别夹大巴视频 | 播放一情欲一k8一经典片| 欧美精品videoss另类日本| 国产精品乱码一区二三区小蝌蚪| 国产成人lu在线视频| 欧美极品另类videosbesr使用方法| 冲田杏梨av爆乳一区二区三区| 虫族bl文全肉高h| 啊我cao死你个荡货| 极品丰满翘臀后进啪啪| 亚洲午夜精品17c| 女教师一级特黄大片| 滚床单无遮挡免费视频| 最新国产美女肝交视频播放| 写真福利片HD在线播放| 超级教师电影在线观看完整版 | 欧美高大丰满sexvideo| 午夜精品无码久久综合网| 中文字幕日韩有码| 亚洲激情影院| free性sex另类蛇交HD| 扒开粉嫩细缝喷白浆sex视频 |