注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

天涯倦客的博客

祝福你朋友永远快乐!

 
 
 

日志

 
 

基于sqlserver2005的两种海量数据查询脚本解决方法  

2011-10-12 23:29:52|  分类: MS SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

1、在分页读取数据时不要读取字段内容大的字段,例如 text 或者 ntext等 。

2、对于一些通过计算生成的字段不支持排序。如果非常重要一定要排序的话,可以采用准备数据的方式通过job异步生成这些信息。

3、如果Left Outer Join 的右表不在WHERE 条件里出现,并且在业务上去掉这些条件和不去最后不会影响到返回的记录数,那么CTE表达式里还是去掉对这些表的关联,

同样在统计记录总数的Sql语句中也应把对这些表的关联去掉。

4、可以考虑在做Inner Join的时候,相互关联的两个表的字段加上索引

1. SQLServer2005中分页SQL 方式1:

declare @p5 int
set @p5=NULL
exec sp_executesql N'

SELECT @TotalCount = COUNT(C.SysNo)
    FROM [dbo].[Customer] AS C WITH(NOLOCK)
    LEFT JOIN [dbo].[Customer_Extend] AS E WITH(NOLOCK)
       ON C.SysNo = E.CustomerSysNo

    #StrWhere# (动态组装查询提交,条件中存在多表操作时,在Select Count(*)语句中应加入left join/right join/inner join链接,否则不需要加)

WITH TempTable AS (

SELECT C.[SysNo] (只取主键一个字段)
,Row_Number() OVER (Order BY #SortColumnName#) AS RowNumber
    FROM [dbo].[Customer] AS C WITH(NOLOCK)
    LEFT JOIN [dbo].[Customer_Extend] AS E WITH(NOLOCK)
       ON C.SysNo = E.CustomerSysNo

    #StrWhere# (动态组装查询提交,条件中存在多表操作时,在WITH TempTable AS (...)语句中应加入left join/right join/inner join链接,否则不需要加)
)

SELECT C.[SysNo]
      ,C.[CustomerID]
      ,C.[Pwd]
      ,C.[Status]
      ,C.[CustomerName]
      ,C.[Gender]
      ,C.[Email]
      ,C.[Phone]
      ,C.[CellPhone]
      ,C.[Fax]
      ,C.[DwellAreaSysNo]
      ,C.[DwellAddress]
      ,C.[DwellZip]
      ,C.[ReceiveName]
      ,C.[ReceiveContact]
      ,C.[ReceivePhone]
      ,C.[ReceiveCellPhone]
      ,C.[ReceiveFax]
      ,C.[ReceiveAreaSysNo]
      ,C.[ReceiveAddress]
      ,C.[ReceiveZip]
      ,C.[TotalScore]
      ,C.[ValidScore]
      ,C.[CardNo]
      ,C.[IsEmailConfirmed]
      ,C.[IsSubscribe]
      ,C.[RegisterTime]
      ,C.[Rank]
      ,C.[AuctionRank]
      ,C.[VIPRank]
      ,C.[TotalSOMoney]
      ,C.[FromLinkSource]
      ,C.[LastLoginDate]
      ,C.[IsAllowComment]
      ,C.[Birthday]
      ,C.[PointExpiringDate]
      ,C.[PromotionRankSign]
      ,C.[CompanyCustomer]
      ,C.[RecommendedByCustomerID]
      ,C.[ConfirmedTotalAmt]
      ,C.[IsUseChequesPay]
      ,C.[ValidPrepayAmt]
      ,C.[NickName]
      ,C.[Vip_CustomerType]

      ,E.[CustomerSysno]
      ,E.[BuyCount]
      ,E.[LastBuyDate]
      ,E.[StudentFlag]
      ,E.[SendCustomerRankEmailDate]
      ,E.[PayDays]
      ,E.[TotalCreditLimit]
      ,E.[AvailableCreditLimit]
      ,E.[LastReceiveAreaSysNo]
      ,E.[LastShipTypeSysNo]
      ,E.[LastPayTypeSysNo]
      ,E.[IsBadCustomer]
      ,E.[IsSystemUser]

FROM TempTable (相当于定义了一个临时表)

    INNER JOIN [dbo].[Customer] AS C WITH(NOLOCK)
        ON TempTable.SysNo = C.SysNo

LEFT JOIN [dbo].[Customer_Extend] AS E WITH(NOLOCK)
     ON C.SysNo = E.CustomerSysNo
WHERE RowNumber > @StartNumber AND RowNumber <= @EndNumber

ORDER BY RowNumber (DESC/ASC确定排序顺序)

',N'@EndNumber int,@StartNumber int,@TotalCount int output',@EndNumber=50,@StartNumber=0,@TotalCount=@p5 output
select @p5


2. SQLServer2005中分页SQL 方式2:

SELECT @TotalCount = COUNT(C.SysNo)
              FROM [dbo].[Customer] C WITH(NOLOCK)
                LEFT JOIN [dbo].[Customer_Extend] E WITH(NOLOCK) ON C.SysNo = E.CustomerSysno
              
        #StrWhere# (动态组装查询提交,条件中存在多表操作时,在Select Count(*)语句中应加入left join/right join/inner join链接,否则不需要加)
             
       SELECT [SysNo]
                    ,[CustomerID]
                    ,[Pwd]
                    ,[Status]
                    ,[CustomerName]
                    ,[Gender]
                    ,[Email]
                    ,[Phone]
                    ,[CellPhone]
                    ,[Fax]
                    ,[DwellAreaSysNo]
                    ,[DwellAddress]
                    ,[DwellZip]
                    ,[ReceiveName]
                    ,[ReceiveContact]
                    ,[ReceivePhone]
                    ,[ReceiveCellPhone]
                    ,[ReceiveFax]
                    ,[ReceiveAreaSysNo]
                    ,[ReceiveAddress]
                    ,[ReceiveZip]
                    ,[TotalScore]
                    ,[ValidScore]
                    ,[CardNo]
                    ,[Note]
                    ,[IsEmailConfirmed]
                    ,[IsSubscribe]
                    ,[RegisterTime]
                    ,[Rank]
                    ,[AuctionRank]
                    ,[VIPRank]
                    ,[TotalSOMoney]
                    ,[FromLinkSource]
                    ,[LastLoginDate]
                    ,[IsAllowComment]
                    ,[Birthday]
                    ,[PointExpiringDate]
                    ,[PromotionRankSign]
                    ,[CompanyCustomer]
                    ,[RecommendedByCustomerID]
                    ,[ConfirmedTotalAmt]
                    ,[IsUseChequesPay]
                    ,[ValidPrepayAmt]
  
                    ,[CustomerSysno]
      ,[BuyCount]
      ,[LastBuyDate]
      ,[StudentFlag]
      ,[SendCustomerRankEmailDate]
      ,[PayDays]
      ,[TotalCreditLimit]
      ,[AvailableCreditLimit]
      ,[LastReceiveAreaSysNo]
      ,[LastShipTypeSysNo]
      ,[LastPayTypeSysNo]
      ,[IsBadCustomer]
              FROM
              (
              SELECT TOP (@EndNumber)
              C.SysNo
                    ,C.CustomerID
                    ,C.Pwd
                    ,C.Status
                    ,C.CustomerName
                    ,C.Gender
                    ,C.Email
                    ,C.Phone
                    ,C.CellPhone
                    ,C.Fax
                    ,C.DwellAreaSysNo
                    ,C.DwellAddress
                    ,C.DwellZip
                    ,C.ReceiveName
                    ,C.ReceiveContact
                    ,C.ReceivePhone
                    ,C.ReceiveCellPhone
                    ,C.ReceiveFax
                    ,C.ReceiveAreaSysNo
                    ,C.ReceiveAddress
                    ,C.ReceiveZip
                    ,C.TotalScore
                    ,C.ValidScore
                    ,C.CardNo
                    ,C.Note
                    ,C.IsEmailConfirmed
                    ,C.IsSubscribe
                    ,C.RegisterTime
                    ,C.Rank
                    ,C.AuctionRank
                    ,C.VIPRank
                    ,C.TotalSOMoney
                    ,C.FromLinkSource
                    ,C.LastLoginDate
                    ,C.IsAllowComment
                    ,C.Birthday
                    ,C.PointExpiringDate
                    ,C.PromotionRankSign
                    ,C.CompanyCustomer
                    ,C.RecommendedByCustomerID
                    ,C.ConfirmedTotalAmt
                    ,C.IsUseChequesPay
                    ,C.ValidPrepayAmt

                    ,E.[CustomerSysno]
      ,E.[BuyCount]
      ,E.[LastBuyDate]
      ,E.[StudentFlag]
      ,E.[SendCustomerRankEmailDate]
      ,E.[PayDays]
      ,E.[TotalCreditLimit]
      ,E.[AvailableCreditLimit]
      ,E.[LastReceiveAreaSysNo]
      ,E.[LastShipTypeSysNo]
      ,E.[LastPayTypeSysNo]
      ,E.[IsBadCustomer]
             ,(ROW_NUMBER() OVER(ORDER BY #SortColumnName#)) AS RowNumber
              FROM [dbo].[Customer] C WITH(NOLOCK)
              LEFT JOIN [dbo].[Customer_Extend] E WITH(NOLOCK) ON C.SysNo = E.CustomerSysno
             
       #StrWhere# (动态组装查询提交,条件中存在多表操作时,在SELECT TOP ()语句中应加入left join/right join/inner join链接,否则不需要加)
             
       ) Result
              WHERE RowNumber > @StartNumber   

来源:http://hi.baidu.com/dreamsun2008/blog/item/1d412a2ccc50903c349bf7f8.html

  评论这张
 
阅读(592)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017