开发者

Getting SQL Connection fragmentation, different way to connect to DB's

开发者 https://www.devze.com 2023-02-06 13:52 出处:网络
We have multiple DB servers. On one of the servers we have a master config table that holds instructions as to what DB server and DataBase name an Agency is supposed to use.

We have multiple DB servers. On one of the servers we have a master config table that holds instructions as to what DB server and DataBase name an Agency is supposed to use.

Currently each Database always has 2 connections on them, even if they're not being used (which we are fixing). However, we're trying to find a way to make it so our connections are not all over the place, and relieve some of the stress on our DB Servers.

After a lot of research we found some articles saying to do all connections to a central location, and then Change which database we're using through the SQLConnection object. Which seems a bit roundabout, but could work.

So I'm wondering what others do in this situation?

The current path for this is:

-User Logs in -System access ConfigTable to find out which database user is going to connect to. -System loads the Agency connection settings into memory (SEssion) for that user. -Every request now directly hits that users database.

Is there a mo开发者_开发技巧re efficient way of doing this?


Open connections late, and close them early.

For example:

string result;
using (var con = new SqlConnection(...))
{
    con.Open();
    var com = con.CreateCommand();
    com.CommandText = "select 'hello world'";
    result = com.ExecuteScalar();
}

The Windows OS will make sure to efficiently pool and reuse connections. And since you're only using connections when you need them, there are no idle connections lying around.

EDIT: Windows only caches connection strings that are literally the same, so if you use Initial Catalog=<dbname> in the connection string, that could hurt performance by requiring 500+ "connection pools" for one server.

So if you have 4 servers with a lot of databases, make sure you only use 4 connection strings. After connecting, switch database with:

com.CommandText = "use <dbname>";
com.ExecuteNonQuery();

Or query with a three-part name like:

select * from <dbname>.dbo.YourTable
0

精彩评论

暂无评论...
验证码 换一张
取 消