locked
Read form ExecuteReader RRS feed

  • Question

  • User-128423884 posted

    Hi, I need to read data from a DataReader and currently i'm reading row by row with Read method but is a little slow (30s for 12k rows more or less). If I read the data with a DataSet.Load(IDataReader,...) the time is more or less the same (2 seconds less).

    Anyone know any other way to load the data that can reduce this times?

    Thanks in advance

    Tuesday, November 27, 2012 1:07 PM

All replies

  • User824124681 posted

    Why do you read one by one instead you can assign it directly to your data control. 

    Tuesday, November 27, 2012 1:48 PM
  • User-128423884 posted

    I need to modify this data and adding more info from othes sources.

    Tuesday, November 27, 2012 6:42 PM
  • User614805505 posted

    Hi,

    Can you post your code for both approaches?

    If you have any data manupulation happen in between DataReader.Read, it will also cost the processing time.

    The pay load for the DataSet (DataAdapter) approach is not end at DataSet.Load. You have to consider the traffic, memory, processing load to return the result to your Presentation layer as well.

    Tuesday, November 27, 2012 10:14 PM
  • User-128423884 posted

    using (var reader = objCommand.Command.ExecuteReader())

    {

    while (reader.Read())

    { ... }

    }

    ____________________________

    using (var reader = objCommand.Command.ExecuteReader())

    {

     var dt = new DataTable("");

    dt.Load(reader);

    }

    Wednesday, November 28, 2012 2:55 AM
  • User614805505 posted

    while (reader.Read())

    { ... }

    Do you mind to share the code in the while block? Maybe the data casting (Boxing or Unboxing) make the processing slow.

    Wednesday, November 28, 2012 3:09 AM
  • User-128423884 posted

    var lst = new List<Products>();

    while (reader.Read())

    {

    lst.Add(new Product{

    Code = reader["code"],

    ... x30 fields

    });

    }

    In addition if I load the data into the DataTable and after i add the items to the list, the time is less than 1s, so seems that the problem not are the unboxing

    Wednesday, November 28, 2012 4:42 AM
  • User614805505 posted

    If this is the case, then I think nothing much can do at the server side coding. How about database indexing? Have you try to execute the same script at the SQL Management Studio? How long it take to return the result?

    Wednesday, November 28, 2012 6:04 AM
  • User-128423884 posted

    In fact i know that we cannot do anything more, but oracle people said us that if not are any way to load each 100 lines per read or something like that (because in oracle is possible) but in fact i think that this is not posible with .net.

    Wednesday, November 28, 2012 7:43 AM
  • User614805505 posted

    Maybe you can look into Parallel Execution of SQL Statement. I never try it but it sound interesting. 

    Wednesday, November 28, 2012 8:47 AM
  • User1786833724 posted
    MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);
    MySqlCommand MySQLSelectCommand = MySQLConnection.CreateCommand();
    MySQLSelectCommand.CommandText = @"select customerNumber, customerName, country from customers limit 81";
    MySqlDataReader MySQLReader;
    MySQLConnection.Open();
    MySQLReader = MySQLSelectCommand.ExecuteReader();
    DataTable Table = new DataTable();
    Table.Load(MySQLReader);
    MySQLConnection.Close();
    
    foreach (DataRow row in Table.Rows)
    {
    	Console.WriteLine(row["customerName"].ToString());
    }

    Monday, December 10, 2012 5:24 PM