anbu210
asked on
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_I D"].ToStri ng().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"].ToS tring().Tr im() + "' AND PROJECT_POOL_ID = " + dt.Rows(i)["PROJECT_POOL_I D"].ToStri ng().Trim( ), conn);
int k = (int)cmdInsert.ExecuteNonQ uery();
int j = (int)cmdUpdate.ExecuteNonQ uery();
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?
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_I
//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"].ToS
int k = (int)cmdInsert.ExecuteNonQ
int j = (int)cmdUpdate.ExecuteNonQ
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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"].ToS tring().Tr im(); //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
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"].ToS
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
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.InvariantCultu re 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.
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.InvariantCultu
http://www.csharp-examples.net/dataview-rowfilter/
Also, remove extra spaces in the rowfilter string between the plus signs and try.
did you tried:
LDAPDtView.RowFilter = "uid[0]=' " + login_id + " ' ";
or
LDAPDtView.RowFilter = "uid(0)=' " + login_id + " ' ";
LDAPDtView.RowFilter = "uid[0]=' " + login_id + " ' ";
or
LDAPDtView.RowFilter = "uid(0)=' " + login_id + " ' ";
ASKER
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 :
error:
?ex.Message
"Syntax error: Missing operand after '[0]' operator."
For 2nd Statement:
?ex.Message
"The expression contains undefined function call uid()."
I also tried with below options..still no luck :(
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...
so uid is just not a system object, its a array of systemobject which contains only one value in it.....
for 1st Statement :
LDAPDtView.RowFilter = "uid[0]=' " + login_id + " ' ";
error:
?ex.Message
"Syntax error: Missing operand after '[0]' operator."
For 2nd Statement:
LDAPDtView.RowFilter = "uid(0)=' " + login_id + " ' ";
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);
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]
"jlane"so uid is just not a system object, its a array of systemobject which contains only one value in it.....
ASKER
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 + "'";
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 + "'";
Unfortunately I couldn't find anything about RowFilter over Array/Object so probably it is not possible.
ASKER
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.
Thanks a lot....
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();
}
Thanks a lot....
you are welcome
ASKER
Provided an idea for the many cause for the program to run slow....
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:
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