导出大量数据

发布于 2024-12-08 19:24:37 字数 1246 浏览 1 评论 0原文

这是我的问题..

我们的网站中有两种类型的报告，网格中显示的数据和立即下载为报告的数据。

这些报告可以包含几年的数据（超过 100 万行），我们一直允许客户根据日期范围下载数据，但我们已经开始限制他们查看数据的时间，以防止我们网站出现性能问题。然而，即使在很小的日期范围内，数据仍然变得相当大，现在它们正在扩展，如果下载太多，我们的内存会在几演出中激增并耗尽内存。

我的问题是，我不想限制他们的数据，所以我试图找出一个好的解决方案，让他们可以根据需要下载尽可能多的数据。

我可以通过仅返回每页数据来限制他们看到的内容，这样就不会出现性能问题，但下载始终是问题所在。

我研究过异步，但未能成功使其工作，因为当我加载数据时它会占用内存。

有想法吗？想法？建议？

代码示例：

// Get Data

SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da;
DataSet ds = new DataSet();

con.ConnectionString = "MyConnectionString";
con.Open();

cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MyStoredProc";
da = new SqlDataAdapter(cmd);
da.Fill(ds);

con.Close();

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
DataGrid dg = new DataGrid();
dg.DataSource = ds.Tables[0];
dg.DataBind();
dg.RenderControl(htw);

Response.ClearContent();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=Report.xls");
Response.Write(sw.ToString());
Response.End();

当我使用我的数据运行此程序时......大约有 800k 行，我的内存出现峰值并且出现内存不足错误，并使事情变得更糟......它总是占用 RenderControl 直到完成

原文

Here is my issue..

We have 2 types of reports in our site, data shown in grid and data instantly downloaded as a report.

These reports can contain a few years of data (1+ million rows), we have been allowing our customers to download the data against a date range, but we have started limited how long they can view data to prevent performance issues in our site. However, the data is still getting pretty large even on a small date range, now that they are expanding, and if they download too much, our memory spikes over a few gigs and run out of memory.

Question I have is, I rather not limit their data so I'm trying to figure out a good solution to allow them to download as much as they want.

I can limit what they see by only returning data per page so there is no performance issues, however downloading is always the problem.

I've looked into async but haven't successfully been able to get that to work, as it spikes the memory when I'm loading the data.

Ideas? Thoughts? Suggestions?

Code Example:


// Get Data

SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da;
DataSet ds = new DataSet();

con.ConnectionString = "MyConnectionString";
con.Open();

cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MyStoredProc";
da = new SqlDataAdapter(cmd);
da.Fill(ds);

con.Close();

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
DataGrid dg = new DataGrid();
dg.DataSource = ds.Tables[0];
dg.DataBind();
dg.RenderControl(htw);

Response.ClearContent();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=Report.xls");
Response.Write(sw.ToString());
Response.End();

When I run this with my data.. which is approximately 800k rows, my memory spikes and I get an out of memory error, and make things worse.. it always hogs up at the RenderControl until its done

分享到QQ

分享到微博