高手请进【性能】为什么相差这么大？

Question

public class Book
{
    private string _BookId;
    public string BookId
    {
        get { return _BookId; }
        set { _BookId = value; }
    }    private string _BookName;
    public string BookName
    {
        get { return _BookName; }
        set { _BookName = value; }
    }    private string _BookAuthor;
    public string BookAuthor
    {
        get { return _BookAuthor; }
        set { _BookAuthor = value; }
    }    private decimal _BookPrice;
    public decimal BookPrice
    {
        get { return _BookPrice; }
        set { _BookPrice = value; }
    }
}
//性能为什么相差这么大？
方法一：
  public SqlCommand InitStoredProcedure(
      string storedProcedure,
      SqlConnection connection)
    {        CheckConnection(ref connection);        SqlCommand command = new SqlCommand(storedProcedure, connection);
        command.CommandType = CommandType.StoredProcedure;        DeriveParameters(command);
        //SqlCommandBuilder.DeriveParameters(command);        return command;
    }    private void DeriveParameters(SqlCommand command)
    {
        DateTime start = DateTime.Now;
        DateTime end = DateTime.Now;
        int count = 0;
        while (true)
        {
            try
            {
                count++;
                start = DateTime.Now;
                SqlCommandBuilder.DeriveParameters(command);
                end = DateTime.Now;
                break;
                //foreach (SqlParameter param in command.Parameters)
                //{
                //    if (param.SqlDbType == SqlDbType.Xml)
                //        param.Size = 1; // this is needed because sqlconnection (for Execute...) requires a size specified if the column is Xml
                //}
            }
            catch (SqlException sqlex)
            {
                if (count < 2 && sqlex.Message.StartsWith("A transport-level error"))   // do it only once
                {
                    SqlConnection.ClearPool(command.Connection);
                    command.Connection.Open();
                    continue;
                }
                throw new Exception(command.CommandText + ": Took " + end.Subtract(start).TotalSeconds + " seconds\n" + sqlex.ToString());
            }
            catch (System.IO.IOException ioex)
            {
                if (count < 2 && ioex.ToString().Contains("System.Net.Sockets.SocketException: Connection reset by peer"))   // do it only once
                {
                    SqlConnection.ClearPool(command.Connection);
                    command.Connection.Open();
                    continue;
                }
                throw new Exception(command.CommandText + "\n" + ioex.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception(command.CommandText + ": Took " + end.Subtract(start).TotalSeconds + " seconds\n" + ex.ToString());
            }
        }
    }
    private void CheckConnection(ref SqlConnection connection)
    {
        if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
    }
    public Book[] GetBook(int id)
    {
        string strsql = "dbo.getBookById";        using (SqlConnection sqlConnection = new SqlConnection(ConfigInfo.SourceTrackLogDataEditorConnStr))
        {
            using (SqlCommand sqlCommand = InitStoredProcedure(strsql, sqlConnection))
            {
                sqlCommand.Parameters["@p_Id"].Value =id;
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    ArrayList list = new ArrayList();
                    int idIndex = reader.GetOrdinal("r_Id");
                    int bookIdIndex = reader.GetOrdinal("r_BookId");
                    int bookNameIndex = reader.GetOrdinal("r_BookName");
                    int bookAuthorIndex = reader.GetOrdinal("r_BookAuthor");
                    int bookPriceIndex = reader.GetOrdinal("r_BookPrice");
                    while (reader.Read())
                    {
                        Book book = new Book();
                        book.BookAuthor = reader.GetString(bookAuthorIndex);
                        book.BookId = reader.GetString(bookIdIndex);
                        book.BookName = reader.GetString(bookNameIndex);
                        book.BookPrice = reader.GetDecimal(bookPriceIndex);
                        list.Add(book);
                    }
                    return list.ToArray(typeof(Book)) as Book[];
                }
            }
        }
    }
方法二： public Book[] GetBook3(int id)
    {
        ArrayList list = new ArrayList();
        string strsql = "getBookById";
        SqlConnection connection = new SqlConnection(ConfigInfo.SourceTrackLogDataEditorConnStr);
        if (connection.State == ConnectionState.Closed)
        {            connection.Open();
            num++;        }        try
        {
            SqlCommand sqlCommand = new SqlCommand(strsql);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Connection = connection;
            SqlParameter[] param ={new SqlParameter("@p_Id",SqlDbType.BigInt)};//, SqlDbType.BigInt), new SqlParameter("@p_BookId", SqlDbType.Char, 10), new SqlParameter("@p_BookName", SqlDbType.NVarChar, 50), new SqlParameter("@p_BookAuthor", SqlDbType.NVarChar, 50), new SqlParameter("@p_BookPrice", SqlDbType.Decimal), new SqlParameter("@p_RetId", SqlDbType.BigInt) };
            param[0].Value =id;
            
            
            foreach (SqlParameter par in param)
            {
                sqlCommand.Parameters.Add(par);
            }              using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
           
                    int idIndex = reader.GetOrdinal("r_Id");
                    int bookIdIndex = reader.GetOrdinal("r_BookId");
                    int bookNameIndex = reader.GetOrdinal("r_BookName");
                    int bookAuthorIndex = reader.GetOrdinal("r_BookAuthor");
                    int bookPriceIndex = reader.GetOrdinal("r_BookPrice");
                    while (reader.Read())
                    {
                        Book book = new Book();
                        book.BookAuthor = reader.GetString(bookAuthorIndex);
                        book.BookId = reader.GetString(bookIdIndex);
                        book.BookName = reader.GetString(bookNameIndex);
                        book.BookPrice = reader.GetDecimal(bookPriceIndex);
                        list.Add(book);
                   
                    }
                   
                }
        }
        catch (Exception ex)
        {        }
        finally
        {
            connection.Close();
            connection.Dispose();
        }
        return list.ToArray(typeof(Book)) as Book[];
    }
 经过很多测试：
方法二查询效率是方法一的2倍。
表
Create table T_Book
( Id bigint Identity(1,1) primary key not null,
  BookId  Char(10) not null,
  BookName nvarchar(50) not null,
  BookAuthor nvarchar(50) not null,
  BookPrice decimal(18,2) default(0) not null
 )
存储过程CREATE PROCEDURE dbo.getBookById
   @p_Id bigint  AS  
   SET NOCOUNT ON  
     
   SELECT Id    AS r_Id,  
          BookId    AS r_BookId,  
          BookName    AS r_BookName,  
          BookAuthor    AS r_BookAuthor, 
          BookPrice        AS r_BookPrice  
        
     FROM dbo.T_Book  
    WHERE Id = @p_Id       
     
   RETURN @@ERROR  

Answer 1

方法2是直接用的ado.net. 方法1无非是多做了些准备工作。两者没看出有什么实质上的效率的区别。可能是方法2的InitStoredProcedure写得太罗嗦了吧。
建议你贴代码用那个#号按钮。这样看，实在看得太费劲了

Answer 2

    public SqlCommand InitStoredProcedure(
      string storedProcedure,
      SqlConnection connection)
    {        CheckConnection(ref connection);        SqlCommand command = new SqlCommand(storedProcedure, connection);
        command.CommandType = CommandType.StoredProcedure;        //DeriveParameters(command);-- 我把这里注视了
        SqlCommandBuilder.DeriveParameters(command);
        return command;
    }
我用SQL Server Profile 跟踪了代码。两种方式生成的SQL不同。

Answer 3

{
  private string _BookId;
  public string BookId
  {
  get { return _BookId; }
  set { _BookId = value; }
  }  private string _BookName;
  public string BookName
  {
  get { return _BookName; }
  set { _BookName = value; }
  }  private string _BookAuthor;
  public string BookAuthor
  {
  get { return _BookAuthor; }
  set { _BookAuthor = value; }
  }  private decimal _BookPrice;
  public decimal BookPrice
  {
  get { return _BookPrice; }
  set { _BookPrice = value; }
  }
}
//性能为什么相差这么大？
//方法一：
  public SqlCommand InitStoredProcedure(
  string storedProcedure,
  SqlConnection connection)
  {  CheckConnection(ref connection);  SqlCommand command = new SqlCommand(storedProcedure, connection);
  command.CommandType = CommandType.StoredProcedure;  //DeriveParameters(command);
  SqlCommandBuilder.DeriveParameters(command);  return command;
  }  private void DeriveParameters(SqlCommand command)
  {
  DateTime start = DateTime.Now;
  DateTime end = DateTime.Now;
  int count = 0;
  while (true)
  {
  try
  {
  count++;
  start = DateTime.Now;
  SqlCommandBuilder.DeriveParameters(command);
  end = DateTime.Now;
  break;
  //foreach (SqlParameter param in command.Parameters)
  //{
  // if (param.SqlDbType == SqlDbType.Xml)
  // param.Size = 1; // this is needed because sqlconnection (for Execute...) requires a size specified if the column is Xml
  //}
  }
  catch (SqlException sqlex)
  {
  if (count < 2 && sqlex.Message.StartsWith("A transport-level error")) // do it only once
  {
  SqlConnection.ClearPool(command.Connection);
  command.Connection.Open();
  continue;
  }
  throw new Exception(command.CommandText + ": Took " + end.Subtract(start).TotalSeconds + " seconds\n" + sqlex.ToString());
  }
  catch (System.IO.IOException ioex)
  {
  if (count < 2 && ioex.ToString().Contains("System.Net.Sockets.SocketException: Connection reset by peer")) // do it only once
  {
  SqlConnection.ClearPool(command.Connection);
  command.Connection.Open();
  continue;
  }
  throw new Exception(command.CommandText + "\n" + ioex.ToString());
  }
  catch (Exception ex)
  {
  throw new Exception(command.CommandText + ": Took " + end.Subtract(start).TotalSeconds + " seconds\n" + ex.ToString());
  }
  }
  }
  private void CheckConnection(ref SqlConnection connection)
  {
  if (connection.State == ConnectionState.Closed)
  {
  connection.Open();
  }
  }
  public Book[] GetBook(int id)
  {
  string strsql = "dbo.getBookById";  using (SqlConnection sqlConnection = new SqlConnection(ConfigInfo.SourceTrackLogDataEditorConnStr))
  {
  using (SqlCommand sqlCommand = InitStoredProcedure(strsql, sqlConnection))
  {
  sqlCommand.Parameters["@p_Id"].Value =id;
  using (SqlDataReader reader = sqlCommand.ExecuteReader())
  {
  ArrayList list = new ArrayList();
  int idIndex = reader.GetOrdinal("r_Id");
  int bookIdIndex = reader.GetOrdinal("r_BookId");
  int bookNameIndex = reader.GetOrdinal("r_BookName");
  int bookAuthorIndex = reader.GetOrdinal("r_BookAuthor");
  int bookPriceIndex = reader.GetOrdinal("r_BookPrice");
  while (reader.Read())
  {
  Book book = new Book();
  book.BookAuthor = reader.GetString(bookAuthorIndex);
  book.BookId = reader.GetString(bookIdIndex);
  book.BookName = reader.GetString(bookNameIndex);
  book.BookPrice = reader.GetDecimal(bookPriceIndex);
  list.Add(book);
  }
  return list.ToArray(typeof(Book)) as Book[];
  }
  }
  }
  }
方法二： public Book[] GetBook3(int id)
  {
  ArrayList list = new ArrayList();
  string strsql = "getBookById";
  SqlConnection connection = new SqlConnection(ConfigInfo.SourceTrackLogDataEditorConnStr);
  if (connection.State == ConnectionState.Closed)
  {  connection.Open();
  num++;  }  try
  {
  SqlCommand sqlCommand = new SqlCommand(strsql);
  sqlCommand.CommandType = CommandType.StoredProcedure;
  sqlCommand.Connection = connection;
  SqlParameter[] param ={new SqlParameter("@p_Id",SqlDbType.BigInt)};//, SqlDbType.BigInt), new SqlParameter("@p_BookId", SqlDbType.Char, 10), new SqlParameter("@p_BookName", SqlDbType.NVarChar, 50), new SqlParameter("@p_BookAuthor", SqlDbType.NVarChar, 50), new SqlParameter("@p_BookPrice", SqlDbType.Decimal), new SqlParameter("@p_RetId", SqlDbType.BigInt) };
  param[0].Value =id;
    
    
  foreach (SqlParameter par in param)
  {
  sqlCommand.Parameters.Add(par);
  }  using (SqlDataReader reader = sqlCommand.ExecuteReader())
  {
    
  int idIndex = reader.GetOrdinal("r_Id");
  int bookIdIndex = reader.GetOrdinal("r_BookId");
  int bookNameIndex = reader.GetOrdinal("r_BookName");
  int bookAuthorIndex = reader.GetOrdinal("r_BookAuthor");
  int bookPriceIndex = reader.GetOrdinal("r_BookPrice");
  while (reader.Read())
  {
  Book book = new Book();
  book.BookAuthor = reader.GetString(bookAuthorIndex);
  book.BookId = reader.GetString(bookIdIndex);
  book.BookName = reader.GetString(bookNameIndex);
  book.BookPrice = reader.GetDecimal(bookPriceIndex);
  list.Add(book);
    
  }
    
  }
  }
  catch (Exception ex)
  {  }
  finally
  {
  connection.Close();
  connection.Dispose();
  }
  return list.ToArray(typeof(Book)) as Book[];
  }
 //经过很多测试：方法二查询效率是方法一的2倍。[code=SQL]Create table T_Book
( Id bigint Identity(1,1) primary key not null,
  BookId Char(10) not null,
  BookName nvarchar(50) not null,
  BookAuthor nvarchar(50) not null,
  BookPrice decimal(18,2) default(0) not null
 )
--存储过程CREATE PROCEDURE dbo.getBookById
  @p_Id bigint   AS   
  SET NOCOUNT ON   
    
  SELECT Id AS r_Id,   
  BookId AS r_BookId,   
  BookName AS r_BookName,   
  BookAuthor AS r_BookAuthor,  
  BookPrice AS r_BookPrice   
    
  FROM dbo.T_Book   
  WHERE Id = @p_Id   
    
  RETURN @@ERROR    
[/code]

Answer 4

SQL Server Profile 跟踪了代码
 方法一：exec sp_reset_connection 
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
exec [LogData].[sys].[sp_procedure_params_managed] @procedure_name=N'getBookById',@procedure_schema=N'dbo'
exec dbo.getBookById @p_Id=1
方法二：exec sp_reset_connection 
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
exec getBookById @p_Id=2