I have N rows (which could be nothing less than 1000) on an excel spreadsheet. And in this sheet our project has 150 columns like this:
Now, our application needs data to be copied (using normal Ctrl+C) and pasted (using Ctrl+V) from the excel file sheet on our GUI sheet. Copy pasting 1000 records takes around 5-6 seconds which is okay for our requirement, but the problem is when we need to make sure the data entered is valid. So we have to validate data in each row generate appropriate error messages and format the data as per requirement. So we need to at runtime parse and evaluate data in each row.
Now all the formatting of data and validations come from the back-end database and we have it in a data-table (dtValidateAndFormatConditions). The conditions would be around 50. So you can see how slow this whole process becomes since N X 150 X 50 operations are required to complete this whole process.
Initially it took approximately 2-3 minutes but now i have reduced it to 20 - 30 seconds. However i have increased the speed by making an expression parser of my own - and not by any algorithm, is there any other way i can improve performance, by using Divide and Conquer or some other mechanism. Currently i am not really sure how to go about this. Here is what part of my code looks like:
public virtual void ValidateAndFormatOnCopyPaste(DataTable DtCopied, int CurRow)
{
foreach (DataRow dRow in dtValidateAndFormatConditions.Rows)
{
string Condition = dRow["Condition"];
string FormatValue = Value = dRow["Value"];
GetValidatedFormattedData(DtCopied,ref Condition, ref FormatValue ,iRowIndex);
Condition = Parse(Condition);
dRow["Condition"] = Condition;
FormatValue = Parse(FormatValue );
dRow["Value"] = FormatValue;
}
}
The above code gets called row-wise like this:
public override void ValidateAndFormat(DataTable dtChangedRecords, CellRange cr)
{
int iRowStart = cr.Row, iRowEnd = cr.Row + cr.RowCount;
for (int iRow = iRowStart; iRow < iRowEnd; iRow++)
{
ValidateAndFormatOnCopyPaste(dtChangedRecords,iRow);
}
}
Please know my question needs a more algorithmic solution than code optimization, however any answers containing code related optimizations will be appreciated as well. (Tagged Linq because although not seen i have been using linq in some parts of my code).
