mysql多表联合查询快还是分开查询快_[实验] 两个表关联查询快，还是分开多次连库查询快...

private static string connectionStr = "data source=192.168.6.37;database=Tidebuy;uid=sa;pwd=123456;";

private static string idStr= @"73498,73514,73546,73716,73798,74029,74042";

static void Main(string[]args)

{

#region 生成数据//for (int index = 100001; index <=200000; index++)//{// Test1 entity =new Test1()//{// Name = "Test" + index.ToString(),// Description =""//};// Encoding gb = System.Text.Encoding.GetEncoding("GB2312");// //调用函数产生4个随机中文汉字编码// object[] bytes = CreateRegionCode(150);// string number = index % 3 == 0 ? index.ToString() : "";// //根据汉字编码的字节数组解码出中文汉字// for (int i = 0; i < 10; i++)//{// entity.Description += gb.GetString((byte[])Convert.ChangeType(bytes[i], typeof(byte[]))) + number;//}// if(InsertData(entity, connectionStr))//{// //输出的控制台//Console.WriteLine(entity.Name);//}//}

#endregionDateTime start = DateTime.Now;

Console.WriteLine("左连操作执行开始："+ start.ToString("yyyy-MM-dd HH:mm:ss fff"));int count =SelectFromTB_OrderLeftJoinTB_OrderLog();

Console.WriteLine("左连操作结果："+ count);DateTime end = DateTime.Now;

Console.WriteLine("左连操作执行结束："+ end.ToString("yyyy-MM-dd HH:mm:ss fff"));

Console.WriteLine("耗时："+ (end -start).ToString());//DateTime start = DateTime.Now;//Console.WriteLine("连库操作执行开始：" + start.ToString("yyyy-MM-dd HH:mm:ss fff"));//string orderIdStr =SelectFromTB_Order();//int count =SelectFromTB_OrderLog(orderIdStr);//Console.WriteLine("连库操作结果：" + count);//DateTime end = DateTime.Now;//Console.WriteLine("连库操作执行结束：" + end.ToString("yyyy-MM-dd HH:mm:ss fff"));//Console.WriteLine("耗时：" + (end - start).ToString() +"\r\n\r\n");

}///

///20w数据的表///

///

publicstatic string SelectFromTB_Order()

{

string sql= string.Format(@"SELECT * FROM TB_Order WHERE ID IN({0})", idStr);

DataTabletable =new DataTable();

TbDatabase.Fill(connectionStr, sql,table);

StringBuilder orderIdStr=new StringBuilder();if (table!=null&&table.Rows.Count>0)

{

foreach (DataRow rowin table.Rows)

{

orderIdStr.Append(row["ID"].ToString() +",");

}

}if (orderIdStr.Length>0)

{return orderIdStr.ToString().Substring(0, orderIdStr.Length - 1);

}else{return"";

}

}///

///105w的数据量///

///

///

public static intSelectFromTB_OrderLog(string orderIdStr)

{

string sql= string.Format(@"SELECT * FROM TB_OrderLog WHERE OrderID IN({0})", orderIdStr);

DataTabletable =new DataTable();

TbDatabase.Fill(connectionStr, sql,table);if (table != null && table.Rows.Count > 0)

{return table.Rows.Count;

}else{return 0;

}

}///

/// 20w*105w左连///

///

public static intSelectFromTB_OrderLeftJoinTB_OrderLog()

{

string sql= string.Format(@"SELECT * FROM TB_OrderLog ol WITH(NOLOCK) LEFT JOIN TB_Order o ON ol.[OrderID]=o.[ID]

WHERE o.ID IN({0})",idStr);

DataTabletable =new DataTable();

TbDatabase.Fill(connectionStr, sql,table);if (table != null && table.Rows.Count > 0)

{return table.Rows.Count;

}else{return 0;

}

}