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?
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.
No matter how much time you spend on coding. Wish you happy coding.
My Technical Blog
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
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?
No matter how much time you spend on coding. Wish you happy coding.
My Technical Blog
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.
miguelaup
Member
18 Points
9 Posts
Read form ExecuteReader
Nov 27, 2012 05:07 PM|LINK
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
anil.india
Contributor
2613 Points
453 Posts
Re: Read form ExecuteReader
Nov 27, 2012 05:48 PM|LINK
Why do you read one by one instead you can assign it directly to your data control.
codepattern.net/blog ||@AnilAwadh
miguelaup
Member
18 Points
9 Posts
Re: Read form ExecuteReader
Nov 27, 2012 10:42 PM|LINK
I need to modify this data and adding more info from othes sources.
CruzerB
Contributor
5399 Points
1098 Posts
Re: Read form ExecuteReader
Nov 28, 2012 02:14 AM|LINK
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.
My Technical Blog
miguelaup
Member
18 Points
9 Posts
Re: Read form ExecuteReader
Nov 28, 2012 06:55 AM|LINK
using (var reader = objCommand.Command.ExecuteReader())
{
while (reader.Read())
{ ... }
}
____________________________
using (var reader = objCommand.Command.ExecuteReader())
{
var dt = new DataTable("");
dt.Load(reader);
}
CruzerB
Contributor
5399 Points
1098 Posts
Re: Read form ExecuteReader
Nov 28, 2012 07:09 AM|LINK
Do you mind to share the code in the while block? Maybe the data casting (Boxing or Unboxing) make the processing slow.
My Technical Blog
miguelaup
Member
18 Points
9 Posts
Re: Read form ExecuteReader
Nov 28, 2012 08:42 AM|LINK
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
CruzerB
Contributor
5399 Points
1098 Posts
Re: Read form ExecuteReader
Nov 28, 2012 10:04 AM|LINK
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?
My Technical Blog
miguelaup
Member
18 Points
9 Posts
Re: Read form ExecuteReader
Nov 28, 2012 11:43 AM|LINK
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.
CruzerB
Contributor
5399 Points
1098 Posts
Re: Read form ExecuteReader
Nov 28, 2012 12:47 PM|LINK
Maybe you can look into Parallel Execution of SQL Statement. I never try it but it sound interesting.
My Technical Blog