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.
            }
        }
MeinhoonaaAsked:
Who is Participating?
 
AndyAinscowFreelance programmer / ConsultantCommented:
Not too surprising - you open a new connection for each record.

What you could do is the following logic.

open connection
for each string in names
  insert the record
end of foreach loop
close connection

You should be able to rearrange your current code without too much effort to do that
0
 
Dave BaldwinFixer of ProblemsCommented:
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.
0
 
MeinhoonaaAuthor Commented:
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)
                {

                }
            }
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
AndyAinscowFreelance programmer / ConsultantCommented:
Strange, that should have brought an improvement.
What is in this function processValueNames ?
0
 
MeinhoonaaAuthor Commented:
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++);
                }
           
            /////////////
        }
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
Hmmm.
So it still takes around 4 minutes for the 2500 records does it.
0
 
MeinhoonaaAuthor Commented:
Yes sir
0
 
Ryan ChongCommented:
>> foreach (String s in names)
{                    
        InsertRow(strSqlInsert);
}   

why do you need to have a for each loop to call the insertrow method?
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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.
0
 
MeinhoonaaAuthor Commented:
@Andy, is it possible to use string builder as a work around? If so, can you send me a sample for the OeDbCommand? Thanks!
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
0
 
MeinhoonaaAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.