Using multiple SqlConnection objects, bad for performance?

[Guest]

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.


With applications I mean ASP.NET applications.


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.

 

[Joerg Jooss]

Thus wrote the friendly display name,

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.

With applications I mean ASP.NET applications.

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.

That sounds suspiciously as though you want to use global connection objects.
Forget about that. Open a connection only when you need it, and close it
immediately after you're done.

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

It depends on your transaction boundaries and what API you use for transactions
(System.EnterpriseServices, System.Data, System.Transactions, ...). What
you definitely want to avoid is accidentally ending up with a global transaction
where a local transaction would have sufficed (that can even happen with
a single DB).

If eliminating DB3 makes sense depends on whether and how both applications
contend for the same resources. I don't think that there's a general answer
to that question.

Cheers,

 

[Mark Rae]

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.

Er, no... What you need (OK, what I would recommend...) is that you use a
DAL (database abstraction layer) based on the Microsoft one
(http://msdn2.microsoft.com/en-us/library/aa480458.aspx) which will create
an internal connection object and command objects (and any other type of
ADO.NET object) as and when required, and destroy them immediately they are
no longer needed.

Since you have three databases, you will probably end up with three
individual connection strings which will, in turn, mean that you will have
three separate connection pools from which the connection objects will be
reused by the DAL to speed things up even further. Under normal
circumstances, this should just happen automatically for you without any
additional coding.

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

Using a DAL means that you never need to use any SqlConnection or SqlCommand
objects in your page at all...

 

[Guest]

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.


With applications I mean ASP.NET applications.


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.

I don't really see any reason to have more than one database. If the
applications will have some of their data in a shared database, they
could just as well have all their data in that database.

 

[Guest]

Hi,

no, I am not using global connection objects, I usualy close connections
immediately too, the two possible connections will not be open at the same
time.

I am using System.Data.

My main question is, will pooling stop working, or be inefficent, or some
other great slowdown, based on the architecture of .net and sql server, if I
use a second SqlConnection object with anohter connection string in a aspx
page?