asked on

OLE DB performance issue

Its taking 4 minutes to insert 2500 records in the database using OLE DB connection. Can you help me improve the performance? Please refer to the c# sample code below.

foreach (String s in names)
{
InsertRow(strSqlInsert);
}

public static void InsertRow(string insertSQL)
{
using (OleDbConnection connection = new OleDbConnection($"Provider=IBMDASQL;Data Source={dbSource};Application Name=WCSWeb;Persist Security Info=True;Library List={dbLibList};Naming Convention=1;User Id={dbUser};Password={dbPassword};Block Fetch=True;Block Size=2000;"))
{
// The insertSQL string contains a SQL statement that
// inserts a new row in the source table.
OleDbCommand command = new OleDbCommand(insertSQL, connection);

// Set the Connection to the new OleDbConnection.
//command.Connection = connection;

// Open the connection and execute the insert command.
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// The connection is automatically closed when the
// code exits the using block.
}
}

ASKER CERTIFIED SOLUTION

AndyAinscow

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave Baldwin

You are opening and closing the database connection for each row. Put the connection opening at the top of the code and the close at the very end after the loop so you only do them once. I'm not a C# programmer so I can't give you the exact code but that's what I do in other languages.

Meinhoonaa

ASKER

I modified the code based n your suggestion but it didn't change anything

using (OleDbConnection connection = new OleDbConnection($"Provider=IBMDASQL;Data Source={dbSource};Application Name=WCSWeb;Persist Security Info=True;Library List={dbLibList};Naming Convention=1;User Id={dbUser};Password={dbPassword};Block Fetch=True;Block Size=2000;"))
{
connection.Open();
try
{
foreach (String s in names)
{
if (keys.Contains(s))
{
using (RegistryKey key2 = rkey.OpenSubKey(s))
processValueNames(key2, s, registryDataTable, connection);
}
}
}
catch (Exception e)
{

}
}

AndyAinscow

Strange, that should have brought an improvement.
What is in this function processValueNames ?

Meinhoonaa

ASKER

public static void processValueNames(RegistryKey Key, string s, DataTable dt, OleDbConnection connection, bool isChildNode=false, string parentKey="", int count=0, string sParentKey="")
{
string[] valuenames = Key.GetValueNames();
string[] valuenames1 = Key.GetSubKeyNames();

string strSqlInsert;
string sKey; string sSubKey;

foreach (string valuename in valuenames)
{
object obj = Key.GetValue(valuename);
if (obj != null)
Console.WriteLine(Key.Name + " " + valuename + " " + obj.ToString());
DataRow registryDataRow = dt.NewRow();
if (isChildNode)
{
registryDataRow["KEY_NAME"] = parentKey;
registryDataRow["SUB_KEY_NAME"] = s;
sKey = parentKey;
sSubKey = s;
}
else
{
registryDataRow["KEY_NAME"] = s;
registryDataRow["SUB_KEY_NAME"] = "PARAMETERS";
sKey = s;
sSubKey = "PARAMETERS";
}
registryDataRow["SETTING_NAME"] = valuename;
registryDataRow["VALUE"] = obj.ToString();
dt.Rows.Add(registryDataRow);
strSqlInsert = "INSERT INTO wcscodtb(KEY_NAME, SUB_KEY_NAME, OCCURENCES, SETTING_NAME, VALUE) VALUES('" + sKey + "','" + sSubKey + "'," + count + ", '" + valuename + "', '" + obj.ToString() + "')";
InsertRow(strSqlInsert, connection);
}

count = 1;
foreach (String s1 in valuenames1)
{
using (RegistryKey key2 = Key.OpenSubKey(s1))
processValueNames(key2, s1, dt, connection, true, s, count++);
}

/////////////
}

AndyAinscow

Hmmm.
So it still takes around 4 minutes for the 2500 records does it.

Meinhoonaa

ASKER

Yes sir

Ryan Chong

>> foreach (String s in names)
{
InsertRow(strSqlInsert);
}

why do you need to have a for each loop to call the insertrow method?

AndyAinscow

You need to use a profiler to check where the time is being taken. This with the connection was a very obvious, but apparently not in your case, slow point. Now all one can do is guess until you test with a tool for timing the code execution.

Meinhoonaa

ASKER

@Andy, is it possible to use string builder as a work around? If so, can you send me a sample for the OeDbCommand? Thanks!

AndyAinscow

Is that the bottleneck you have found from profiling?

If you are worried about stringX = "hhhh" + "jjkk" + "uzuuz" giving performance issues you can use the String.Format like
s = String.Format("hello {0} world {1}", "x", "y") which gives s = hello x world y

Meinhoonaa

ASKER

thank you