October 23, 2009 at 3:15 pm
I've been playing around with some C# .net and I was wondering something. The SqlClient.SqlDataReader is a forward only means of reading data from with SQL. Is the data provider doing some kind of kung fu in sql server to cache the data on the server and feed it to my test app a little bit at a time?
Query: SELECT * FROM big_table
The memory footprint for my test application remains relatively small while looping through the dataset returned by my reader, but the data set it is reading is around 14 million rows. I tried the same query using a class that caches the dataset in the memory of the machine that is running the app and gave up and killed the debugger when the process broke 1GB of used memory.
Does anyone know if/where the data is being cached? I ran profiler on my database while executing the test app and caught one "SQL: batch starting" and one "SQL: batch completed" for the query. I haven't really seen anything out of the ordinary, but the query does lock the table (sp_lock) while the query is executing. Using WITH(NOLOCK) eliminates the table lock (of course).
How is the SqlDataReader able to keep the memory footprint so small?
This might be a question for a different forum, but I thought I'd ask anyway.
Thanks!
October 26, 2009 at 4:50 am
Iam often asked by developers whether the ADO.NET DataReader or the DataSet is the better tool. Some developers say the DataReader is better because it is lightweight, while still others say they prefer the DataSet for its inherent flexibility. The truth is that both have their place in Microsoft® .NET development as their usefulness depends on the situation.Among the hurdles you may well encounter in migrating from classic ADO to ADO.NET is gaining a full understanding of how the operations that the ADO recordset performed are now handled in ADO.NET.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply