Datagrids vs multiple selects

Posted on 2006-05-30
Medium Priority
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
  • 2
  • 2
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 200 total points
ID: 16790717
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.


Expert Comment

ID: 16790869
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,

Author Comment

ID: 16791177
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.

Accepted Solution

MyersA earned 800 total points
ID: 16791613
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,

Author Comment

ID: 16792420
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month16 days, 12 hours left to enroll

864 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