Solved

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

Posted on 2013-06-01
12
440 Views
Last Modified: 2013-06-04
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
Comment
Question by:anbu210
12 Comments
 
LVL 11

Accepted Solution

by:
madgino earned 500 total points
ID: 39212942
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
 
LVL 35

Expert Comment

by:Miguel Oz
ID: 39213643
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
 
LVL 10

Expert Comment

by:adriankohws
ID: 39213985
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
 

Author Comment

by:anbu210
ID: 39214002
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
 
LVL 1

Expert Comment

by:CM2013
ID: 39214753
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
 
LVL 11

Expert Comment

by:madgino
ID: 39215430
did you tried:

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

or

LDAPDtView.RowFilter = "uid(0)=' "  + login_id + " ' ";
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:anbu210
ID: 39216287
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
 

Author Comment

by:anbu210
ID: 39216319
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
 
LVL 11

Expert Comment

by:madgino
ID: 39216399
Unfortunately I couldn't find anything about RowFilter over Array/Object so probably it is not possible.
0
 

Author Comment

by:anbu210
ID: 39216923
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
 
LVL 11

Expert Comment

by:madgino
ID: 39217253
you are welcome
0
 

Author Closing Comment

by:anbu210
ID: 39218466
Provided an idea for the many cause for the program to run slow....
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now