使用SqlDataReader填充List< T>在C#中 [英] Using SqlDataReader for filling List<T> in c#

问题描述

我有一个像下面这样的课程，

I have a class like below,

class Student
{
    public string Name {get; set;}
    public string Surname {get; set;}
    public int Age {get; set;}
    public string Address {get; set;}
}

我有一个具有50,000条记录的MySql表.表格的结构如下所示，

And I have a MySql table with 50,000 records. The Structure of the table is like below,

ID        NAME       SURNAME        AGE          ADDRESS
1         Joe        Philip         20           Moscow
2         Misha      Johny          25           London
...

我有一个C#代码，

List<Student> students = new List<Student>();
string sql = "SELECT name,surname,age,address FROM Students";
command.CommandText = sql;
MySqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
    Student st = new Student();
    st.Name = reader["Name"].ToString();
    st.Surname = reader["Surname"].ToString();
    st.Age = Convert.ToInt32(reader["Age"].ToString());
    st.Address = reader["Address"].ToString();
    students.Add(st);
}

但是它工作很慢.您建议采用哪种方法使此代码运行更快?

But it works very slow. Which method do you advice to make this code run faster?

更新:

当我使用此代码时，

DataTable dt = new DataTable();
adapter.Fill(dt);

效果很好，速度也很正常.但是我在自己的班级尝试过什么问题呢?

It works very well and speed is very normal. But what is the problem I try it with my own classes?

推荐答案

如果代码运行缓慢，最大的原因是有50,000条记录.您究竟需要50,000个Student对象是什么?如果您能找到解决问题的方法而无需读取所有这些记录并创建所有这些对象，那么您将获得更快的代码.

If the code runs slowly, the largest cause is that there are 50,000 records. What exactly do you need with 50,000 Student objects? If you can find a way to solve your problem without reading all of those records and creating all of those objects, you'll have faster code.

更新

使用自己的课程就可以了.在大多数情况下，事情运行缓慢，这是因为您的代码受 I/O约束(您将大部分时间都花在等待I/O上).为了避免所有这些I/O，您可以减少检索的数据量(也许通过从数据中消除不相关的列或行)，或者通过更复杂的查询或存储过程来对数据库进行处理.

Using your own class is fine. Most of the time when things run slow, it's because your code is I/O bound (you spend most of your time waiting for I/O). To avoid all that I/O, you can reduce the amount of data you retrieve (perhaps by eliminating irrelevant columns or rows from your data) or doing your processing on the database through a more complex query or stored procedure.

更新2

要回答您的后续问题(为什么创建对象列表比获取DataSet要慢)，我希望将整个查询读取为DataSet只会比创建对象快一点.我不熟悉如何实现MySQL .NET库.令我惊讶的是，这两种方法的速度差异很大.也许MySqlDataReader在做一些愚蠢的事情，例如使用内部DataSet.如果两者之间的性能完全不同，则可能是该库的作者应该解决的问题.

To answer your follow-up question (why creating a list of objects is slower than getting a DataSet), I would expect that reading the entire query as a DataSet would only be slightly faster than object creation. I'm not familiar with how that MySQL .NET library is implemented. It is surprising to me that the two methods would have a large difference in speed. Maybe MySqlDataReader is doing something dumb like using an internal DataSet. If the performance is drastically different between the two, it's probably something the author of that library should fix.

更新3

对于用于批量传输的MySqlDataAdapter或MySqlDataReader的答案?有一个很好的提示；设置阅读器的BatchSize可能会有所帮助.如果批处理大小对于阅读器而言太小，那么将使您的大量记录的效率降低.

This answer for MySqlDataAdapter or MySqlDataReader for bulk transfer? has a good tip; setting the BatchSize of the reader may be helpful. If the batch size is too small for the reader, that would make it less efficient with a large number of records like yours.

这篇关于使用SqlDataReader填充List< T>在C#中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！