Best practice: single or multiple connections

Transact-SQL
1

Hi folks,

This may be a very basic question but I was wondering, what is best practice between the two following alternatives:

  1. Opening several connections to distinct databases and opening datasets which are tuned to each particular connection (e.g. Select ID from MyTable, cn1).

  2. Opening a single connection and opening all datasets using that connection (e.g. Select ID from DB1.DBO.MyTable, cn).

In other words, what are there performance costs of each?

Thanks!

2

A few thoughts...

  • Schema qualify any objects you reference. For example, SELECT column FROM dbo.Table. This helps with query plan reuse and troubleshooting.
  • You should get the same performance using both approaches. I haven't encountered issues with this.
  • I tend to think a lot about support-ability. Be sure if a "thing" (web site, application, script, etc.) runs against your database, that it sets an application name that allows you to identify it.
  • Second, if any of the databases may move to a different server, put them in separate connections.
  • It's possible to have multiple connection strings defined that currently all point to the same thing. That makes it easy to redirect read-only queries or move certain databases in the future.
3

Thanks Graz, I really appreciate you taking time to respond. Definitely provides a good response to "what's best practice"! Thanks!