欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ASP.NET MVC + EF 利用存储过程读取大数据,1亿数据测试很OK

程序员文章站 2022-10-03 13:10:58
看到本文的标题,相信你会忍不住进来看看!   没错,本文要讲的就是这个重量级的东西,这个不仅仅支持单表查询,更能支持连接查询,   加入一个表10W数据...
看到本文的标题,相信你会忍不住进来看看!

 

没错,本文要讲的就是这个重量级的东西,这个不仅仅支持单表查询,更能支持连接查询,

 

加入一个表10W数据,另一个表也是10万数据,当你用linq建立一个连接查询然后利用take,skip读取第N页数据的时候,

 

你的程序就挂了,因为,你很可能读取需要几十秒甚至几分钟以上。

 

下面来讲解一下,ASP.NET MVC + EF 利用存储过程读取大数据的详细过程。

 

1.首先,我们创建一个实体类PageinationInfo,主要用于分页,如下

 

复制代码

 1 public class PageinationInfo

 2     {

 3         /// <summary>

 4         /// 要显示的表或多个表的连接

 5         /// </summary>

 6         public string strTable { get; set; }

 7 

 8         /// <summary>

 9         /// 要查询的字段

10         /// </summary>

11         public string strField { get; set; }

12 

13         /// <summary>

14         /// 每页多少条记录

15         /// </summary>

16         public int pageSize { get; set; }

17 

18         /// <summary>

19         /// 当前页

20         /// </summary>

21         public int pageIndex { get; set; }

22 

23         /// <summary>

24         /// 查询条件,不需where

25         /// </summary>

26         public string strWhere { get; set; }

27 

28         /// <summary>

29         /// 用于排序的主键

30         /// </summary>

31         public string strSortKey { get; set; }

32 

33         /// <summary>

34         /// 用于排序,如:id desc (多个id desc,dt asc)

35         /// </summary>

36         public string strSortField { get; set; }

37 

38         /// <summary>

39         /// 排序,0-顺序,1-倒序

40         /// </summary>

41         public bool strOrderBy { get; set; }

42 

43         /// <summary>

44         /// 总记录数

45         /// </summary>

46         public int RecordCount { get; set; }

47 

48         /// <summary>

49         /// 总页数

50         /// </summary>

51         public int PageCount { get; set; }

52 

53         /// <summary>

54         /// 查询耗时,毫秒为单位

55         /// </summary>

56         public int UsedTime { get; set; }

57 

58     }

复制代码

2.然后我们再DAL层新建一个类 PageinationInfoService 主要用于实现分页读取数据,如下:

 

复制代码

 1 public class PageinationInfoService

 2     {

 3         /// <summary>

 4         /// 获取分页列表

 5         /// </summary>

 6         /// <param name="pageinationInfo"></param>

 7         /// <returns></returns>

 8         public IList<Entity> GetPageinationInfoList<Entity>(PageinationInfo pageinationInfo) where Entity : class

 9         {

10             dynamic result = null;

11             using (SnsLearningLogManagerDB db = new SnsLearningLogManagerDB())

12             {

13                 #region SqlParameter参数

14                 SqlParameter[] paras = new SqlParameter[10];

15                 paras[0] = new SqlParameter("strTable", DbType.String);

16                 paras[0].Value = pageinationInfo.strTable;

17 

18                 paras[1] = new SqlParameter("strField", DbType.String);

19                 paras[1].Value = pageinationInfo.strField;

20 

21                 paras[2] = new SqlParameter("pageSize", DbType.Int16);

22                 paras[2].Value = pageinationInfo.pageSize;

23 

24                 paras[3] = new SqlParameter("pageIndex", DbType.Int16);

25                 paras[3].Value = pageinationInfo.pageIndex;

26 

27                 paras[4] = new SqlParameter("strWhere", DbType.String);

28                 paras[4].Value = pageinationInfo.strWhere;

29 

30                 paras[5] = new SqlParameter("strSortKey", DbType.String);

31                 paras[5].Value = pageinationInfo.strSortKey;

32 

33                 paras[6] = new SqlParameter("strSortField", DbType.String);

34                 paras[6].Value = pageinationInfo.strSortField;

35 

36                 paras[7] = new SqlParameter("strOrderBy", DbType.Boolean);

37                 paras[7].Value = pageinationInfo.strOrderBy;

38 

39                 paras[8] = new SqlParameter("RecordCount", DbType.Int16);

40                 paras[8].Value = pageinationInfo.RecordCount;

41                 paras[8].Direction = ParameterDirection.Output;

42 

43                 paras[9] = new SqlParameter("UsedTime", DbType.Int16);

44                 paras[9].Value = pageinationInfo.UsedTime;

45                 paras[9].Direction = ParameterDirection.Output;

46                 #endregion

47 

48                 try

49                 {

50                     result = db.Database.SqlQuery<Entity>("exec LYBPager @strTable,@strField,@pageSize,@pageIndex,@strWhere,@strSortKey,@strSortField,@strOrderBy,@RecordCount output,@UsedTime output", paras).ToList();

51                     pageinationInfo.RecordCount = (int)paras[8].Value;

52                     pageinationInfo.UsedTime = (int)paras[9].Value;

53                 }

54                 catch (Exception ex)

55                 {

56                     throw;

57                 }

58             }

59             return result;

60         }

61     }

复制代码

PageinationInfoService类中我们传入的参数是实体和PageinationInfo,实体主要是用于接收数据并封装到实体中,LYBPager 这个是数据库中存储过程的名称,而UsedTime 就是存储过程读取数据所用的时间

 

BLL层的东西,我就不贴出来了,你们或者用工厂模式或者用简单3层,这个都无关紧要。

 

3.接下来,我们在Controller里来处理我们的业务需求,贴一段自己项目的代码作为分析,如下

 

复制代码

 1 public ActionResult Index(string LabelName,string _Title,string _Content, int pageNumber = 1, int pageSize = 10)

 2         {

 3             #region 分页

 4             SnsModels.PageinationInfo pageinationInfo = new SnsModels.PageinationInfo();

 5             pageinationInfo.pageIndex = pageNumber;

 6             pageinationInfo.pageSize = pageSize;

 7             pageinationInfo.RecordCount = 0;

 8             pageinationInfo.strField = "*";

 9             pageinationInfo.strOrderBy = true;

10             pageinationInfo.strSortField = "ArticleID desc";

11             pageinationInfo.strSortKey = "ArticleID";

12             pageinationInfo.strTable = "ArticleInfo";

13             pageinationInfo.strWhere = " 1=1";

14             pageinationInfo.UsedTime = 0;

15             pageinationInfo.PageCount = 0;

16             #endregion

17 

18             IList<SnsModels.LabelInfo> LabelInfoList = Repository.GetList<SnsModels.LabelInfo>();

19             ViewBag.LabelInfoList = new SelectList(LabelInfoList,"LabelName","LabelName");

20 

21             #region 参数处理

22             if (LabelName != null)

23             {

24                 if (!string.IsNullOrEmpty(LabelName))

25                 {

26                     pageinationInfo.strWhere += " and LabelName like '%" + HttpUtility.UrlDecode(LabelName.Trim()) + "%'";

27                     ViewBag.LabelInfoList = new SelectList(LabelInfoList, "LabelName", "LabelName",LabelName.Trim());

28                 }

29             }

30             if (_Title != null)

31             {

32                 if (!string.IsNullOrEmpty(_Title))

33                 {

34                     pageinationInfo.strWhere += " and Title like '%" + _Title.Trim() + "%'";

35                 }

36             }

37             if (_Content != null)

38             {

39                 if (!string.IsNullOrEmpty(_Content))

40                 {

41                     pageinationInfo.strWhere += " and Content like '%" + _Content.Trim() + "%'";

42                 }

43             }

44             #endregion

45 

46 

47             IList<SnsModels.ArticleInfo> List = PageinationInfoManager.GetPageinationInfoList<SnsModels.ArticleInfo>(pageinationInfo);

48 

49             #region 传值

50             ViewBag.List = List;

51             ViewBag.pageNumber = pageNumber;

52             ViewBag.pageSize = pageSize;

53             ViewBag.RecordCount = pageinationInfo.RecordCount;

54             ViewBag.LabelName =HttpUtility.UrlDecode(LabelName);

55             ViewBag._Title = _Title;

56             ViewBag._Content = _Content;

57             #endregion

58 

59             return View();

60         }

复制代码

上面我们通过把参数传递到PageinationInfo,然后根据传递的参数拼凑pageinationInfo.strWhere现实了多条件查询,

 

pageNumber,pageSize 我们已经传递到View,你可以通过Jquery插件展示你的分页,点击一下一页的时候,跳转回控制器就行了。

 

 

 

4,接下来,我给出存储过程,代码如下:

 

复制代码

USE [LearningLogManagerDB2]

GO

/****** Object:  StoredProcedure [dbo].[LYBPager]    Script Date: 07/30/2014 11:51:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

--参数说明-------------------------------------------------------------

/**//*

@strTable --要显示的表或多个表的连接

@strField --要查询出的字段列表,*表示全部字段

@pageSize --每页显示的记录个数

@pageIndex --要显示那一页的记录

@strWhere --查询条件,不需where

@strSortKey --用于排序的主键

@strSortField --用于排序,如:id desc (多个id desc,dt asc)

@strOrderBy --排序,0-顺序,1-倒序

@RecordCount --查询到的总记录数

@UsedTime --耗时测试时间差

*/

 

 

ALTER PROCEDURE [dbo].[LYBPager]

@strTable varchar(1000) = '[dbo].[ttable]',--表名

@strField varchar(1000) = '*', --查询字段

@pageSize int = 10,  --每页多少条记录

@pageIndex int = 1,  --当前页

@strWhere varchar(1000) = '1=1', --查询条件

@strSortKey varchar(1000) = 'id', --主键

@strSortField varchar(500) = 'id DESC', --排序

@strOrderBy bit = 1, --是否排序   1表示排序

@RecordCount int OUTPUT,  --总记录数

@UsedTime int OUTPUT --查询耗时,毫秒为单位

AS

SET NOCOUNT ON

Declare @sqlcount INT

Declare @timediff DATETIME

select @timediff=getdate()

Begin Tran

DECLARE @sql nvarchar(500),@where1 varchar(200),@where2 varchar(200)

    IF @strWhere is null or rtrim(@strWhere)=''

        BEGIN--没有查询条件

            SET @where1=' WHERE '

            SET @where2=' '

        END

    ELSE

        BEGIN--有查询条件

            SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件

            SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件

        END

    --SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2

 

    BEGIN

        SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'

    END

    --print @sql

 

EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数

SELECT @RecordCount = @sqlcount --设置总记录数

    IF @pageIndex=1 --第一页

        BEGIN

            SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where2+'ORDER BY '+ @strSortField

        END

    Else

        BEGIN

            IF @strOrderBy=0

                SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+ ' FROM '+

                    @strTable+@where1+@strSortKey+'>(SELECT MAX('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+

                    CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+@strSortKey+' FROM '+@strTable+@where2+

                    'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField

            ELSE

                SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where1+

                    @strSortKey+'<(SELECT MIN('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+

                    @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''

        END

print @sql

--select @RecordCount

EXEC(@sql)

print @sql

If @@Error <> 0

Begin

RollBack Tran

Return -1

End

Else

Begin

Commit TRAN

set @UsedTime = datediff(ms,@timediff,getdate())

--select @UsedTime

--select datediff(ms,@timediff,getdate()) as 耗时

Return @sqlcount

End

复制代码

当然存储过程不是本人写的,我只是利用而已,也用了很久了,感觉还可以,有兴趣的可以分析分析。

 

在此非常感谢很多博友加入本人ASP.NET MVC QQ群,并且都很踊跃提问