c# – SqlConnection在打开时挂起
我有一个程序可以将多个请求并行发送到SQL Server实例.在我的所有并发请求中,其中一个总是在db.open()上阻塞几秒钟.
void MyMethod() { var brands = new List<string>{"Chevy","Honda","Ford","GM"}; var foundCars = new ConcurrentBag<Car>(); Parallel.ForEach(brands,brand => { logger.Trace(brand + " before enqueue"); foundsCars.Enqueue(FindCar(brand)); logger.Trace(brand + " after enqueue"); }); } public Car FindCars(string brand) { using (var db = new SqlConnection(connectionString)) { sqlLogger.Trace("Brand " + " brand " + " before db open"); db.Open(); sqlLogger.Trace("Brand " + " brand " + " after db open"); using (var cmd = new SqlCommand(sqlCmd,db)) { while (reader.Read()) { //SQL SELECT Stuff going on } } } } 如果我查看我的跟踪,我会看到类似的东西,顺序取决于我猜测的数据库响应时间,在这种情况下,本田是数据正在缓慢处理,但它并不总是: 12:00:00.0000 Chevy before enqueue 12:00:00.0000 Honda before enqueue 12:00:00.0000 Ford before enqueue 12:00:00.0000 GM before enqueue 12:00:00.0200 Brand Chevy before db open 12:00:00.0300 Brand Honda before db open 12:00:00.0200 Brand Ford before db open 12:00:00.0200 Brand GM before db open 12:00:00.0300 Brand Chevy after db open 12:00:00.0300 Brand Ford after db open 12:00:00.0300 Brand GM after db open 12:00:00.0400 Chevy after enqueue 12:00:00.0400 Ford after enqueue 12:00:00.0400 GM after enqueue 12:00:07.0000 Brand Honda after db open <-- usually around 7 seconds late 12:00:07.0100 Honda after enqueue 我似乎无法在我的SQL Server 2008实例上本地复制此问题,但它始终在SQL Server 2012实例上发生.这个问题似乎是一个配置问题.它总是发生在我发送并发请求和SELECT请求时.如果我在SQL Server Management中执行阻塞查询,它将在子100ms内返回. 这是我的连接字符串: <add key="SqlConnectionString" value="Data Source=10.0.20.20;Initial Catalog=CarsDB;Integrated Security=SSPI;Connection Timeout=240;" /> 编辑: 我在我的连接字符串中插入“Min Pool Size = 30”,似乎只有第一次,第二次或第三次方法被命中它在15秒内运行,然后所有其他方法调用都是500ms以下. 我还在我们的测试环境中运行了SQL Server 2012,其中表中的元数据与看到问题的地方相同,我无法复制此问题.这似乎是某处的配置问题. 解决方法
除非你有一个分区表,否则这是疯了
Parallel.ForEach(brands,brand => { logger.Trace(brand + " before enqueue"); foundsCars.Enqueue(FindCar(brand)); logger.Trace(brand + " after enqueue"); }); 在同一个表之后的多个连接并不快,更不用说多个连接的开销了.另一方面,您有一个物理读写头.你不是那么开放和关闭连接比简单阅读更长?真的,你只需将桌子排成一行并从中读取.读者非常快. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |