Datagrids vs multiple selects

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.

thanks!
LVL 1
cdfllcAsked:
Who is Participating?
 
MyersAConnect With a Mentor Commented:
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();
      mySqlConnection.Open();
      SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
      sqladapter.Fill(toReturn);
      mySqlConnection.Close();
      this.dataGrid1.DataSource= toReturn;
}


Hope this helps,
Vaughn
0
 
Bob LearnedConnect With a Mentor Commented:
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.

Bob
0
 
MyersACommented:
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,
Vaughn
0
 
cdfllcAuthor Commented:
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.
0
 
cdfllcAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.