Faster update for millions of records in Web Forms

Question

So I need to update some over 1 million records from an asp.net web forms project in the code behind with a data given in a TextBox. I tried to do that with LINQ but that takes to long...

1st Question: What is the best solution?

I noticed that if I run the update in SQL (MSSQL) it only takes 20-30 seconds and that is an acceptable time.

2nd Question: Should I create procedures in SQL and import them in my project and call the procedures ? Will that give me a much better time? Basically, will using imported procedures bring the time down to close to the time needed for that query to run in SQL?

Not exactly a duplicate, but you should read [this old question and answer of mine](http://stackoverflow.com/questions/7637907/query-extremely-slow-in-code-but-fast-in-ssms). I had the exact same problem as you (slow in c# but same query is fast in SSMS), it was because of the datatype I was passing in was not the correct type which caused the query not to use the index on the table. — Scott Chamberlain, Apr 10 '13 at 03:33

score 0 · Answer 1 · answered Apr 10 '13 at 02:39

If running it via a normal query is faster, create a stored procedure that accepts a parameter:

using (SqlConnection con = new SqlConnection("connection string here")) {
    using (SqlCommand cmd = new SqlCommand("sp_Stored_Proc_Name_Here", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@VariableNameHere", SqlDbType.VarChar).Value = textBoxNameHere.Text;

      con.Open();
      cmd.ExecuteNonQuery();
    }
}

score 0 · Answer 2 · answered Apr 10 '13 at 02:40

0

Stored Procedures should be the best solution.

However, you can use ADO.NET to call these stored procedures without the need to import them in your Project.

Stored Procedures are SQL-Injection Safe

answered Apr 10 '13 at 02:40

Muhammad Omar ElShourbagy

6,718
2
32
50

score 0 · Answer 3 · answered May 06 '14 at 15:28

Use this to gain high-performance on your app:

1) Create a stored proc that accepts a TVP (Table-Valued-Parameter) when calling it.

That way, you call the proc only ONE TIME and flush ALL the data at once
You MUST use ADO.Net to call it (LINQ does not support TVPs)
Here are the resources:
- Define the proc http://technet.microsoft.com/en-us/library/bb510489.aspx
- Call from C# http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx

2) Make sure your TVP uses a table-value-data-type that matches in column-data-types against the expected indexes of the target table (so SQL can effectively use indexes or anything required).

3) Use a correct transaction isolation level inside your proc. For example, "READ COMMITTED", which gives good response.

4) Add into your stored proc the "SET NOCOUNT ON", so the proc won't signal ADO.Net each time a T-SQL statement is executed.

5) On your SqlConnection object (in C#), set the PacketSize property to something between 3000 and 8000. Play with the values until you get the right response time. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.packetsize.aspx

Faster update for millions of records in Web Forms

3 Answers3