最近因为要开发大数据量网站，特作比较。

 

 

Linq to SQL 查询 记录数：399997

Linq to SQL 查询 Milliseconds：1910

视图查询 记录数：399997

视图查询 Milliseconds：3435

Entity Framework 查询 记录数：400000

Entity Framework 查询 Milliseconds：4049

企业库存储过程 to DataReader 记录数：399997

企业库存储过程 to DataReader Milliseconds：321

企业库存储过程 to DataSet 记录数：399997

企业库存储过程 to DataSet Milliseconds：2807

ADO.Net存储过程 to SqlDataReader 记录数：399997

ADO.Net存储过程 to SqlDataReader Milliseconds：306

企业库SQL语句直接查询 to DataSet 记录数：399997

企业库SQL语句直接查询 to DataSet Milliseconds：3015

企业库SQL语句直接查询 to DataReader 记录数：399997

企业库SQL语句直接查询 to DataReader Milliseconds：367

 

第二次执行：

 

 

 

代码：

 

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<School> schs = dc.ExecuteQuery<School>("Select * from School");

                

//

System.Data.Linq.Table<School> schools = dc.Schools;

                List<School> schools = dc.Schools.ToList();

                

int count = 

0;

                

foreach (School sc 

in schools)

                {

                    count++;

                }

                

//

List<School> schs = schools.ToList();

                Response.Write(

"

<br>Linq to SQL 查询 记录数：

" + schools.Count().ToString());

            }

            sw3.Stop();

            Response.Write(

"

<br>Linq to SQL 查询 Milliseconds：<font color='#FF0000'>

" + sw3.ElapsedMilliseconds+

"

</font>

");   

            

            Stopwatch sw2 = 

new Stopwatch();

            sw2.Start();

            DataSet dr = 

new DataSet();

            

for (

int i = 

0; i < repeatTimes; i++)

            {

                 dr = selectView();

            }

            Response.Write(

"

<br>视图查询 记录数：

" + dr.Tables[

0].Rows.Count);

            sw2.Stop();

            Response.Write(

"

<br>视图查询 Milliseconds：<font color='#FF0000'>

" + sw2.ElapsedMilliseconds + 

"

</font>

");

            Stopwatch sw4 = 

new Stopwatch();

            sw4.Start();

            

for (

int i = 

0; i < repeatTimes; i++)

            {

                ECPDBEntities1 ecp = 

new ECPDBEntities1();

                ObjectSet<ClassGroup> classGroup = ecp.ClassGroup;

                

//

List<ClassGroup> classGroup = ecp.ClassGroup.ToList();

                

//

List<ClassGroup> 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(

"

<br>Entity Framework 查询  记录数：

" + classGroup.Count());

            }

            sw4.Stop();

            Response.Write(

"

<br>Entity Framework 查询 Milliseconds：<font color='#FF0000'>

" + sw4.ElapsedMilliseconds + 

"

</font>

");

            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(

"

<br>企业库存储过程 to DataReader 记录数：

" + count);

                    reader.Close();

                }

            }

            sw.Stop();

            Response.Write(

"

<br>企业库存储过程 to DataReader Milliseconds：<font color='#FF0000'>

" + sw.ElapsedMilliseconds + 

"

</font>

");

            Stopwatch sw6 = 

new Stopwatch();

            sw6.Start();

            DataSet ds=

new DataSet();

            

for (

int i = 

0; i < repeatTimes; i++)

            {

                ds = selectProSet();

            }

            Response.Write(

"

<br>企业库存储过程 to DataSet 记录数：

" + ds.Tables[

0].Rows.Count);

            sw6.Stop();

            Response.Write(

"

<br>企业库存储过程 to DataSet Milliseconds：<font color='#FF0000'>

" + sw6.ElapsedMilliseconds + 

"

</font>

");

            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(

"

<br>ADO.Net存储过程 to SqlDataReader 记录数：

" + count);

                reader.Close();

            }

            sw5.Stop();

            Response.Write(

"

<br>ADO.Net存储过程 to SqlDataReader Milliseconds：<font color='#FF0000'>

" + sw5.ElapsedMilliseconds + 

"

</font>

");

            Stopwatch sw1 = 

new Stopwatch();

            sw1.Start();

            DataSet ds1 = 

new DataSet();

            

for (

int i = 

0; i < repeatTimes; i++)

            {

                ds1 = selectSQL();

            }

            Response.Write(

"

<br>企业库SQL语句直接查询 to DataSet 记录数：

" + ds1.Tables[

0].Rows.Count);

            sw1.Stop();

            Response.Write(

"

<br>企业库SQL语句直接查询 to DataSet Milliseconds：<font color='#FF0000'>

" + sw1.ElapsedMilliseconds + 

"

</font>

");

            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(

"

<br>企业库SQL语句直接查询 to DataReader 记录数：

" + count);

                reader.Close();

            }

            sw8.Stop();

            Response.Write(

"

<br>企业库SQL语句直接查询 to DataReader Milliseconds：<font color='#FF0000'>

" + sw8.ElapsedMilliseconds + 

"

</font>

");

            

//

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;

        }

    }

}