none
returning data rows in something other then datatable RRS feed

  • Question

  • Hi all,

    working on C# vs 2010, win 7. In a windows forms app I'm calling a stored procedure from a sql server 2008 r2 express, this SP returns me around 621K rows in a DataTable with just one column, full of numerical values. This datatable takes around 150MB or so, this is an estimated size of course, and this estimation has been inferred by looking at the windows Task Manager, cuz as soon as the sql data adapter class calls the Fill method to fill the datatable, the task manager goes up around 130 or 150 or so mega bytes. Now when I get the SP results in a datatable, to free the memory instantly I create an array of type double [] and copy all values from the datatable and call Clear() on datatable and set it to null. The double [] array then is passed to routines which does numerical analysis on these values.

    What i'm looking for is to save this ~150 MB of ram taken by the DataTable due to the result set returned from the SP. Is there an alternate that would, instead of populating a DataTable class, populate an array [] of values for me? If such a way exists, it would not only save me ram but probably also the processing time that is required to construct a datatable of those 621K values by Microsoft's internal ado.net classes (I assume).

    Thanks in advance.

    ..ab


    Ab

    Monday, May 19, 2014 10:08 AM

Answers

  • Hi ab -- Try using a List<double> (instead of an array) and use SqlDataReader instead of the SqlDataAdapter.Fill(). Something like this:

    List<double> MyList = new List<double>();
    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            //The 0 stands for "the 0'th column", so the first column of the result
            MyList.Add(rdr.GetDouble(0));
        }
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by developer1 Tuesday, May 20, 2014 10:42 AM
    Tuesday, May 20, 2014 5:08 AM

All replies

  • Hi ab -- Try using a List<double> (instead of an array) and use SqlDataReader instead of the SqlDataAdapter.Fill(). Something like this:

    List<double> MyList = new List<double>();
    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            //The 0 stands for "the 0'th column", so the first column of the result
            MyList.Add(rdr.GetDouble(0));
        }
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by developer1 Tuesday, May 20, 2014 10:42 AM
    Tuesday, May 20, 2014 5:08 AM
  • thank you Bonnie the Geek Goddess ....

    *bows*

    that really helped! .. wish I could buy you a pizza with extra cheese and mushrooms and all


    Ab

    Tuesday, May 20, 2014 10:41 AM
  • Oooh, pizza! Sounds yummy! It would have to have sausage, onions and black olives too!  I'm glad I could help!!  =0)

    I used to know a guy who signed his name "ab" ... but his first name was Allen (or Alan, I don't remember) and his last name started with a "B". You're not him are you?  ;0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, May 21, 2014 4:52 AM
  • :) .. no I'm not him. My initials "ab" are cuz my class fellows (way back) found it very easy to just call me "a.b" then to pronounce my name -_- . so I tell everyone its ok to just call me "ab" cuz i'm used to that.

    btw before this I never knew the datatables are such memory eaters! usually cuz we always use small sets of data in them and never notice in these times of GBs of ram in every computer.


    Ab

    Wednesday, May 21, 2014 10:26 AM
  • I didn't really think you were the same "ab", but I had to ask. ;0)

    I always use DataSets and DataAdapter.Fill(), but you're right that they can use a bit of memory if you're talking about huge amounts of data. But in normal use in all the applications I've ever worked on, we've never had more than a few thousand rows of data. So, I've gotta wonder why you're reading more than a half million rows of data into your application? Wow, that's a lot! What kind of process are you doing on all that data? Just curious ....


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, May 21, 2014 3:31 PM
  • I'm consulting for a company, on a 3 months contract here, the app I'm working on displays a lot of charts based on the data stored in the database. The calculations which I was doing just now was calculating mean/variance/standard-deviation and few other things. The data is selected on the ui, its the nature of the combinations of the selection of the stuff on parameter screen which determines how much data the calculations will be performed on and ultimately rendered in the charts (parametric histograms, and many more types to come). the worst cases so far involves me having to play with 1.3 million rows. 1.3 million was beginning to hit ram limits for me and that's cuz this app is compiled as a 32 bit exe, because of a dependency of a webbrowser control that we are using in our application to show charts, its called awesomium (http://www.awesomium.com/), (funny name) based on chrome browser source code. Once this awesomium is available in 64 bits we'll have more ram to play in, but even then the single datatable with a million values in it is just not feasible. then entered the datareader, thanks to your suggestion. You know when I joined this team, they were doing these calculations inside a database stored procedure and it was SO slow! upon a lot of investigation on all main areas of this application, apart from many other places, one suggestion that I put forward was that I wanted to write this same SP stuff in c# and see how the performance turns out to be. so all the a class frequency calculations from this data, plus the mean/variance/stddev, is so fast now, its awesome. All I do is get the data in the ram in an array of type double, issue a couple of for loops and its done within a matter of 3 or 4 seconds. Before datareader, since the datatable was taking a lot of ram, we could only issue just one or 2 calculations in parallel, but since now the datareader has saved us huge amounts of ram, we can issue a lot of calculations at the same time, basically as many as Environment.ProcessorCount; or even more.

    the answer to your question cud be short I know but I like to type (and talk) a lot hehe!

    ..ab

     


    Ab

    Thursday, May 22, 2014 6:13 AM
  • I'm surprised that the calculations done in SQL Server SPs were slow. That server should be where the actual power is, perhaps they aren't using an optimally configured server.

    But no matter, you've got it whizzing right along now, so that's awesome! =0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, May 23, 2014 4:15 PM