EF 在千万的数据库查询测试

表有2500万条数据，结构 

先来最简单EF查询的吧，

然后赶紧开个SQL Server 2014 Profiler 去拦截SQL

去掉第一次启动加载时间，咱们在后面刷几下页面

接下来分页查询吧，先简单的开始。

下面查询语句时间00:00:00.4783581 也是1秒不到，1秒以下的这里就不比较了

1. System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();            
2. stopwatch.Start();
3. int total=0;            
4. var list= _adminbll.GetListByPage(1"开始页", 100"查询条数", out total"获取符合条件的总记录数", o => o.ID>0"查询条件", o => o.ID"排序方式", false).ToList(); 注释写在代码里吧，好看的懂           
5. stopwatch.Stop();            timeSpan = stopwatch.Elapsed;            
6. ViewBag.Title = timeSpan.ToString();
7. TimeSpan timeSpan = new TimeSpan();

这里我们把开始页调高点，他也会影响查询速度.

下面查询语句时间：00:00:02.5151320

1. var list= _adminbll.GetListByPage(10000, 100"查询条数", out total"获取符合条件的总记录数", o => o.ID>0"查询条件", o => o.ID"排序方式", false).ToList(); 注释写在代码里吧，好看的懂

上面查询语句转化成sql

下面查询语句时间：2秒

SELECT 

    [Extent1].[ID] AS [ID], 

    [Extent1].[UserName] AS [UserName], 

    [Extent1].[PassWord] AS [PassWord], 

    [Extent1].[LastLoginIP] AS [LastLoginIP], 

    [Extent1].[LastLoginTime] AS [LastLoginTime], 

    [Extent1].[Status] AS [Status]

    FROM [dbo].[s_Admin] AS [Extent1]

    WHERE [Extent1].[ID] > 0

    ORDER BY row_number() OVER (ORDER BY [Extent1].[ID] DESC)

    OFFSET 9999900 ROWS FETCH NEXT 100 ROWS ONLY 

再将sql升级下

ef执行下面查询语句 username包含123的，lastlogintime小于当前时间的

下面查询时间

00:00:11.8896552

1. var list= _adminbll.GetListByPage(100000, 100, out total, o => o.UserName.Contains("123") &&o.LastLoginTime<DateTime.Now, o => o.ID, false).ToList();

转换成sql

下面语句查询也是11秒。

SELECT 

    [GroupBy1].[A1] AS [C1]

    FROM ( SELECT 

        COUNT(1) AS [A1]

        FROM [dbo].[s_Admin] AS [Extent1]

        WHERE ([Extent1].[UserName] LIKE N'%123%') AND ([Extent1].[LastLoginTime] < (SysDateTime()))

    )  AS [GroupBy1]//查询总记录数

 

SELECT 

    [Extent1].[ID] AS [ID], 

    [Extent1].[UserName] AS [UserName], 

    [Extent1].[PassWord] AS [PassWord], 

    [Extent1].[LastLoginIP] AS [LastLoginIP], 

    [Extent1].[LastLoginTime] AS [LastLoginTime], 

    [Extent1].[Status] AS [Status]

    FROM [dbo].[s_Admin] AS [Extent1]

    WHERE ([Extent1].[UserName] LIKE N'%123%') AND ([Extent1].[LastLoginTime] < (SysDateTime()))

    ORDER BY row_number() OVER (ORDER BY [Extent1].[ID] DESC)

    OFFSET 9999900 ROWS FETCH NEXT 100 ROWS ONLY 

其中光

SELECT 

    [GroupBy1].[A1] AS [C1]

    FROM ( SELECT 

        COUNT(1) AS [A1]

        FROM [dbo].[s_Admin] AS [Extent1]

        WHERE ([Extent1].[UserName] LIKE N'%123%') AND ([Extent1].[LastLoginTime] < (SysDateTime()))

    )  AS [GroupBy1];

这条语句就查询了4秒中。

以上的sql都是ef执行转换出来的。理论来说也是应当ef执行和sql语句执行的速度相差差不多。而分页查询速度优化看sql的逻辑内容了，然后再将自己的逻辑转化成代码实现。

 

 

 

然后下面这个语句做下优化。我的想法说个事只展现100页，再结合各种条件排序，100页后面就是数据就不展现了，或者你确实需要的话，那你可以用其他优化方案。 之前查询时间是11秒

SELECT 

    [GroupBy1].[A1] AS [C1]

    FROM ( SELECT 

        COUNT(1) AS [A1]

        FROM [dbo].[s_Admin] AS [Extent1]

        WHERE ([Extent1].[UserName] LIKE N'%123%') AND ([Extent1].[LastLoginTime] < (SysDateTime()))

    )  AS [GroupBy1]//查询总记录数

 

SELECT 

    [Extent1].[ID] AS [ID], 

    [Extent1].[UserName] AS [UserName], 

    [Extent1].[PassWord] AS [PassWord], 

    [Extent1].[LastLoginIP] AS [LastLoginIP], 

    [Extent1].[LastLoginTime] AS [LastLoginTime], 

    [Extent1].[Status] AS [Status]

    FROM [dbo].[s_Admin] AS [Extent1]

    WHERE ([Extent1].[UserName] LIKE N'%123%') AND ([Extent1].[LastLoginTime] < (SysDateTime()))

    ORDER BY row_number() OVER (ORDER BY [Extent1].[ID] DESC)

    OFFSET 9999900 ROWS FETCH NEXT 100 ROWS ONLY 

这里只展现100页。每页100条

这里我们就可以判断记录条数是否超过10000，这里转成sql

 select count(0) num from(

 SELECT top 10000 ID

        FROM [dbo].[s_Admin] AS [Extent1]

        WHERE ([Extent1].[UserName] LIKE N'%123%') AND ([Extent1].[LastLoginTime] < (SysDateTime())) 

) f 

执行速度不到1秒

SELECT 

    [Extent1].[ID] AS [ID], 

    [Extent1].[UserName] AS [UserName], 

    [Extent1].[PassWord] AS [PassWord], 

    [Extent1].[LastLoginIP] AS [LastLoginIP], 

    [Extent1].[LastLoginTime] AS [LastLoginTime], 

    [Extent1].[Status] AS [Status]

    FROM [dbo].[s_Admin] AS [Extent1]

    WHERE ([Extent1].[UserName] LIKE N'%123%') AND ([Extent1].[LastLoginTime] < (SysDateTime()))

    ORDER BY row_number() OVER (ORDER BY [Extent1].[ID] DESC)

    OFFSET 9900 ROWS FETCH NEXT 100 ROWS ONLY 

也不到1秒

转化成EF执行语句

1.  Expression<Func<Admin, bool>> whereLambda=o => o.UserName.Contains("123") &&o.LastLoginTime<DateTime.Now;
2.  int total = _adminbll.GetList<int>(whereLambda).Take(100*100).Count(); 
3. var list = _adminbll.GetList( whereLambda, o => o.ID, false).Skip(9900).Take(100).ToList();

相同的查询条件获取第100页100条数据，以上代码执行00:00:00.0458893 

 

原文地址《http://www.52daren.com/450538150/article_id_47.html》