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?

score 2 · Answer 1 · answered Jun 17 '09 at 18:48

2

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

answered Jun 17 '09 at 18:48

D'Arcy Rittich

167,292
40
290
283

Thanks, this is a great alternative. Is there a way to code this if I don't know for sure that each of the tables exists? In other words, how can a defend against the situation where NyDataTable doesn't exist? – Sisiutl Jun 17 '09 at 19:24
1

I would construct the above query dynamically based on information obtained from: select * from information_schema.tables – D'Arcy Rittich Jun 17 '09 at 19:30

score 0 · Answer 2 · answered Jun 17 '09 at 21:07

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.

MARS vs NextResult

2 Answers2