Multithread SQL select statements

Question

I am a multithreading novice and a SQL novice, so please excuse any rookie mistakes.

I am trying to execute many SQL queries asynchronously. The queries are all select statements from the same table in the same database. I can run them synchronously and everything works fine, but testing a small subset leads me to believe that to run all the queries synchronously would take approximately 150 hours, which is far too long. As such, I'm trying to figure out how to run them in parallel.

I have tried to model the code after the answer at run a method multiple times simultaneously in c#, but my code is not executing correctly (it's erroring, though I do not know specifically how. The code just says an error occurs).

Here is what I have (A much smaller and simpler version of what I am actually doing):

class Program
{
    static void Main(string[] args)
    {
        List<string> EmployeeIDs = File.ReadAllLines(/* Filepath */);
        List<Tuple<string, string>> NamesByID = new List<Tuple<string, string>>();

        //What I do not want to do (because it takes too long) ...
        using (SqlConnection conn = new SqlConnection(/* connection string */))
        {
            foreach (string id in EmployeeIDs)
            {
                using (SqlCommand cmd = new SqlCommand("SELECT FirstName FROM Employees WITH (NOLOCK) WHERE EmployeeID = " + id, conn))
                {
                    try
                    {
                        conn.Open();
                        NamesByID.Add(new Tuple<string, string> (id, cmd.ExecuteScalar().ToString()));
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }


        //What I do want to do (but it errors) ...
        var tasks = EmployeeIDs.Select(id => Task<Tuple<string, string>>.Factory.StartNew(() => RunQuery(id))).ToArray();
        Task.WaitAll(tasks);
        NamesByID = tasks.Select(task => task.Result).ToList();
    }

    private static Tuple<string, string> RunQuery(string id)
    {
        using (SqlConnection conn = new SqlConnection(/* connection string */))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT FirstName FROM Employees WITH (NOLOCK) WHERE EmployeeID = " + id, conn))
            {
                try
                {
                    conn.Open();
                    return new Tuple<string, string> (id, cmd.ExecuteScalar().ToString());
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }
}

Note: I do not care exactly how this is multithreaded (tasks, parallel.foreach, backgroundworker, etc). This is going to be used to run ~30,000 select queries exactly 1 time, so I just need it to run fast (I'm hoping for ~8 hrs = one work day, but I'll take what I can get) one time. It doesn't have to really be pretty.

Thank you in advance!

Answer 1

This is just plain wrong. You should build one query to select all FirstNames you need. If you need to pass a bunch of ids to the server, that is no problem, just use table valued parameter (aka TVP), coma separated list of values really does not scale well. If the query is correctly written and the tables indexed, that should be quite fast. 100k rows table is a small table.

The query then may look like this

SELECT DollarAmount, comp.CompanyID
FROM Transactions 
JOIN (SELECT MIN(TransactionID) as minTransactionID, CompanyID 
      FROM CompanyTransactions       
      GROUP BY CompanyID
     ) AS comp 
ON Transactions.TransactionID = comp.minTransactionID
JOIN @IDList ON id = comp.CompanyID

You may use IN instead of JOIN if the ids in TVP are not unique.

Btw. do you know what NOLOCK means? If you are the only user of the database and use it single threaded or do not modify any data, then you are safe. Other than that it means that you are okay with a small chance of:

• some records may be missing in the result
• there are duplicate records in the result
• there are rows in the result, that have never been committed and never were accepted as valid data
• if you use varchar(max), you may get text that has never been stored

Answer 2

You want to do one query to get all of the ID/Name combinations, then put them into a dictionary (for quick access). This will remove the very slow process of running 30,000 queries as well as reduce the complexity of your code.

I could get you something more concrete if you posted the actual SQL query (you can change the column and table names if you need) but this should be close:

;WITH CompTransCTE AS (
    SELECT CompanyID, MIN(TransactionID) AS TransactionID
    FROM CompanyTransactions
    WHERE CompanyID IN (/*Comma seperated list of values*/)
    GROUP BY CompanyID
)
SELECT CT.CompanyID, T.DollarAmount, T.TransactionID
FROM Transactions AS T
INNER JOIN CompTransCTE AS CT ON CT.TransactionID = T.TransactionID;

Answer 3

Without creating a User-Defined Table Type in the database, you can use SqlBulkCopy to load the IDs into a temp table, and reference that in the query.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace ConsoleApp11
{
    class Program
    {

        static void Main(string[] args)
        {
            //var EmployeeIDs = File.ReadAllLines(""/* Filepath */);
            var EmployeeIDs = Enumerable.Range(1, 30 * 1000).ToList();
            var dt = new DataTable();
            dt.Columns.Add("id", typeof(int));

            dt.BeginLoadData();
            foreach (var id in EmployeeIDs)
            {
                var row = dt.NewRow();
                row[0] = id;
                dt.Rows.Add(row);
            }
            dt.EndLoadData();

            using (SqlConnection conn = new SqlConnection("server=.;database=tempdb;integrated security=true"))
            {
                conn.Open();

                var cmdCreateTemptable = new SqlCommand("create table #ids(id int primary key)",conn);
                cmdCreateTemptable.ExecuteNonQuery();

                //var cmdCreateEmpable = new SqlCommand("create table Employees(EmployeeId int primary key, FirstName varchar(2000))", conn);
                //cmdCreateEmpable.ExecuteNonQuery();


                var bc = new SqlBulkCopy(conn);
                bc.DestinationTableName = "#ids";
                bc.ColumnMappings.Add("id", "id");
                bc.WriteToServer(dt);

                var names = new List<string>();
                var cmd = new SqlCommand("SELECT FirstName, EmployeeId FROM Employees WHERE EmployeeID in (select id from #ids)", conn);
                using (var rdr = cmd.ExecuteReader())
                {
                    var firstName = rdr.GetString(0);
                    var id = rdr.GetInt32(1);
                    names.Add(firstName);
                }

                Console.WriteLine("Hit any key to continue");
                Console.ReadKey();
            }



        }


    }
}