Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Help Required for Best way to use Insert and Update statement in a for loop of C# with best performance

My requirement is to create a job which connects a MS-SQL server of an application and retrieves a table domain id from table Employee and connect LDAP and compare the domain . Only if different then first insert in table Domain and then Update in table Employee.

I used below code for it.....But each time I am opening a connection only when the domain are different and performing Insert and Update operation. This is very slow and it takes lot of time to update more than 2000 records...

//Here I am using Code for SqlConnection,  SqlDataAdapter adap and filling to Datatable dt    
 and closing the conn.

// Here the FOR loop of datatable starts....
 for (int i = 0; i < dt.Rows.Count; i++)
    {
        retreived old domain from dt table.
        Here I have written the code Connect to LDAP and run the LDAP query to retreive the current user from dt.
            Here i am setting the new domain from LDAP value.

          //     closing the LDAP connection
          below the is if statement within this FOR loop
   if (newDomain != oldDomain)
                    {
                        conn.Open();

                        //Add old domain and new domain in the new table with the date
                        SqlCommand cmdInsert = new SqlCommand("INSERT INTO DOMAIN (PROJECT_POOL_ID, OLD_DOMAIN, NEW_DOMAIN, UPDATED_TIME) VALUES (" + dt.Rows(i)["PROJECT_POOL_ID"].ToString().Trim() + ",'" + oldDomain + "','" + newDomain + "',GETDATE())", conn);                        
                        //update the record in employee table with the new domain
                        SqlCommand cmdUpdate = new SqlCommand("UPDATE employee SET DOMAIN = '" + newDomain + "' WHERE LOGIN_ID = '" + dt.Rows(i)["LOGIN_ID"].ToString().Trim() + "' AND PROJECT_POOL_ID = " + dt.Rows(i)["PROJECT_POOL_ID"].ToString().Trim(), conn);

                        int k = (int)cmdInsert.ExecuteNonQuery();
                        int j = (int)cmdUpdate.ExecuteNonQuery();
                         
                        conn.Close();
                       
                    }
  Note changed to (i) since problem with posting in square bracket
 
The above code runs perfectly but it takes lot of time to perform the update and insert operation. Could someone help me know the best way to perform Update and insert at much faster rate?
0
anbu210
Asked:
anbu210
1 Solution
 
madginoCommented:
There is no much space for improvement here.
You have to test which operation is taking the most time (LDAP query or db update/insert) but I'm almost sure that the time consuming operation is querying the LDAP and comparing to this, the insert/update time are not neglijable.

If this is true check if you can to interrogate LDAP for all the users in a domain(s) to obtain a full list: LOGIN_ID, PROJECT_POOL_ID, DOMAIN. Then parse this list and do update/insert for each employee.

Or create a temporary table from this list and do a single employee update using inner join between employee and temporary table.

Also check in LDAP if you have a LastModifiedDate to extra filter the employees list by this value when you're querying the LDAP.
0
 
Miguel OzSoftware EngineerCommented:
Quick improvements that  you can do:
1) Do  not open/close the SQL connection in the loop, make it outside the loop
2) Run both insert and update command as one SQL statement as follows:
 conn.Open();
for (int i = 0; i < dt.Rows.Count; i++)
    { 
        retreived old domain from dt table.
        Here I have written the code Connect to LDAP and run the LDAP query to retreive the current user from dt.
            Here i am setting the new domain from LDAP value.

          //     closing the LDAP connection 
          below the is if statement within this FOR loop
   if (newDomain != oldDomain)
                    {
                        SqlCommand mySqlCommand = conn.CreateCommand();
//Add old domain and new domain in the new table with the date
                        string cmdInsert = new SqlCommand("INSERT INTO DOMAIN (PROJECT_POOL_ID, OLD_DOMAIN, NEW_DOMAIN, UPDATED_TIME) VALUES (" + dt.Rows(i)["PROJECT_POOL_ID"].ToString().Trim() + ",'" + oldDomain + "','" + newDomain + "',GETDATE())", conn);                        
                        //update the record in employee table with the new domain
                        string cmdUpdate = new SqlCommand("UPDATE employee SET DOMAIN = '" + newDomain + "' WHERE LOGIN_ID = '" + dt.Rows(i)["LOGIN_ID"].ToString().Trim() + "' AND PROJECT_POOL_ID = " + dt.Rows(i)["PROJECT_POOL_ID"].ToString().Trim(), conn);
                        mySqlCommand.CommandText = cmdInsert  + cmdUpdate;
                        int k = (int)mySqlCommand.ExecuteNonQuery();
                      
                    }
//end of loop
conn.Close();

Open in new window


Thus you use one round trip not two round trips. Furthermore connection is only open once for the execution of the loop. (It is an expensive operation to open/close connection)

Next I will profile the C# code to check (VS2010/12 Ulitmate has profiler) to determine where are the bottlenecks. If you find they are located in the SQL /LDAP interaction then you go to thos

Finally I will consider changing the logic a little and see if you can use SQLBulkCopy or set statements:
For example doing the updates first using update as one big sql statement as shown here
http://stackoverflow.com/questions/184096/update-multiple-rows-into-sql-table
and then use SQLBulkCopy for the inserts by building a datatable:
http://www.dotnetcurry.com/ShowArticle.aspx?ID=323
Disclaimer: I do not know the complexity of your SQL/LDAP interaction  the last suggestion may  work depending on your program design
0
 
adriankohwsCommented:
Not really know about this structure thing but can you write a stored procedure with insert and update statement into one, check if exists, update, otherwsie insert. Meaning you don't check or loop for checking, just execute this stored procedure and either they will be updated or inserted.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
anbu210Author Commented:
Thanks All for your valuable suggestions......
madgino, you were right the issue was with LDAP, it was taking more time than insert and Update operation.

Now I have taken the complete LDAP values in a datatable before the forloop and trying to use dataview and rowfilter within the for loop....Now I have an issue with using rowfilter...

part of my code within Forloop.
login_id = dt.Rows(i)["LOGIN_ID"].ToString().Trim();  //login_id is string.

LDAPDtView = dstLDAPMain.DefaultView;              

LDAPDtView.RowFilter = "uid=' "  + login_id + " ' ";

There is no syntax error with above statements but getting an exception at runtime.
"Cannot perform '=' operation on System.Object[] and System.String."

After adding a quick watch found that uid (column from LDAP) was of type System.Object and within uid [0] it stores the value in it.(refer attachment image)

Can someone help me with this?
QuickWatch-LDAPValue.JPG
0
 
CM2013Commented:
As the users suggested above, conn object need not be opened and closed again. Also, sending it as one execute query will make it as one trip to the server and back.

If the data is huge, Stored pocs can be used as its processed at the server and the results returned back. Its also pre-compiled.

The error you see clearly says different datatypes are compared. Apples and oranges cannot be added.

What is loginID returning on debug? Also, try using String.Format to set RowFilter and use CultureInfo.InvariantCulture to set the datatype. Several examples are here:

http://www.csharp-examples.net/dataview-rowfilter/

Also, remove extra spaces in the rowfilter string between the plus signs and try.
0
 
madginoCommented:
did you tried:

LDAPDtView.RowFilter = "uid[0]=' "  + login_id + " ' ";

or

LDAPDtView.RowFilter = "uid(0)=' "  + login_id + " ' ";
0
 
anbu210Author Commented:
Madgino, I had already tired the first option however now also tried the second statement you gave below are the errors I am getting...

for 1st Statement :
 LDAPDtView.RowFilter = "uid[0]=' "  + login_id + " ' ";

Open in new window


error:
?ex.Message
"Syntax error: Missing operand after '[0]' operator."

For 2nd Statement:
LDAPDtView.RowFilter = "uid(0)=' "  + login_id + " ' ";

Open in new window

error:
?ex.Message
"The expression contains undefined function call uid()."

I also tried with below options..still no luck :(

LDAPDtView.RowFilter = String.Format("uid[0] = '{0}'", login_id);

Open in new window


In order to retrieve the value of uid from the main datatable I had to use below code in immediate....Hope this will give you some idea to use in rowfilter...


?(dstLDAPMain.Rows[0].ItemArray[0] as object[])[0]

Open in new window

"jlane"

so uid is just not a system object, its a array of systemobject which contains only one value in it.....
0
 
anbu210Author Commented:
Let me know if any other way to compare a System array object of a datatable column with the string value using rowfilter ?

Now I am planning to add another datacolumn of type string and use for loop to copy all data value of uid to this new data column named UserID whose type is string and then use it as

LDAPDtView.RowFilter = "UserID='" + login_id + "'";
LDAPDtView.RowFilter = "UserID='" + login_id + "'";

Open in new window

0
 
madginoCommented:
Unfortunately I couldn't find anything about RowFilter over Array/Object so probably it is not possible.
0
 
anbu210Author Commented:
madgino,
Not an issue, In a function I used below code to add a new column and use this column for the rowfilter....Now the code runs much faster rate than compared to earlier......

The main catch was connecting to LDAP each time within For-loop.

 dstLDAP.Columns.Add("UserID", typeof(String));

          for (int i = 0; i < dstLDAP.Rows.Count; i++)
          {
              dstLDAP.Rows[i]["UserID"]  = (dstLDAP.Rows[i].ItemArray[0] as object[])[0].ToString();
          }   

Open in new window


Thanks a lot....
0
 
madginoCommented:
you are welcome
0
 
anbu210Author Commented:
Provided an idea for the many cause for the program to run slow....
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now