Performance problems when using multiple SqlConnection objects?

Hello,

I am planning to use three databases on the local SQL Server. DB1 will be for App1, DB2 for App2, and DB3 will hold tables, that both, App1 and App2 need to access.

Obviously, I need in App1 and App2 at least two SqlConnection and SqlCommand objects, one that access their respective databases, and one that will access DB3.

Will this result in a worse performance? (The using of two SqlConnection objects in one aspx page as example, and switching them)

Should I for better performance get rid of DB3 and add the tables there to DB1 and DB2 ? (Thus multiplying data, but, if the performance will be much better, it will be fine with me)

I would be thankful for any advices.

Hello my friend,

Try and use the same database where possible. By using the same database, and therefore the same connection string, the built-in connection pooling will work and therefore conserve resources for you.

Kind regards

Scotty

|||Thanks|||

ask_Scotty:

Hello my friend,

Try and use the same database where possible. By using the same database, and therefore the same connection string, the built-in connection pooling will work and therefore conserve resources for you.

Kind regards

Scotty

Thanks again, but, the more I am thinking about this and your advice, the more confused I am.

The Databases are all on the same machine and on the same instance. Will pooling stop working, or be inefficent, if I use a second SqlConnection object with anohter connection string in a aspx page? In the source code, I usualy close connections immediately, the two will not be open at the same time.

I am still somewhat unsure what to do.

|||

Hello again my friend,

Connecting to different databases, if necessary, from your web pages should be fine. If you can restrict the number of times you need to connect, this would be better. To understand what I mean, read up on page caching and especially data caching in ASP.NET applications. Basically, data caching involves connecting to the database on the first request for the data and then caching it to service subsequent requests as opposed to connecting to the database each time. Caching methods and strategies form a huge subject but one worth getting into.

Kind regards

Scotty