This might be difficult for me to describe clearly but I will try anyway. Also note that this is entirely speculative; it might not even be able to run any faster (that is why I am asking).
This is a question directed at performance. I have been trying to improve the performance of a method and have managed to improve it quite a bit already (around 40% faster). Right now, the entire process takes about 1 minute and 15 seconds to complete. I would like to see if maybe you guys can see some obvious faults in my code and how we could perhaps improve it even further, seeing as I am not quite satisfied yet.
The method fetches a big amount of data from a couple of databases and needs to be displayed in a grid in a certain way. Here is the heavy part of the method. I will describe it in more detail after:
DataClasses1DataContext dbContext = new DataClasses1DataContext(sqlConnectionString);
//Find minimum value of N in the specified timespan
var headerMin = ((from y in dbContext.Testheaders
where Convert.ToDateTime(y.StartTime) >= GlobalVariables.StartTimeMSA
select y.N).Min());
//Find maximum value of N in the specified timespan
var headerMax = ((from y in dbContext.Testheaders
where Convert.ToDateTime(y.StartTime) <= GlobalVariables.EndTimeMSA
select y.N).Max());
//Find appropriate data in the specified timespan (min and max headerid)
var msaData = from x in dbContext.Testdatas
where x.HeaderID >= headerMin
&&
x.HeaderID <= headerMax
select new
{
StartTime = Convert.ToDateTime((from z in dbContext.Testheaders
where x.HeaderID == z.N
select z.StartTime).FirstOrDefault()),
DUT_id = Convert.ToString((from y in dbContext.Testheaders
where x.HeaderID == y.N
select y.DUT_id).FirstOrDefault()),
Meas = (double)x.Meas,
x.Step,
x.LogID,
x.HeaderID,
printID = Convert.ToInt32((from y in dbContext.Testheaders
where x.HeaderID == y.N
select y.PrintID).FirstOrDefault()),
Prod_Blok = Convert.ToInt32((from y in dbContext.Testheaders
where x.HeaderID == y.N
select y.Prod_Blok).FirstOrDefault()),
Rep_Count = Convert.ToInt32((from y in dbContext.Testheaders
where x.HeaderID == y.N
select y.Rep_Count).FirstOrDefault()),
Operator = Convert.ToString((from y in dbContext.Testheaders
where x.HeaderID == y.N
select y.Operator).FirstOrDefault()),
};
msaDataTable.Columns.Add("Time");
msaDataTable.Columns.Add("DUT");
msaDataTable.Columns.Add("Position");
var functionDistinct = msaData.Select(x => new { x.Step, x.LogID }).Distinct().ToList().OrderBy(x => x.Step).ThenBy(x => x.LogID);
//Add columns to datatable corresponding to each function. The number of columns is only known at runtime.
foreach (var item in functionDistinct)
{
var column = new DataColumn() { DataType = typeof(double), ColumnName = String.Format("{0}-{1}", item.Step, item.LogID), AllowDBNull = true };
msaDataTable.Columns.Add(column);
}
var distinctHeaderID = msaData.Select(x => x.HeaderID).Distinct();
foreach (var headerID in distinctHeaderID)
{
ct.ThrowIfCancellationRequested();
//Fetch the data for the current headerID
var headerIDData = msaData.Where(x => x.HeaderID == headerID).Select(x => new { x.StartTime, x.DUT_id, x.printID, x.Step, x.LogID, x.Meas }).ToList();
if (headerIDData != null)
{
DataRow row = msaDataTable.NewRow();
row["Time"] = headerIDData.FirstOrDefault().StartTime;
row["DUT"] = headerIDData.FirstOrDefault().DUT_id;
row["Position"] = headerIDData.FirstOrDefault().printID;
//Run through the data for the current headerID and assign the values to the right function.
foreach (var data in headerIDData)
{
string function = String.Format("{0}-{1}", data.Step, data.LogID);
row[function] = data.Meas;
}
msaDataTable.Rows.Add(row);
}
}
Some sample rows from the database query (msaData) could look like this (these are just made up):
I am basically taking those sample rows and combine them into a single row, where the distinct Step and LogID are combined into "functions" shown as columns with the Measurements as cell values. To illustrate, here is how the above picture would be displayed correctly:
A few notes:
Step and LogID combined are known as a "function". In the database they are separated into two columns instead of just the function. For example, Step could have a field value of 5 and LogID 1. Then the function would be known as 5-1. This is what I am displaying as columns instead.
To achieve the goal I am creating a DataTable and binding it to the grid after I am done filling it with appropriate data.
I need to create the columns dynamically as there is no way to know how many functions there are at design time. That is the reason I am using a DataTable in the first place instead of just creating a class and an
ObservableCollectionfor example.The above sample rows is a very small amount of what the actual data will consist of. We are talking 400000 rows in the test scenario I am running currently.
Are there any immediate problems in my code you can spot? Perhaps some places where a ToList() call could improve performance (even though I have tried some already). Or perhaps another way to tackle how I combine the rows?
