查询大数据表的效率对比：LinqtoSQL、EntityFramework、企业库存储过程、ADO.Net

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Diagnostics;
using System.Data.Objects;
using System.Data.SqlClient;

namespace WebApplication1
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            SeewoECP.Model.School model = new SeewoECP.Model.School();
            model.ID = "1";
            model.Name = "test";
            model.Country = "test";
            model.Province = "test";
            model.City = "test";
            model.Address = "test";
            model.ZipCode = "test";
            model.Phone = "test";
            model.IsApproved = true;

            int repeatTimes = 1;

            Stopwatch sw3 = new Stopwatch();
            sw3.Start();

            for (int i = 0; i < repeatTimes; i++)
            {
                DataClasses1DataContext dc = new DataClasses1DataContext();
                
                //IEnumerable schs = dc.ExecuteQuery("Select * from School");
                //System.Data.Linq.Table schools = dc.Schools;
                List schools = dc.Schools.ToList();
                int count = 0;
                foreach (School sc in schools)
                {
                    count++;
                }
                //List schs = schools.ToList();
                Response.Write("
Linq to SQL 查询 记录数：" + schools.Count().ToString());

            }

            sw3.Stop();
            Response.Write("
Linq to SQL 查询 Milliseconds：" + sw3.ElapsedMilliseconds+"");   
            
            Stopwatch sw2 = new Stopwatch();
            sw2.Start();
            DataSet dr = new DataSet();
            for (int i = 0; i < repeatTimes; i++)
            {
                 dr = selectView();
            }
            Response.Write("
视图查询 记录数：" + dr.Tables[0].Rows.Count);

            sw2.Stop();
            Response.Write("
视图查询 Milliseconds：" + sw2.ElapsedMilliseconds + "");


            Stopwatch sw4 = new Stopwatch();
            sw4.Start();

            for (int i = 0; i < repeatTimes; i++)
            {
                ECPDBEntities1 ecp = new ECPDBEntities1();
                ObjectSet classGroup = ecp.ClassGroup;
                //List classGroup = ecp.ClassGroup.ToList();
                //List classGroup =
                //from s in ecp.ClassGroup where s.id < 10 select s.name;

                //ClassGroup cg = classGroup.Single(s => s.ID == "1");
                int count = 0;
                foreach (ClassGroup c in classGroup)
                {
                    count++;
                    //Response.Write( c.ClassName);
                }

                    Response.Write("
Entity Framework 查询  记录数：" + classGroup.Count());
            }

            sw4.Stop();
            Response.Write("
Entity Framework 查询 Milliseconds：" + sw4.ElapsedMilliseconds + "");

            Stopwatch sw = new Stopwatch();
            sw.Start();

            for (int i = 0; i < repeatTimes; i++)
            {

                IDataReader reader = selectPro();
                if (reader != null)
                {
                    int count = 0;
                    while (reader.Read())
                    {
                        count++;
                        //Response.Write(String.Format("{0}, {1}",reader[0], reader[1]));
                    }
                    Response.Write("
企业库存储过程 to DataReader 记录数：" + count);

                    reader.Close();
                }
            }

            sw.Stop();
            Response.Write("
企业库存储过程 to DataReader Milliseconds：" + sw.ElapsedMilliseconds + "");

            Stopwatch sw6 = new Stopwatch();
            sw6.Start();

            DataSet ds=new DataSet();
            for (int i = 0; i < repeatTimes; i++)
            {
                ds = selectProSet();
            }
            Response.Write("
企业库存储过程 to DataSet 记录数：" + ds.Tables[0].Rows.Count);

            sw6.Stop();
            Response.Write("
企业库存储过程 to DataSet Milliseconds：" + sw6.ElapsedMilliseconds + "");

            Stopwatch sw5 = new Stopwatch();
            sw5.Start();

            for (int i = 0; i < repeatTimes; i++)
            {
                SqlDataReader reader = selectNormalPro();
                int count = 0;
                while (reader.Read())
                {
                    count++;
                    //Response.Write(String.Format("{0}, {1}",reader[0], reader[1]));
                }

                Response.Write("
ADO.Net存储过程 to SqlDataReader 记录数：" + count);
                reader.Close();
            }

            sw5.Stop();
            Response.Write("
ADO.Net存储过程 to SqlDataReader Milliseconds：" + sw5.ElapsedMilliseconds + "");

            Stopwatch sw1 = new Stopwatch();
            sw1.Start();

            DataSet ds1 = new DataSet();
            for (int i = 0; i < repeatTimes; i++)
            {
                ds1 = selectSQL();
            }
            Response.Write("
企业库SQL语句直接查询 to DataSet 记录数：" + ds1.Tables[0].Rows.Count);

            sw1.Stop();
            Response.Write("
企业库SQL语句直接查询 to DataSet Milliseconds：" + sw1.ElapsedMilliseconds + "");

            Stopwatch sw8 = new Stopwatch();
            sw8.Start();

            for (int i = 0; i < repeatTimes; i++)
            {
                IDataReader reader = selectSQLReader();
                
                int count = 0;
                while (reader.Read())
                {
                    count++;
                    //Response.Write(String.Format("{0}",reader["ID"]));
                }

                Response.Write("
企业库SQL语句直接查询 to DataReader 记录数：" + count);
                reader.Close();
            }

            sw8.Stop();
            Response.Write("
企业库SQL语句直接查询 to DataReader Milliseconds：" + sw8.ElapsedMilliseconds + "");

            //DataSet d1 = select1();
            //DataSet d2 = select2();
            //IDataReader dr = select3();
        }

        public int Add(SeewoECP.Model.School model,int i)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("InsertSchool");
            db.AddInParameter(dbCommand, "ID", DbType.String, i);
            db.AddInParameter(dbCommand, "Name", DbType.String, model.Name);
            db.AddInParameter(dbCommand, "Country", DbType.String, model.Country);
            db.AddInParameter(dbCommand, "Province", DbType.String, model.Province);
            db.AddInParameter(dbCommand, "City", DbType.String, model.City);
            db.AddInParameter(dbCommand, "Address", DbType.String, model.Address);
            db.AddInParameter(dbCommand, "ZipCode", DbType.String, model.ZipCode);
            db.AddInParameter(dbCommand, "Phone", DbType.String, model.Phone);
            db.AddInParameter(dbCommand, "IsApproved", DbType.Boolean, model.IsApproved);
            return db.ExecuteNonQuery(dbCommand);
        }

        Database db;
        DbCommand dbCommand;
        public DataSet select()
        {
            try
            {
                db = DatabaseFactory.CreateDatabase();
                dbCommand = db.GetStoredProcCommand("SelectSchoolsAll");
                return db.ExecuteDataSet(dbCommand);
            }
            finally
            {
                dbCommand.Connection.Close();
                dbCommand.Connection.Dispose();
            }
        }

        public DataSet select1()
        {
            //Database db1 = DatabaseFactory.CreateDatabase();
            dbCommand = db.GetStoredProcCommand("SelectSystemErrorLogsAll");
            return db.ExecuteDataSet(dbCommand);
        }

        public DataSet select2()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SelectSystemErrorLogsAll");
            return db.ExecuteDataSet(dbCommand);
        }

        public DataSet selectSQL()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand("select * from School");
            return db.ExecuteDataSet(dbCommand);
        }

        public IDataReader selectSQLReader()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand("select * from School");
            return db.ExecuteReader(dbCommand);
        }

        public DataSet selectView()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand("select * from ViewsSchool");
            return db.ExecuteDataSet(dbCommand);
        }

        public DataSet selectProSet()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SelectSchoolsAll");
            return db.ExecuteDataSet(dbCommand);
        }

        public IDataReader selectPro()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SelectSchoolsAll");
            return db.ExecuteReader(dbCommand);
        }

        public SqlDataReader selectNormalPro()
        {
            SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ECPDB;Integrated Security=SSPI;");
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, "SelectSchoolsAll", null);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }

            return command;
        }

    }
}