学习Stopwatch类验证LINQ分页之强大

今天看到同事测试存储过程分页和LINQ分页效率的时候 用到了Stopwatch类  晚上就来学习了下

发现LINQ的分页效果之强大 代码如下

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace stopwatch
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Stopwatch sp = new Stopwatch();
            sp.Start();
            GridView1.DataSource = dt();
            GridView1.DataBind();
            GridView1.DataSource = null;
            GridView1.DataBind();
            sp.Stop();
            TimeSpan ts = sp.Elapsed;
            Response.Write(ts);
            Response.Write("<br/>");
            sp.Reset();
            sp.Start();
            IDataReader dd = dr();
            GridView1.DataSource = dd;
            GridView1.DataSource = null;
            GridView1.DataBind();
            GridView1.DataBind();
            dd.Close();
            sp.Stop();
            ts = sp.Elapsed;
            Response.Write(ts);
            Response.Write("<br/>");
            sp.Restart();
            GridView1.DataSource = ss();
            GridView1.DataSource = null;
            GridView1.DataBind();
            GridView1.DataBind();
            sp.Stop();
            ts = sp.Elapsed;
            Response.Write(ts);

            Response.Write("<br/>");
            sp.Restart();
            GridView1.DataSource = dt2();
            GridView1.DataSource = null;
            GridView1.DataBind();
            GridView1.DataBind();
            sp.Stop();
            ts = sp.Elapsed;
            Response.Write(ts);
            
        }

        public DataTable dt()
        {
            string str = ConfigurationManager.ConnectionStrings[1].ToString();
            SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from (select   * ,ROW_NUMBER() over(order by name desc) as ff    from www ) aa where  ff between 40  and  150", con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            con.Close();
            cmd.Dispose();
            return dt;

        }

        public IDataReader dr()
        {
            string str = ConfigurationManager.ConnectionStrings[1].ToString();
            SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from (select   * ,ROW_NUMBER() over(order by name desc) as ff    from www ) aa where  ff between 40  and  150", con);
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        xxDataContext cc = new xxDataContext();

        public List<www> ss()
        {
            var strLinq = cc.www.Where(p => p.age >= 40 & p.age <= 50);
            return strLinq.ToList();

        }

        public DataTable dt2()
        {
            string str = ConfigurationManager.ConnectionStrings[1].ToString();
            SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlCommand cmd = new SqlCommand("ff", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            con.Close();
            cmd.Dispose();
            return dt;

        }


    }
}

　　其结果

00:00:00.3360374
00:00:00.4130388
00:00:00.0504019
00:00:00.3248878 

 

效率居然相差了6到8倍   测试环境为 500000W条数据 SQL数据库

代码中的存储过程

create proc ff
as
select * from (select * ,ROW_NUMBER() over(order by name desc) as ff from www ) aa where ff between 40 and 150

循环添加数据

declare @a int
set @a=1
while @a<500000
begin
insert into www values('张三',@a)
set @a=@a+1
end

 

强大啊!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!吼吼