最近因为要开发大数据量网站,特作比较。
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
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
第二次执行:
代码:
View Code
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;
}
}
}
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;
}
}
}