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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>> 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.