SqlDataAdapter 内存使用情况
我有一个将 1500,000 个数据加载到网格的应用程序。问题是它需要大量内存。 （1.8 GB）
我观察到以下情况，
- 如果我在 SQL 查询上运行，则相同的查询 分析器在应用程序中需要大约 60MB
- 如果我只是执行， ExecuteNonQuery() 它也需要 大约 60MB 左右。
当我执行它以获取数据表形式的输出时，问题就出现了，我觉得我这样做的方式存在一些问题。请帮忙。
这是我的做法，（即使我调用 SP，它也会执行我作为参数传递的 sql）
using (var conn = new SqlConnection(connStr))
{
SqlCommand command = conn.CreateCommand();
//DbCommand command = conn.CreateCommand();
command.CommandTimeout = 30000;
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spName;
SqlDataAdapter da = new SqlDataAdapter(command);
dt = new DataTable();
da.Fill(dt);
if (dt != null)
{
if (dt.Rows.Count == 0)
{
dt = null;
}
}
conn.Close();
}
I have an application which loads 1500,000 to a grid. Issue is it takes lots of memory. (1.8 GB)
I observed following,
- Same query if I run on SQL Query
Analyzer it takes around 60MB - In application if I just execute
ExecuteNonQuery() it also takes
somewhere around 60MB.
Issue comes when I execute it to get output as DataTable, I feel there is some issue in the way I do it. Please help.
Here's the way I do it, (even though I call a SP, it too execute the sql which I pass as a parameter)
using (var conn = new SqlConnection(connStr))
{
SqlCommand command = conn.CreateCommand();
//DbCommand command = conn.CreateCommand();
command.CommandTimeout = 30000;
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spName;
SqlDataAdapter da = new SqlDataAdapter(command);
dt = new DataTable();
da.Fill(dt);
if (dt != null)
{
if (dt.Rows.Count == 0)
{
dt = null;
}
}
conn.Close();
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将文本文件中的大量数据加载到数据表/数据网格时，我遇到了类似的问题。我正在加载的许多数据无法轻松键入，因为这些文件来自许多不同的来源且有许多变体，因此它们都只是作为文本列加载。我正在加载的很多数据只是一个字符长
本文讨论 .NET 中字符串的开销以及这对单个字符的许多元素的影响。它提供了使用 ANT 分析器的示例。我得到了试用版并用它来确认我所看到的用法。由于我拥有的数据存在差异，我无法调整列类型，但如果您发现数据列可以输入为字符串以外的其他内容，那么您可能会发现一些重大改进。
I faced a similar problem when loading up a lot of data from text files into datatables/datagrids. A lot of the data I am loading cannot easily be typed as the files come from many different sources with many variations, so they are all simply loaded in as text columns. A lot of the data I am loading is simply one character long
This article discusses the overhead of strings in .NET and the effect this has on lots of elements of just one character. It provides examples using ANTs profiler. I got the trial version and used it to confirm the usage I was seeing. Due to the variations in the data I had, I couldn't adjust my column types, but if you find you have columns of data that can by typed as something other than string, then you might find some significant improvements.
也许您可以实现某种分页或“按需加载”？
当运行此方法两次时，您会处理旧的数据表，不是吗？
Perhaps you can implement somekind of paging or "load on demand" ?
When running this method twice, you dispose the old datatable, don't you ?