提高从SQL Server返回数据时的性能

我在SQL Server Management Studio中有一个存储过程，在大约10秒内产生2,675条记录。

然而，当我从我的c#应用程序调用它时，需要1:05来填充DataTable。我能做些什么来提高表现吗?

下面是我如何填充DataTable:

public static DataTable GetDataTable(string procName, params SqlParameter[] procParams)
{
    using (_conn = new SqlConnection(_connStr))
    {
        SqlCommand cmd = _conn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = procName;
        if (procParams != null)
        {
            foreach (SqlParameter p in procParams)
            {
                cmd.Parameters.Add(p);
            }
        }
        _conn.Open();
        var da = new SqlDataAdapter(cmd);
        var table = new DataTable();
        da.Fill(table);
        return table;
    }
}

特别在这一点上:

var table = new DataTable();
da.Fill(table);

几乎所有的时间都被占用了(大约60秒)。

我以前用这种方法处理过大数据，从来没有遇到过这么大的延迟。

有什么建议或想法吗?

更新:

我们还尝试了这样的DataReader:

var dataReader = cmd.ExecuteReader();
table.Load(dataReader);
dataReader.Close();

更新:

在桌面报表查看器中为相同的报表运行相同的sp，可以在12秒内获得结果。这是在网页和桌面上运行的相同的报告。为什么这么慢?

叫SP:

这是SP运行为报告获取数据。

ALTER PROCEDURE [dbo].[rptCheckRegDetail]
@ldStartDt as char(10) = '',
@ldEndDt as char(10) = '',
@lcStartCkNo as char(10)= ' ',
@lcEndCkNo as char(10) =' ',
@lcUniqSupNo as char(10)=' ',
@lcBk_Uniq as char(10)= ' ',
@lnStatus as int=1
as
begin
  declare @ChkHd table  (ApChk_uniq char(10),Bank char(35),Bk_acct_no char(15),
      iCheckno char(10),Checkno char(10),CheckDate smalldatetime,
      SupName char(35),CheckAmt numeric(12,2),Status char(15), Detail char(6),
      CheckNote text,ReconcileStatus char(1),ReconciledDate smalldatetime)
  Insert into @ChkHd        
    exec [CheckRegView] @ldStartDt,@ldEndDt,@lcStartCkNo,@lcEndCkNo,@lcUniqSupNo,@lcBk_Uniq,@lnStatus                       
  SELECT c1.ApChk_uniq, c1.Bank, c1.Bk_acct_no, c1.iCheckno, c1.Checkno, 
    c1.CheckDate, c1.SupName,
    case when 
        ROW_NUMBER() over (partition by c1.apchk_uniq order by c1.checkno) = 1 
      then CheckAmt else cast(0.00 as numeric(12,2)) end as CheckAmt,
    c1.status,c1.checknote,Apchkdet.item_no, Apchkdet.ponum, Apchkdet.invno,
    Apchkdet.invdate, Apchkdet.due_date, Apchkdet.item_desc, Apchkdet.invamount, 
    Apchkdet.disc_tkn, Apchkdet.aprpay,Apchkdet.apchk_uniq, Apchkdet.itemnote, 
    MICSSYS.LIC_NAME
      FROM      @ChkHd as C1 
        inner join apchkdet on apchkdet.APCHK_UNIQ = c1.ApChk_uniq
        cross join micssys
      ORDER BY  Checkno,Apchkdet.item_no
end

public static List<Dictionary<string, object>> GetDataTable(string procName, params SqlParameter[] procParams)
{
    using (_conn = new SqlConnection(_connStr))
    {
        using (SqlCommand cmd = _conn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            if (procParams != null)
            {
                foreach (SqlParameter p in procParams)
                {
                    cmd.Parameters.Add(p);
                }
            }
            _conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                var result = new List<Dictionary<string, object>>();
                while (reader.Read())
                {
                    var row = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                       string fieldName = reader.GetName(i);
                       row.Add(fieldName, reader[fieldName]);
                    }
                    result.Add(row);
                }
                return result;
            }
        }
    }
}

foreach (var result in results)
{
    foreach (KeyValuePair<string, object> field in result)
    {
        string fieldName = field.Key;
        string fieldValue = field.Value.ToString(); // Cast to correct type here
    }
}

Cast(@smalldatetimevar as datetime) smalldatetimevar