SQL Server 2008 — execute queries in parallel

Question

Is there an easy way to execute queries in parallel? I have a query that has something like this:

delete from TableA where id = @id
delete from TableB where id = @id
delete from TableC where id = @id
...

and I want to do them in parallel. I can also use C#, though not sure how.

Answer 1

First idea is to have separate threads and separate connections but I think you can manage it with multiple connections in a single thread using async callbacks:

string[] tables = new string[] { "TableA", "TableB", "TableC" ... };
var runningCommands = new List<SqlCommand>();
foreach(var table in tables)
{
  var conn = new SqlConnection(...);
  conn.Open();
  var cmd = new SqlCommand("DELETE FROM " + table + " WHERE id = @id");
  cmd.Parameters.Add(new SqlParameter("@id", id);
  cmd.BeginExecuteNonQuery(); 
  runningCommands.Add(cmd);
}
// now wait for all of them to finish executing
foreach(var cmd in runningCommands)
{
  cmd.EndExecuteNonQuery();
  cmd.Connection.Close();
}

Answer 2

    List<string> list;
    list.Add("query1");
    list.Add("query2");
    list.AsParallel().ForAll(query => ExecuteQuery(query));

Answer 3

Use SSIS. Put 3 Execute sql tasks on the control flow. Add a delete statement to each task. When the package executes, they will all get executed at the same time.

You could also create a job for each statement and schedule them all to run at the same time.

Async callback would also work, but the 2 above are easier for someone with a dba skillset to implement and manage.