MARS vs NextResult
我通过从多个表收集数据来补充我的业务对象，例如
SELECT * FROM CaDataTable; SELECT * FROM NyDataTable; SELECT * FROM WaDataTable;
等等...... （C# 3.5、SQL Server 2005）
我一直在使用批处理：
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());
}
}
}
我还针对同一连接使用了多个命令：
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);
}
}
}
}
其中一种技术是否明显优于另一种？ 另外，如果我在第二种方法上使用 MARS 会有增益吗？ 换句话说，是否就像在连接字符串中设置 MultipleActiveResultSets=True 一样简单并获得巨大的好处？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果每个表中的数据结构都相同，我会这样做：
If the data structure is the same in each table, I would do:
如果没有实际对两个版本进行相互计时，您只能推测......
我希望版本 1 (BatchReader) 会更快，因为您只需要与数据库进行一次往返。 版本 2 需要 3 次不同的往返 - 您执行的每个查询各一次。
但再次强调：您只能真正通过测量来判断。
马克
哦，PS：当然，在现实生活中，它也有助于限制返回的列，例如不要使用
SELECT *，而是使用SELECT（字段列表）< /code> 并使该字段列表尽可能短。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 useSELECT (list of fields)and keep that list of fields as short as possible.