Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

C# SQL Query considerations against a large database

Avatar of nbb007
nbb007 asked on
.NET ProgrammingMicrosoft DevelopmentEditors IDEs
6 Comments1 Solution2078 ViewsLast Modified:
I am querying a database table and performing a calculation in each row returned.  I'd like to put the result of this calculation right back in the same table, but I'm unsure of how to do this properly.  The table is very large - 100,000 - 1,000,000 rows - so I can't bring the table local, it must stay on the server.  Here is what I'm currently doing:

OleDbConnection accessConnect = new OleDbConnection()
{
   try
   {
      accessConnect.Open();//Open the data connection

      OleDbDataReader tableReader = (new OleDbCommand(sqlQuery, accessConnect)).ExecuteReader();

     while (tableReader.Read())//true if there are more rows; otherwise, false
     {
        alpha = Convert.ToDouble(tableReader.GetValue(0));
        beta = Convert.ToDouble(tableReader.GetValue(1));

         distribution = CalcUtility.BetaCumulativeDistribution(alpha, beta);
      }
.............

I need to use the "distribution" value in another query against the same database, so I'm thinking the best way to do this would be to add a "DISTRUBUTION" column to the table I'm querying and adding the "distribution" value.  Would I add this data as soon as it is calculated, i.e.

SqlConnection custConn = new SqlConnection(...);
custConn.Open();
SqlCommand sqlCmd = new SqlCommand();
SqlUpdateCommand1.Connection = custConn;
SqlUpdateCommand1.CommandText="ALTER TABLE table ADD COLUMN DISTRIBUTION Double;
SqlUpdateCommand1.ExecuteNonQuery();

using (OleDbConnection accessConnect = new OleDbConnection())
{
   try
   {
      accessConnect.Open();//Open the data connection

      OleDbDataReader tableReader = (new OleDbCommand(sqlQuery, accessConnect)).ExecuteReader();

     while (tableReader.Read())//true if there are more rows; otherwise, false
     {
        alpha = Convert.ToDouble(tableReader.GetValue(0));
        beta = Convert.ToDouble(tableReader.GetValue(1));

         distribution = CalcUtility.BetaCumulativeDistribution(alpha, beta);

         SqlUpdateCommand1.CommandText="UPDATE table SET DISTRIBUTION =" + distribution.ToString() + "' WHERE stuff;"
         SqlUpdateCommand1.ExecuteNonQuery();
      }
................

These seems pretty clunky, but I think it would work.  I think the best method would be to create a dataset based on the query, calculate, add, and fill the column, and then update the server database with the dataset.  The problem is that there is too much data, so filling a local dataset isn't an option.  Is there a way I could add the "distribution" column in bulk, once all distrubution values are calculated?

Thanks in advance for the help!!