在多头服务中使用SqlConnection以获得更好的性能? [英] Use SqlConnection in multitheading service for better performance?

问题描述

大家好，

我的项目是使用SqlConnection (sql server + C#)的多线程服务，稳定性和性能非常重要，并且没有太多的连接.

实际上，我并没有太多使用TSQL的经验，现在我正在考虑使用SqlConnection的2种解决方案:

-解决方案1:每次使用Using (SqlConnection con = new SqlConnection(someConnectionString)){...} =>就是dispose() SqlConnection.

-解决方案2:Using SortedList<ThreadName, SqlConnection>管理SqlConnection，每个线程使用其自己的1 SqlConnection，它被打开了1次，而不是dispose()，因此仍然保持同时打开的连接数足够小，因为我们同时保持线程数小于阈值.

我不知道哪种解决方案更好(稳定性和性能都很好).

你能给我一些指导吗?

在此先感谢，
TuanNM

Hi all,

My project is multi-Thread service using SqlConnection (sql server + C#), stability and performance are very importance, and there are not too much connection at same time.

In fact, I does has not much experiences of working with TSQL, now I am considering 2 solution of working with SqlConnection:

- Solution1: Using (SqlConnection con = new SqlConnection(someConnectionString)){...} => so dispose() the SqlConnection each time we use.

- Solution2: Using SortedList<ThreadName, SqlConnection> to manage SqlConnection, each Thread using its own 1 SqlConnection, it was opened 1 time and wasn''t dispose(), so still keep Number of Connection opening at same time is small enough, because we keep Number of Thread at same time is smaller than a threshold.

I have no idea which solution is better (good stability and performance).

Could you give me some guide.

Thank in Advance,
TuanNM

推荐答案

根据您提供的信息，我将首先尝试使事情尽可能简单.因此，创建连接并在不再需要它时将其关闭将是我的选择.原因之一是连接池可以帮助您提高打开连接的性能.

更多信息，请参见:
- SQL Server连接池(ADO.NET) [ ^ ]
- SQL Server:了解和控制连接池碎片 [ ^ ]

Based on the info you provided, I''d first try to keep things as simple as possible. So creating the connection and closing it when it''s not needed anymore would be my choice. One reason is that the connection pooling helps you with the performance regarding to open the connection.

More info, see:
- SQL Server Connection Pooling (ADO.NET)[^]
- SQL Server: Understanding and Controlling Connection-Pooling Fragmentation[^]

这篇关于在多头服务中使用SqlConnection以获得更好的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！