More efficient way of getting this aggregate data?

  • Thread starter Thread starter sherifffruitfly
  • Start date Start date
S

sherifffruitfly

Hi all,

Our database contains daily data going back for years, which I need
aggregated into weekly averages. I came up with the code below to get
it, but I don't like it - mostly because hitting the the database with
a couple thousand queries feels wrong somehow.

Is there a way to somehow send a "bulk query" to the DB through
ado.net, and receive the response in a "bulk" package?

(No need to tell me about parametrizing the query.)


Thanks for any ideas,

cdj

================
private void GetWeeklyAverages(DateTime theLastMonday)
{
DateTime currMon = theLastMonday;
DateTime currFri = currMon.AddDays(4);

int i=0;
int numZeroAvgsInARow = 0;
while (i<2000 && numZeroAvgsInARow<10)
{
string strConn = "user id=ID;password=PASSWORD;data source=SOURCE";
OracleConnection dc_UAT = new OracleConnection(strConn);

string avg = "0";
try
{
dc_UAT.Open();

string oracleTable = "TABLE";
string oracleSelectString = "select round(avg(DATA),5) from " +
oracleTable + //variable
" where indexid='" +
"1" + //variable
"' and as_of_date between '" +
currMon.ToString("dd-MMM-yyyy") +
"' and '" +
currFri.ToString("dd-MMM-yyyy") +
"'";

OracleCommand cmd = new OracleCommand(oracleSelectString, dc_UAT);

avg = cmd.ExecuteScalar().ToString();
}
catch(Exception ex)
{
MessageBox.Show(i.ToString() + ": " + ex.Message, "Error getting
average");
}
finally
{
if (dc_UAT != null)
{
dc_UAT.Close();
}
}

//Print out info
txt_Output.Text += i.ToString() +
": " +
currMon.ToString("MM/dd/yy") +
" -" +
currFri.ToString("MM/dd/yy") +
"\t\t" +
avg +
Environment.NewLine;

//Next week back
currMon = currMon.AddDays(-7);
currFri = currMon.AddDays(4);

//Take care of loop vars
if (avg == "0")
{
numZeroAvgsInARow++;
}
else
{
numZeroAvgsInARow = 0;
}

i++;
}
 
I do not know oracle which may have some add-ins to SQL that would make this
quicker, however if you turned your command into a stored procedure with a
date range as inputs, you could reduce your overhead by making only one call
to the DB server. Then your results would come back "in bulk" and all the
work could be done on the DB level.

One "hit" to the DB is much better than 2000+.

You might have some better luck asking this on a SQL ng.

Rick
 
Back
Top