数据表占用太多时间来加载50000条记录。建立其解决方案。 [英] Data table taking too much time to load 50000 records.suggest its solution.

问题描述

你好朋友，



目前我正面临数据表加载问题。我的查询执行速度快但返回50000条记录，当我跟踪我的代码时，它显示数据表填充需要花费很多时间。我想将我的数据存储在Datatable中并想要在Excel中导出但是花费了太多时间。请询问是否有任何人有最好的解决方案。



注意：由于客户需要excel，我需要这么多数据。



谢谢，

Chintan Rana



我尝试过：



我的代码如下

-----------------

 SqlCommand cmd =  new  SqlCommand（）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.CommandText =   sp_web_reports; 
 cmd.Parameters.AddWithValue（  @ mode， 50 ）; 
 cmd.Parameters.AddWithValue（  @ From_Date，Convert.ToDateTime（txt_from_date.Text ））; 
 cmd.Parameters.AddWithValue（  @ TO_Date，Convert.ToDateTime（txt_to_date.Text ））; 
 cmd.Parameters.AddWithValue（  @ GroupCode，drp_vehicle_group.SelectedValue）; 
 conn.Open（）; 
 cmd.Connection = conn; 
 cmd.CommandTimeout =  0 ; 
 SqlDataReader reader = cmd.ExecuteReader（）; 
 conn.Close（）; 
 DataTable dt =  new  Good_Issue_Vehicle_Report.dt_GoodIssueVehicleDataTable（）; 
  dt.Load（读者）; < -----花费太多时间
 如果（dt .Rows.Count >   0 ）
 {
导出代码在 Excel 
} 

解决方案

你可以做的太多了这里只要您使用DataTable，这是一个断开连接的结构（意味着它将在一个循环中加载SQL中的所有数据）...

因为看起来你的最终目标是导出到Excel （CSV？）你可以使用一个DataReader（你已经拥有一个）并循环遍历它并逐个记录导出数据...在这种情况下你可以获得巨大的性能...

另外考虑不要从SQL中带来任何不必要的东西 - 它会占用更多内存！

以这种方式试试：

 SqlConnection conn =  null ; 
 SqlCommand cmd =  null ; 
 SqlDataAdapter adapter =  null ; 
 DataSet dataset =  null ; 
 DataTable dataTable =  null ; 
 
 尝试 
 {
 使用（conn =  new  SqlConnection（ 连接字符串） ）
 {
 conn.Open（）; 
 使用（cmd =  new  SqlCommand（   sp_web_reports，conn）{CommandType = CommandType.StoredProcedure}）
 {
 cmd.Parameters.AddWithValue（  @ mode， 50 ）; 
 cmd.Parameters.AddWithValue（  @ From_Date，Convert.ToDateTime（txt_from_date.Text ））; 
 cmd.Parameters.AddWithValue（  @ TO_Date，Convert.ToDateTime（txt_to_date.Text ））; 
 cmd.Parameters.AddWithValue（  @ GroupCode，drp_vehicle_group.SelectedValue）; 
 使用（adapter =  new  SqlDataAdapter（cmd））
 {
 dataset =  new  DataSet（）; 
 adapter.Fill（dataset）; 
 dataTable = dataset.Tables [ 0 ]; 
} 
} 
} 
} 
  catch （例外情况）
 {
  //  处理异常 
} 
 
  //  如果你没有例外，你可以使用你的dataTable对象。 

使用语句可以清除适配器，命令和连接对象而无需编写代码专门处理。此外，您应该使用 try / catch 块包装 ALL ADO代码并适当地处理异常。在第一个之前使用语句定义各种对象允许您在发生异常时在调试器中检查它们。



如果要导出到Excel，您只需要调用 dataTable.WriteXML（filename），Excel 2007+就可以读取它。

您是否已查看过相关表格的索引？

您的SQL语句是否有select *？



c＃ - DataTable加载非常慢 - 堆栈溢出 [ ^ ]

c＃ - SQL查询运行良好的SSMS在ASP.NET中运行非常慢 - Stack Overflow [ ^

Hello Friends,

Currently i am facing data table loading issue.My query execute fast but return 50000 records and when i am tracing my code ,its shows that Data table fill taking much time.I want to store my Data in Datatable and want to export in Excel but its taking too much time. Please request if any one has its best solution.

Note : I am taking this much data because client need it in excel.

Thanks,
Chintan Rana

What I have tried:

My Code As below
-----------------

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_web_reports";
cmd.Parameters.AddWithValue("@mode", 50);
cmd.Parameters.AddWithValue("@From_Date", Convert.ToDateTime(txt_from_date.Text));
cmd.Parameters.AddWithValue("@TO_Date", Convert.ToDateTime(txt_to_date.Text));
cmd.Parameters.AddWithValue("@GroupCode", drp_vehicle_group.SelectedValue);
conn.Open();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
conn.Close();
DataTable dt = new Good_Issue_Vehicle_Report.dt_GoodIssueVehicleDataTable();
dt.Load(reader);   <----- Taking too much time
if (dt.Rows.Count > 0)
{
Export Code in Excel
}

解决方案

There is not too much you can do here as long as you use DataTable, which is a disconnected structure (means it will load all the data from the SQL in one loop)...
As it seems you final goal is to export to Excel (CSV?) you may use a DataReader (you already have one) and loop over it and export data record-by-record... In this case you may gain huge performance...
Also consider NOT to bring anything unnecessary from the SQL - it takes up more memory!

Try it this way:

SqlConnection  conn      = null;
SqlCommand     cmd       = null;
SqlDataAdapter adapter   = null;
DataSet        dataset   = null;
DataTable      dataTable = null;

try
{
    using (conn = new SqlConnection("connection string"))
    {
        conn.Open();
        using (cmd = new SqlCommand("sp_web_reports", conn) { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue("@mode",      50);
            cmd.Parameters.AddWithValue("@From_Date", Convert.ToDateTime(txt_from_date.Text));
            cmd.Parameters.AddWithValue("@TO_Date",   Convert.ToDateTime(txt_to_date.Text));
            cmd.Parameters.AddWithValue("@GroupCode", drp_vehicle_group.SelectedValue);
            using (adapter = new SqlDataAdapter(cmd))
            {
                dataset = new DataSet();
                adapter.Fill(dataset);
                dataTable = dataset.Tables[0];
            }
        }
    }
}
catch (Exception ex)
{
    // handle the exception
}

// if you get here without an exception, you can work with your dataTable object.

The using statement has the benefit of cleaning up the adapter, command, and connection objects without writing code to specifically handle that. Furthermore, you should wrap ALL ADO code with try/catch blocks and handle the exceptions appropriately. Defining the various objects before the first using statement allows you to examine them in the debugger in the event an exception is raised.

If you want to export to Excel, all you have to do is call dataTable.WriteXML("filename"), and Excel 2007+ can read it.

Have you reviewed your indexing on the tables in question?
Does your SQL Statement have a "select *"?

c# - DataTable Load very slow - Stack Overflow[^]
c# - SQL Query that runs fine in SSMS runs very slow in ASP.NET - Stack Overflow[^]

这篇关于数据表占用太多时间来加载50000条记录。建立其解决方案。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！