Performance issue (Data selection)

[EDN Admin]

Hi, I am currenly working on project where i need to build a log analysis tool. For that as my first assignment i need to convert 12-25 MB text file to another txt file (with some additional data).In between reading and writing to another text file i need to do processing on log record.Each log file contains 40k to 70k records. And each record has particular message id based on which I have to select the parameters for that message and their details.I am using Microsoft mdb file which has 7 tables in that. I am only accessing 4 tables for each record in log file i have pasted my code here to extract data from mdb file.As of now this processing takes 30 minutes or more.
I know that 3 db calls per record takes a time.I am thinking these steps to increase performance.
1.Create a thread per each record and do the processing(I am new to threading so help me out converting this code to thread one)
2. Optimize the database access. I have done as much tuning as per knowledge.Please do look at queries and comment and correct me.
Please do look into this code(Ignore the missing braces). in your leisure time and help me increase the performance.Its critical for me reduce the processing time. suggestions are welcome. namespace WindowsFormsApplication2
{
class Decoder
{
private static OleDbConnection GetConnection()
{
OleDbConnection conn = new OleDbConnection();
try
{
string connectionString = @"Provider=Microsoft.JET.OlEDB.4.0;"
+ @"Data Source=C:Usersrvk463DesktopGiriApiDatabaseApiDatabase.mdb";
conn = new OleDbConnection(connectionString);
conn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return conn;
}

private static int ProcessHex(string str)
{
var strOut = "";
for (var i = 0; i < str.Length; i += 5)
{
strOut += str.Substring(i + 2, 2);
strOut += str.Substring(i, 2);
}

return Int32.Parse(strOut, System.Globalization.NumberStyles.HexNumber);
}
private static string Process(string str)
{
string strOut = "";
for (var i = 0; i < str.Length; i += 5)
{
strOut += str.Substring(i + 2, 2);
strOut += str.Substring(i, 2);
}

return strOut;
}

public static string Decoders(string opCode, string payLoad, bool isZCMsg)
{
String szTempMsg = "";
String szSQL;
int szMsgId, wApiId, wGroupId, wParamId, wNoOfBytes, bTryToDecode = 1, bDecodeFailed = 1, wParamTypeId = 0;
bool bIsHidden, bDisplayHex;
string szParamName, szTypeName, szParamValueDec, szParamValueHex, szAlias = "", szApiName, wTypeId;
ushort wAliasLength = 0, wStationControlCmdCount = 0, byValue, wValue;
uint dwValue;
String[] field = payLoad.Split(.);
string fields = string.Join(String.Empty, field); // Null arguments are bad

try
{
if (true == isZCMsg)
{
szMsgId = Int16.Parse(opCode, System.Globalization.NumberStyles.HexNumber);
szSQL = "SELECT ApiId, ApiGroupId, ApiName FROM Api WHERE ApiDefineValue = " + szMsgId.ToString() + " AND ApiGroupId = 10";
}
else
{
szMsgId = ProcessHex(opCode);
szSQL = "SELECT ApiId, ApiGroupId, ApiName FROM Api WHERE ApiDefineValue = " + szMsgId.ToString() + " AND ( (ApiGroupId <> 10) and (ApiGroupId <> 22) )";
}
OleDbConnection conn = GetConnection();
OleDbCommand command = new OleDbCommand(szSQL, conn);
OleDbDataReader reader;
reader = command.ExecuteReader();

if (reader.Read())
{
wApiId = reader.GetInt32(0);
wGroupId = reader.GetInt32(1);
szApiName = reader.GetString(2);
reader.Close();
szTempMsg = szTempMsg + szApiName;

szSQL = "SELECT Parameter.ParameterId, NoOfBytes, ParameterName, TypeName, Parameter.TypeId, DisplayHex, IsHidden " +
"FROM ApiParameter INNER JOIN (Parameter LEFT JOIN Type ON Parameter.TypeId = Type.TypeId) " +
"ON ApiParameter.ParameterId = Parameter.ParameterId " + "WHERE ApiParameter.ApiId = " + wApiId + " ORDER BY ApiParameter.Ordinal";


OleDbCommand command1 = new OleDbCommand(szSQL, conn);
OleDbDataReader reader1;
reader1 = command1.ExecuteReader();

int i = 0;
while (reader1.HasRows)
{
if (reader1.Read())
{
wParamId = reader1.GetInt32(0);
wNoOfBytes = reader1.GetInt32(1);
szParamName = reader1.GetString(2);
szTypeName = reader1["TypeName"].ToString();
wTypeId = reader1["TypeId"].ToString();
bDisplayHex = reader1.GetBoolean(5);
bIsHidden = reader1.GetBoolean(6);

bTryToDecode = 1;
szParamValueDec = "";
szParamValueHex = "";
if (wTypeId == "")
{
wParamTypeId = 0;
}
else
{
wParamTypeId = Convert.ToInt32(wTypeId);
}
if (true == isZCMsg)
{
string szByteValue;
if (wParamTypeId == 1)
{
for (int j = 1; j <= (int)wNoOfBytes; j++)
{
szByteValue = (uint.Parse(fields.Substring(i, 2), System.Globalization.NumberStyles.HexNumber)).ToString("D2");
szParamValueDec = szParamValueDec + szByteValue;
szByteValue = uint.Parse(fields.Substring(i, 2), System.Globalization.NumberStyles.HexNumber).ToString("X2");
szParamValueHex = szParamValueHex + szByteValue;
i = i + 2;
}
}
}
if (bTryToDecode != 0)
{
szSQL = "";
if (szParamValueDec != "")
{
szSQL = "SELECT ValueName FROM [Value], ParameterValue "
+ "WHERE ParameterValue.ParameterId = " + wParamId + " AND ParameterValue.ValueId = [Value].ValueId "
+ "AND [Value].[Value] = " + szParamValueDec;
OleDbCommand command2 = new OleDbCommand(szSQL, conn);
OleDbDataReader reader2;
reader2 = command2.ExecuteReader();
if (reader2.Read())
{
szTempMsg = szTempMsg + reader2["ValueName"];
bDecodeFailed = 0;
}
else
{
bDecodeFailed = 1;
}
reader2.Close();
}
}
conn.Close();
return szTempMsg;
}
return szTempMsg;
}

private static string sample(string Message, string MessageId, string Payload)
{
if (messageName[1] == "MumdTrunkingCallControlRequest" ||
messageName[1] == "MumdConventionalCallControlRequest" ||
messageName[1] == "MumdTrunkingCallControlStatus" ||
messageName[1] == "MumdConventionalCallControlStatus")
{
if (messageName[1] == "MumdTrunkingCallControlRequest" ||
messageName[1] == "MumdConventionalCallControlRequest")
{
string[] NewPayload = Payload.Split);
string sOpCode = NewPayload[1].Substring(60, 4);
string sPayload = NewPayload[1].Substring(65, NewPayload[1].Length - 65);
string newPayload = "";
newPayload = Decoder.Decoders(sOpCode, sPayload, true);
return newPayload;
}
else
{
string[] NewPayload = Payload.Split);
string sOpCode = NewPayload[1].Substring(65, 4);
int temp = NewPayload[1].Length - 70;
string sPayload = NewPayload[1].Substring(70, temp);
string newPayload = "";
newPayload = Decoder.Decoders(sOpCode, sPayload, true);
return newPayload;
}
}
else
{
if (Message != "MlogWrite")
{
string[] sOpCode = MessageId.Split);
string[] sPayload = Payload.Split);
string newPayload = "";
newPayload = Decoder.Decoders(sOpCode[1], sPayload[1], false);
return newPayload;
}
}
}
return messageName[1];
}
}
private void button2_Click(object sender, EventArgs e)
{
if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
label2.Text = saveFileDialog1.FileName;
string[] value;
string file = openFileDialog1.FileName;
string[] readText = File.ReadAllLines(file);
string[] writetext = (string[]) readText.Clone();

for(int i=0;i< readText.Length;i++)
{
if ((!readText.Contains("#")) && (!(readText.Length ==0 )))
{
string[] fields = readText.Split(t); //<-- change field separator here & split fields
for (int j = 0; j < fields.Length; j++)
{
value = fields[j].Split(new char[] { : }, 2);
if (value[0].ToString() == "Message")
{
if (value[1].ToString().Trim() != "MlogWrite")
{
string Decoded = sample(fields[7].Trim(), fields[8].Trim(), fields[9].Trim());
string temp = Decoded.Trim(new char[] { n });
writetext = writetext + "tDecoded Payload(" + Decoded + ")";
}
}
}
}
}
File.WriteAllLines(@label2.Text, writetext);
}
}
}

ITs very urgent any hel ll be great,
Thanks Girish
Girish

View the full article