MARS vs NextResult

Question

I rehydrate my business objects by collecting data from multiple tables, e.g.,

SELECT * FROM CaDataTable;
SELECT * FROM NyDataTable;
SELECT * FROM WaDataTable;

and so on... (C# 3.5, SQL Server 2005)

I have been using batches:

    void BatchReader()
    {
        string sql = "Select * From CaDataTable" +
                     "Select * From NyDataTable" +
                     "Select * From WaDataTable";

        string connectionString = GetConnectionString();
        using (SqlConnection conn = new SqlConnection(connectionString)) {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            using (SqlDataReader reader = cmd.ExecuteReader()) {
                do {
                    while (reader.Read()) {
                        ReadRecords(reader);
                    }
                } while (reader.NextResult());
            }
        }
    }

I've also used multiple commands against the same connection:

    void MultipleCommandReader()
    {
        string connectionString = GetConnectionString();
        string sql;
        SqlCommand cmd;
        using (SqlConnection conn = new SqlConnection(connectionString)) {
            conn.Open();  

            sql = "Select * From CaDataTable";
            cmd = new SqlCommand(sql, conn);
            using (SqlDataReader reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    ReadRecords(reader);
                }
            }

            sql = "Select * From NyDataTable";
            cmd = new SqlCommand(sql, conn);
            using (SqlDataReader reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    ReadRecords(reader);
                }
            }

            sql = "Select * From WaDataTable";
            cmd = new SqlCommand(sql, conn);
            using (SqlDataReader reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    ReadRecords(reader);
                }
            }
        }
    }

Is one of these techniques significantly better than the other? Also, would there be a gain if I use MARS on the second method? In other words, is it as simple as setting MultipleActiveResultSets=True in the connection string and reaping a big benefit?

Answer 1

If the data structure is the same in each table, I would do:

Select *, 'Ca' Source From CaDataTable
union all
Select *, 'Ny' Source From NyDataTable
union all
Select *, 'Wa' Source From WaDataTable

Answer 2

Without actually timing the two versions against one another, you can only speculate....

I hope bet that version 1 (BatchReader) will be faster, since you only get one round-trip to the database. Version 2 requires three distinct round-trips - one each for every query you execute.

But again: you can only really tell if you measure.

Marc

Oh, PS: of course in a real-life scenario it would also help so limit the columns returned, e.g. don't use SELECT * but instead use SELECT (list of fields) and keep that list of fields as short as possible.