CODEWITHSUNDEEP
c#sql.netsql-servermultithreading
rajcool111
rajcool111

Reputation: 667

C# Execute SQL SP using multithreaded or parallel execution

I have a SQL Server SP and would like to execute that for around 10 million user record sets to process the user info and update DB. Currently its taking few millisecond for 1 user to update in DB. Since, it has to process million records what are the best ways to do it?

I am thinking do it from the C# application using multithreaded or parallel foreach. Below is the basic code to run the SP, how can I use it to run it in multithreaded mode to do the processing job faster?

 string connString = "data source=.\SQLEXPRESS;initial catalog=Test;integrated security=True;";
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("ETL_TEST", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@user_id", '12345'));
            cmd.ExecuteReader();
            conn.Close();

Upvotes: 2

Views: 2682

Answers (3)

Eric J.
Eric J.

Reputation: 150108

how can I use it to run it in multithreaded mode to do the processing job faster?

Parallel.ForEach() is a very reasonable starting point to accomplish your goal. Up to a point, sending concurrent requests to SQL Server can improve overall throughput.

Put your code into the action of Parallel.ForEach(). Use using statements to ensure that things are properly disposed (right now you will leave a connection open if an exception is thrown before it is closed).

Note that the .NET Framework will attempt to find an optimal number of threads (and different versions of the framework may end up selecting different numbers). It is possible that .NET will select a number of threads that is either

  • Too low (which you cannot influence, but you could solve by creating and managing tasks explicitly)
  • Too high, resulting in inefficient execution in SQL Server (due to IO or lock contention, high CPU, etc). In this case, you can use the MaxDegreeOfParallelism property to limit the number of threads. Don't forget to adjust the property if you move to a more or less powerful SQL Server.

Note that a multi-threaded solution, while probably more efficient than a single-threaded one, is still very chatty. It makes a request to the database for each user record. In this type of situation, I have achieved order-of-magnitude performance improvements by sending a batch of work over to SQL Server rather than a single work item. You'll likely see the greatest gains by redesigning the stored procedure to accept a number of users at once.

Upvotes: 3

Steve
Steve

Reputation: 5545

There are several ways you could speed this up:

  1. Pass the data in a table-value parameter so you can process them in one call is likely the best way, but it does take a bit of setup and if this is a one-time thing, not really worth it, IMO.
  2. Since you are dealing with a single parameter, turn it into a comma-separated value string and pass it in so you can process a couple thousand per call on the server.
  3. The simplest thing you can do without changing much code is put this in a transaction and commit your records every couple thousand records, or all at once. Doing this will increase the speed by about 100-fold.

Upvotes: 0

Solomon Rutzky
Solomon Rutzky

Reputation: 48806

Since, it has to process million records what are the best ways to do it?

If you are asking what is the best way to make the process parallel, then @Eric's answer looks promising. I would agree that generally speaking, "Up to a point, sending concurrent requests to SQL Server can improve overall throughput.", as long as we really stress the "Up to a point" part. But "that point" could easily be just a few running at a time (depending on the logic and schema), which isn't the type of gain you are looking for.

If you are asking what is the best way to accomplish the processing of 10 million "sets" of records as quickly / efficiently as possible, well, that is a much more involved question. It requires a bit more analysis. At bare minimum it would require reviewing both the logic in your ETL_TEST Stored Procedure and your Schema (Tables, Indexes, etc). So for the moment, there is not enough information presented here to provide any meaningful assistance along these lines.

Upvotes: 0

Related Questions