sql 存储过程千万条数据分页

最新推荐文章于 2024-09-30 11:39:45 发布

bodilove

最新推荐文章于 2024-09-30 11:39:45 发布

阅读量3.4k

点赞数 1

文章标签： sql 分页分页数据

本文链接：https://blog.csdn.net/bodilove/article/details/73991783

版权

1.新建表（数据库TestDB）

USE [TestDB]
GO
/****** Object:  Table [Data].[TestInfo]    Script Date: 2017/6/30/周五 13:20:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [Data].[TestInfo](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[ProcessID] [INT] NOT NULL,
[ProductID] [INT] NOT NULL,
[StationName] [NVARCHAR](50) NOT NULL,
[StationID] [INT] NOT NULL,
[StationContent] [NVARCHAR](60) NULL,
[ProcessData] [VARCHAR](MAX) NULL,
[UpdateTime] [DATETIME] NOT NULL,
[Enable] [NVARCHAR](50) NOT NULL,
CONSTRAINT [PK_TestInfo_1] PRIMARY KEY CLUSTERED 
([ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

ALTER TABLE [Data].[TestInfo] ADD  CONSTRAINT [DF_TestInfo_Enable]  DEFAULT (N'NOK') FOR [Enable]
GO

2.插入一千万条数据（时间比较久，自己可酌情添加数据自己测试）

DECLARE  @i INT
SET  @i=1
WHILE  @i<10000000
BEGIN 
	INSERT INTO Data.TestInfo (
				ProcessID,
				ProductID ,
				StationName,
				StationID ,
				StationContent,
				UpdateTime) 
	VALUES ( @i 
			,@i
			,'A050'
			,0
			,'当前工位代替描述的文本'
			,GETDATE())
	SET  @i=@i+1
END

3.存储过程（我自己写了四种方式分页，都能使用，但是第四种效果最好。）

第一种使用临时变量存储临时表数据，好处使用完不用删除临时表，（微软建议使用此种方法，一般数据不是很大，其实和使用临时表区别不大，只要内存够大就行）

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[a_Get_Base_TestInfo]    Script Date: 2017/6/30/周五 13:35:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
 * 功能描述：获取分页列表
 * 创建时间：2017-06-28
 * 创建人：LHB
 
exec a_Get_Base_TestInfo
-1,
-1,
'',
'',
'NOK',
'2015-06-25 09:12',
'2017-06-29 09:12:00',
2,
10,
null
 */
ALTER PROCEDURE [dbo].[a_Get_Base_TestInfo]
 
     @ProcessID       INT           --工艺编号 (-1为不作限制) 
	,@ProductID       INT           --产品编号 (-1为不作限制) 
	,@StationName     NVARCHAR(100)	--工位名称            		             	
	,@StationContent  NVARCHAR(100)	--工位描述
	,@Enable		  NVARCHAR(100)	--是否启用（0:"NOK";1:"OK"）
	,@StartTime		  NVARCHAR(50)  --开始时间
	,@EndTime		  NVARCHAR(50)	--结束时间
	,@PageIndex	      INT			--当前页码
	,@PageSize	      INT			--分页大小（每页条数）
	,@RowCount 	 	  INT OUTPUT	 	
	
AS
	
   --P1.定义临时表，用于存储过滤后的数据
   DECLARE @tmpInfo AS TABLE 
   ([ID] [int]
    ,[ProcessID] [int]
	,[ProductID] [int]
	,[UpdateTime] [datetime] 
	,PRIMARY KEY CLUSTERED(ID)
   );
   
   --P2.把过滤后的数据存储到临时表
   INSERT INTO @tmpInfo
			([ID]
   			 ,[ProcessID]
   			 ,[ProductID]
   			 ,[UpdateTime])
	SELECT	 b.ID
			,b.ProcessID
			,b.ProductID
			,b.UpdateTime
	 FROM Data.TestInfo AS b WITH (NOLOCK)
	
	 WHERE (1=1
			  AND (@ProcessID = -1 OR  @ProcessID IS NULL OR b.[ProcessID] =  @ProcessID)
			  AND (@ProductID = -1 OR  @ProductID IS NULL OR b.[ProductID] =  @ProductID)
			  AND (@StationName IS NULL OR LEN(@StationName)=0 or b.StationName LIKE '%'+@StationName+'%')
			  AND (@StationContent IS NULL OR LEN(@StationContent)=0 or b.StationContent LIKE '%'+@StationContent+'%')
			  AND ( @Enable='-1' OR @Enable IS NULL OR LEN(@Enable) = 0   OR  b.[Enable]=@Enable)
			  AND (@StartTime IS NULL OR LEN(@StartTime)=0 OR b.UpdateTime >= @StartTime)
			  AND (@EndTime IS NULL OR LEN(@EndTime)=0 OR b.UpdateTime <= @EndTime)
			)
   
   --P3.获得活动总纪录数
   SELECT @RowCount = COUNT(1) FROM @tmpInfo;
   --P4.数据分页
   SELECT g.ID
		,g.ProcessID
		,g.ProductID
		 ,b.StationName
		--,b.StationID
		,b.StationContent
		,g.UpdateTime
		,b.[Enable]
		 
   FROM 
   (
	   SELECT  fp.*
			  ,ROW_NUMBER() OVER (ORDER BY fp.ID DESC) AS RowIndex 
		 FROM @tmpInfo AS fp
   ) g
   INNER JOIN Data.TestInfo AS b WITH (NOLOCK) ON b.ID  = g.ID
   
   WHERE g.RowIndex > ((@PageIndex-1)*@PageSize) AND g.RowIndex <=( @PageIndex*@PageSize);

第二种（使用临时表，最后使用完要记得删除。如果表比较大建议先清空表数据，在删除临时表，速度比较快）

此处使用了 INSERT INTO #tmpInfo ..... SELECT ...... FROM Data.TestInfo AS b WITH (NOLOCK) 将数据插入到临时表

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[a_Get_Base_TestInfo_temp]    Script Date: 2017/6/30/周五 13:44:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
 * 功能描述：获取分页信息列表（使用临时表#table）
 * 创建时间：2017-06-28
 * 创建人：LHB
 
exec a_Get_Base_TestInfo_temp
-1,
-1,
'',
'',
'NOK',
'2015-06-25 09:12',
'2017-06-29 09:12:00',
2,
10,
null
 */
ALTER PROCEDURE [dbo].[a_Get_Base_TestInfo_temp]
 
     @ProcessID       INT           --工艺编号 (-1为不作限制) 
	,@ProductID       INT           --产品编号 (-1为不作限制) 
	,@StationName     NVARCHAR(100)	--工位名称            		             	
	,@StationContent  NVARCHAR(100)	--工位描述
	,@Enable		  NVARCHAR(100)	--是否启用（0:"NOK";1:"OK"）
	,@StartTime		  NVARCHAR(50)  --开始时间
	,@EndTime		  NVARCHAR(50)	--结束时间
	,@PageIndex	      INT			--当前页码
	,@PageSize	      INT			--分页大小（每页条数）
	,@RowCount 	 	  INT OUTPUT	 	
	
AS
	
   --P1.定义临时表，用于存储过滤后的数据
   create table #tmpInfo 
   ([ID] [int]
    ,[ProcessID] [int]
	,[ProductID] [int]
	,[UpdateTime] [datetime] 
   );
   
   --P2.把过滤后的数据存储到临时表
   INSERT INTO #tmpInfo
			([ID]
   			 ,[ProcessID]
   			 ,[ProductID]
   			 ,[UpdateTime]
			 )
	SELECT	 b.ID
			,b.ProcessID
			,b.ProductID
			,b.UpdateTime
	 FROM Data.TestInfo AS b WITH (NOLOCK)
	
	 WHERE (1=1
			 AND (@ProcessID = -1 OR b.[ProcessID] =  @ProcessID)
			  AND (@ProductID = -1 OR b.[ProductID] =  @ProductID)
			  AND (LEN(@StationName)=0 or b.StationName =@StationName)
			  --AND (@StationContent IS NULL OR LEN(@StationContent)=0 or b.StationContent LIKE '%'+@StationContent+'%')
			  AND (LEN(@Enable) = 0   OR  b.[Enable]=@Enable)
			  AND (LEN(@StartTime)=0 OR b.UpdateTime >= @StartTime)
			  AND (LEN(@EndTime)=0 OR b.UpdateTime <= @EndTime)
			)
   
   --P3.获得活动总纪录数
   SELECT @RowCount = COUNT(1) FROM #tmpInfo;
   --P4.数据分页
   SELECT g.ID
		,b.ProcessID
		,b.ProductID
		 ,b.StationName
		--,b.StationID
		,b.StationContent
		,b.UpdateTime
		,b.[Enable]
		 
   FROM 
   (
	   SELECT  fp.*
			  ,ROW_NUMBER() OVER (ORDER BY fp.ID DESC) AS RowIndex 
		 FROM #tmpInfo AS fp
   ) g
   INNER JOIN Data.TestInfo AS b WITH (NOLOCK) ON b.ID  = g.ID
   
   WHERE g.RowIndex > ((@PageIndex-1)*@PageSize) AND g.RowIndex <=( @PageIndex*@PageSize);

   --清空临时表数据 （注释：表数据比较大，先清空在删除速度比较快）
    truncate table #tmpInfo;
	--删除临时表数据
	drop table #tmpInfo;

第三种（使用临时表，最后使用完要记得删除。如果表比较大建议先清空表数据，在删除临时表，速度比较快）

此处使用了 select * into #table from table 将数据插入到临时表（此处省略了临时表的创建，据说可以减少sql log日志的记录提高查询速度，没感觉出来，有兴趣可以自己测试）

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[a_Get_Base_TestInfo_temp_NoLog]    Script Date: 2017/6/30/周五 13:52:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
 * 功能描述：获取工艺基础信息列表（减少log，增加速度）
 * 创建时间：2017-06-28
 * 创建人：LHB
 
exec a_Get_Base_TestInfo_temp_NoLog
-1,
-1,
'',
'',
'NOK',
'2015-06-25 09:12',
'2017-06-29 09:12:00',
2,
10,
null
 */
ALTER PROCEDURE [dbo].[a_Get_Base_TestInfo_temp_NoLog]
 
     @ProcessID       INT           --工艺编号 (-1为不作限制) 
	,@ProductID       INT           --产品编号 (-1为不作限制) 
	,@StationName     NVARCHAR(100)	--工位名称            		             	
	,@StationContent  NVARCHAR(100)	--工位描述
	,@Enable		  NVARCHAR(100)	--是否启用（0:"NOK";1:"OK"）
	,@StartTime		  NVARCHAR(50)  --开始时间
	,@EndTime		  NVARCHAR(50)	--结束时间
	,@PageIndex	      INT			--当前页码
	,@PageSize	      INT			--分页大小（每页条数）
	,@RowCount 	 	  INT OUTPUT	 	
	
AS
	
   --P1.定义临时表，用于存储过滤后的数据
 --  create table #tmpInfo 
 --  ([ID] [int],
 --   [ProcessID] [int],
	--[ProductID] [int],
	--[UpdateTime] [datetime] 
 --  );
   
   --P2.把过滤后的数据存储到临时表（select * into #table from table where field='' ）
   
	SELECT	 b.ID
			--,b.ProcessID
			--,b.ProductID
			--,b.UpdateTime
   INTO #tmpInfo
   FROM Data.TestInfo AS b WITH (NOLOCK)
	 WHERE (1=1
			  AND (@ProcessID = -1 OR b.[ProcessID] =  @ProcessID)
			  AND (@ProductID = -1 OR b.[ProductID] =  @ProductID)
			  AND (LEN(@StationName)=0 or b.StationName=@StationName)
			  --AND (LEN(@StationContent)=0 or b.StationContent LIKE '%'+@StationContent+'%')
			  AND (LEN(@Enable) = 0   OR  b.[Enable]=@Enable)
			  AND (LEN(@StartTime)=0 OR b.UpdateTime >= @StartTime)
			  AND (LEN(@EndTime)=0 OR b.UpdateTime <= @EndTime)

			)
			
   --P3.获得活动总纪录数
   SELECT @RowCount = COUNT(1) FROM #tmpInfo;

   --P4.数据分页
   SELECT 
		g.ID
		,b.ProcessID
		,b.ProductID
		 ,b.StationName
		--,b.StationID
		,b.StationContent
		,b.UpdateTime
		,b.[Enable]
		 
   FROM 
   (
	   SELECT  fp.*
			  ,ROW_NUMBER() OVER (ORDER BY fp.ID DESC) AS RowIndex 
		 FROM #tmpInfo AS fp
   ) g
   INNER JOIN Data.TestInfo AS b WITH (NOLOCK) ON b.ID  = g.ID
   
   WHERE g.RowIndex > ((@PageIndex-1)*@PageSize) AND g.RowIndex <=( @PageIndex*@PageSize);
  
   --清空临时表数据 （注释：表数据比较大，先清空在删除速度比较快）
    truncate table #tmpInfo;
   --删除临时表数据
    drop table #tmpInfo;

第四种（使用临时表，最后使用完要记得删除。如果表比较大建议先清空表数据，在删除临时表，速度比较快）

我使用的是拼接sql语句，把不用的 where 条件直接过滤掉。速度立马提升一大截（条件越多速度越慢）。当然返回的数据结果越多，所使用的时间越久。

从一千多万条数据查询几十万条数据，速度都是1秒级别。

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[a_Get_Base_TestInfo_temp_sql]    Script Date: 2017/6/30/周五 13:32:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
 * 功能描述：获取工艺基础信息列表（减少log，增加速度）
 * 创建时间：2017-06-28
 * 创建人：LHB
 
exec a_Get_Base_TestInfo_temp_sql
-1,
-1,
'',
'',
'NOK',
'2015-06-25 09:12',
'2017-06-29 09:12:00',
2,
10,
null
 */
ALTER PROCEDURE [dbo].[a_Get_Base_TestInfo_temp_sql]
 
     @ProcessID       INT           --工艺编号 (-1为不作限制) 
	,@ProductID       INT           --产品编号 (-1为不作限制) 
	,@StationName     NVARCHAR(100)	--工位名称            		             	
	,@StationContent  NVARCHAR(100)	--工位描述
	,@Enable		  NVARCHAR(100)	--是否启用（0:"NOK";1:"OK"）
	,@StartTime		  NVARCHAR(50)  --开始时间
	,@EndTime		  NVARCHAR(50)	--结束时间
	,@PageIndex	      INT			--当前页码
	,@PageSize	      INT			--分页大小（每页条数）
	,@RowCount 	 	  INT OUTPUT	 	
	
AS

   --P1.定义临时表，用于存储过滤后的数据
   create table #tmpInfo 
   ([ID] [int]
 --   [ProcessID] [int],
	--[ProductID] [int],
	--[UpdateTime] [datetime] 
   );
   
    --P2.把过滤后的数据存储到临时表（select * into #table from table where field='' ）
   declare @sql NVARCHAR(MAX) ;
   set @sql ='INSERT INTO #tmpInfo SELECT b.ID  FROM Data.TestInfo AS b WITH (NOLOCK) WHERE 1=1 '
   
   IF(@ProcessID>0)
   begin
		set @sql= @sql+' AND b.[ProcessID]= '''+cast(@ProcessID AS VARCHAR(20))+''''
   end
   IF(@ProductID>0)
   begin
		set @sql= @sql+' AND b.[ProductID]= '''+cast(@ProductID AS VARCHAR(20))+''''
   end

   IF(LEN(@StationName)>0)
   begin
		set @sql= @sql+' AND b.[StationName]= '''+@StationName+''''
   end
   IF(LEN(@Enable)>0)
   begin
		set @sql= @sql+' AND b.[Enable]= '''+@Enable+''''
   end
   IF(LEN(@StartTime)>0)
   begin
		set @sql= @sql+' AND b.[UpdateTime]>= '''+@StartTime+''''
   end
   IF(LEN(@EndTime)>0)
   begin
		set @sql= @sql+' AND b.[UpdateTime]<= '''+@EndTime+''''
   end
  
   EXEC sp_executesql @sql;
				
   --P3.获得活动总纪录数
  SELECT @RowCount= COUNT(1) FROM #tmpInfo;

   --P4.数据分页
   SELECT 
		g.ID
		,b.ProcessID
		,b.ProductID
		 ,b.StationName
		--,b.StationID
		,b.StationContent
		,b.UpdateTime
		,b.[Enable]
		 
   FROM 
   (
	   SELECT  fp.*
			  ,ROW_NUMBER() OVER (ORDER BY fp.ID DESC) AS RowIndex 
		 FROM #tmpInfo AS fp
   ) g
   INNER JOIN Data.TestInfo AS b WITH (NOLOCK) ON b.ID  = g.ID
   
   WHERE g.RowIndex > ((@PageIndex-1)*@PageSize) AND g.RowIndex <=( @PageIndex*@PageSize);
  
   --清空临时表数据 （注释：表数据比较大，先清空在删除速度比较快）
    truncate table #tmpInfo;
	--删除临时表数据
	drop table #tmpInfo;

4.调用存储过程测试

exec a_Get_Base_TestInfo_temp_sql
-1,
-1,
'',
'',
'NOK',
'2015-06-25 09:12',
'2017-06-29 09:12:00',
2,
10,
null

5. asp.net 实例调用

创建 PageRequestParam.cs公共参数分页用到的实体进行封装

/// <summary>
    /// 分页查询参数
    /// </summary>
    public class PageRequestParam
    {

        /// <summary>
        /// 当前页码
        /// </summary>
        public int PageIndex { get; set; }

        /// <summary>
        /// 分页大小
        /// </summary>
        public int PageSize { get; set; }


        /// <summary>
        /// 起始时间
        /// </summary>
        public string StartTime { get; set; }

        /// <summary>
        /// 结束时间
        /// </summary>
        public string EndTime { get; set; }
    }

创建Mode实体,并继承分页类

 /// <summary>
    /// 测试信息model
    /// </summary>
    public class TestInfo:PageRequestParam
    {
        public int ID { get; set; }
        public int ProcessID { get; set; }
        public int ProductID { get; set; }
        public string Operation { get; set; }
        public string StationName { get; set; }
        public int StationID { get; set; }
        public string StationContent { get; set; }
        public string ProcessData { get; set; }
        //public string CreateDate { get; set; }
        public string UpdateTime { get; set; }
        public string Enable { get; set; }
       
    }

C#分页方法，存储过程的调用

 /// <summary>
    /// 工艺基础信息 
    /// </summary>
    public class basicdataManagement_BPInfo
    {
        /// <summary>
        /// 链接字符串
        /// </summary>
        public string connString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"].ToString();

        /// <summary>
        ///  分页工艺基础信息查询
        /// </summary>
        /// <param name="pageSize">当前页</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="totalRecords">总记录</param>
        /// <param name="totalPages">总页数</param>
        /// <returns>object</returns>
        public string getInfoList(TestInfo m)
        {
            string json= "";
            int totalRecords = 0;
            using (SqlConnection con = new SqlConnection(connString))
            {
                if (m == null)
                {
                  return json.ToString();
                }
                //接收分页数据
                DataTable dt = new DataTable();
                try
                { 
                    con.Open();
                    SqlCommand cmd = new SqlCommand("a_Get_Base_TestInfo_temp_sql", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@ProcessID", m.ProcessID));
                    cmd.Parameters.Add(new SqlParameter("@ProductID", m.ProductID));
                    cmd.Parameters.Add(new SqlParameter("@StationName", m.StationName));
                    cmd.Parameters.Add(new SqlParameter("@StationContent", m.StationContent));
                    cmd.Parameters.Add(new SqlParameter("@Enable", m.Enable));
                    cmd.Parameters.Add(new SqlParameter("@StartTime", m.StartTime));
                    cmd.Parameters.Add(new SqlParameter("@EndTime", m.EndTime));
                    cmd.Parameters.Add(new SqlParameter("@PageIndex", m.PageIndex));
                    cmd.Parameters.Add(new SqlParameter("@PageSize", m.PageSize));
                    cmd.Parameters.Add(new SqlParameter("@RowCount", totalRecords));
                    //设置返回参数 为输出参数OutPut。
                    cmd.Parameters["@RowCount"].Direction = ParameterDirection.Output;
                    SqlDataAdapter dd = new SqlDataAdapter(cmd);
                    dd.Fill(dt);
                    //获取输出参数
                    totalRecords = int.Parse(cmd.Parameters["@RowCount"].Value.ToString());
                    cmd.Parameters.Clear();
                   json=totalRecords.ToString()
                }
                catch (Exception ex)
                { 
                }
                return json.ToString();
            }
       }
   }