[Stackoverflow] [Progress OpenEdge ABL] C# ODBC: Improving performance for thousands of select queries

Status
Not open for further replies.
G

GimpFlamingo

Guest
The database I am using is Progress OpenEdge. In my program I am taking thousands of serial numbers and querying the database for the information relating to them.

Essentially this code bellow takes ~6000 serial numbers and creates a select query for each serial number and returns a list containing the strings.

/// <summary>
/// Builds a list of queries for each serial number in param<c>sn</c>
/// </summary>
/// <param name="sn">A list of valid serial numbers</param>
/// <returns>A list of strings where each string is a sql query for a serial number's information</returns>
private static List<string> BuildQuery(List<string> sn)
{
return sn.Select(s => "SELECT a.\"Date-Created\", a.\"date-produced\", a.employee, " +
"a.\"Item-Code\", a.\"job-number\", a.\"station-produced\", " +
"a.\"shift-produced\", a.\"Serial-Number\", a.\"parent-serial\", " +
"a.\"gross-wgt\", a.\"qty-base\", a.\"resin-wgt\", " +
"a.\"tare-wgt\", a.\"Integer-1\", a.\"unit-number\" FROM " +
$"CUSTOM10.PUB.\"imsngsnt\" a WHERE a.\"Serial-Number\" = '{s}'").ToList();
}


And then I loop through the list calling this function ~6000 times.

/// <summary>
/// Makes a query to the given connection <c>conn</c> and query <c>sql</c> and returns the information for the serial number <c>sn</c>
/// </summary>
/// <param name="conn">Connection to SNT database</param>
/// <param name="sql">SQL query</param>
/// <param name="sn">The serial number the query pertains to</param>
/// <returns><c>SerialInformation</c> containing the information for Serial number <c>sn</c></returns>
private static SerialInformation MakeQuery(OdbcConnection conn, string sql, string sn)
{
var set = new DataSet();
// Perform all queries
var adapter = new OdbcDataAdapter(sql, conn);

adapter.Fill(set);
if (set.Tables[0].Rows.Count == 0) return null;
var info = ConvertDataSet(set);
// Add starting serial number to info
info.StartingSerial = sn;
return info;
}


I know this is horribly inefficient and I would like to know how I can make these queries faster using .NET System.Data.ODBC.

Continue reading...
 
Status
Not open for further replies.
Top