Datagrids vs multiple selects

Posted on 2006-05-30
Last Modified: 2010-04-16
hello, I have a situation where I am trying to build some sort of reporting functionality.

I have a table of accounts, lets say there are 1000 accounts.
I have another table of payments, lets say there are 500,000 payments (multiple payments per account)

I have to obtain the payment information for each account, and manipulate it in some way.

I imagine that is why they created DataTables - you just do a "big" select from the database, and keep that table in memory, and then do selects against that in-memory table, right?

Can anyone show me an example of how to do that?

So basically, in this example, I just want to get back the matching payment records for an account. I have an account id in the payment table.

Question by:cdfllc
    LVL 96

    Assisted Solution

    by:Bob Learned
    While that sounds like a possibility, the DataTable.Select method is pretty primitive.  It is more of a filter expression that an SQL select statement.

    LVL 2

    Expert Comment

    Hi  cdfllc,

    With VS 2002 1.0 I tried to do something similar to that (store over 500,000 records into a datatable) and the performance for that was horrible. All those records are stored in memory, and in my case, it took up so much of my physical memory, that it got to the point that I couldn't switch between applications.  And if you're going to bind that datatable/dataset to a datagrid, then you'll see an even higher performance reduction (I had 1GB ram).

    What I ended up doing was implementing paging.  I'd display the first X records (50-100 of them) and I'd have a 'Previous' and 'Next' button' that would let me move through all the records. I was doing this with Access so it was a pain in the ###, but now SQL Server 2005 has a new feature that lets you easily implement the paging functionality.
    Unless it's been improved with VS .Net 2003 or VS2005, I wouldn't store that much data in a datatable.

    Hope this helps,
    LVL 1

    Author Comment

    Well, we might now even mind having a "dedicated" machine to handle this report.
    I am just concerned that I am going to slow down everyone else using another application that hits this same database.
    I don't want to issue 1000 select statements, when I can just get everything back in a single one - and then work with that result set.
    I mean, if it takes this dedicated machine "5 minutes" to perform the math on the results, we don't really care - as long as it's not slowing down the database or the other application users.

    As far as you know, there are not any limits to the amount of data stored in a DataTable - are there?
    I guess we could possibly bump up the RAM in this dedicated machine if that would help.
    LVL 2

    Accepted Solution

    I can't give you a definite answer as to how much data a datatable can hold, but I can tell you that this same client recently called me because the application threw an out-of-memory exception. I would assume that once you try to store data in memory that doesn't exist, some type of memory exception will come out (i.e. I don't think the datatable has a limit on the amount of data it can hold)
    But this is in VS2002. I'm not sure if they've resolved the issue with the later versions.
    Anyways, This is what you can do if you want to fill a datagrid with SQL server data. You can also modify the query below so it brings everything in your table (the 500,000+ records). That way you can see what the performance hit is:

    private void buttonFill_Click(object sender, System.EventArgs e)
          SqlConnection mySqlConnection = new SqlConnection("data source=mysource;initial catalog=cat;UID=uid;PWD=pass");
          SqlCommand cmdToExecute = new SqlCommand();
          cmdToExecute.Connection = mySqlConnection;
          cmdToExecute.CommandText = "select acctField1, acctField2, acctField3 from account_table where id = '" + this.txtAccount.Text + "'";
          SqlDataAdapter sqladapter = new SqlDataAdapter(cmdToExecute);
          cmdToExecute.CommandType = CommandType.Text;
          DataTable oReturn = new DataTable();
          SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
          this.dataGrid1.DataSource= toReturn;

    Hope this helps,
    LVL 1

    Author Comment

    This is the answer I was looking for - I realized I was searching for the wrong thing

    DataRow[] oRows;
    DataTable oTable = new DataTable();
    oTable = PaymentDataSet.Tables[0]; // this is the 500,000 record table - I only want to loop through each payment for a specific account
    oRows = oTable.Select("ACCOUNT_ID = " + propertyId,"ACCOUNT_ID ASC");

    foreach(DataRow oRow in oRows)
         cashBalanceStr      = oRow["ACCOUNT_BALANCE"].ToString().Trim();                        
         cashBalanceDbl      = System.Convert.ToDouble(cashBalanceStr);
         // do the math...

    But you guys gave me some good information, so I'll split the points!

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now