locked
Using multiple SqlConnection objects, bad for performance? RRS feed

  • Question

  • 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.

    Friday, February 9, 2007 8:52 PM

All replies

  • Hi,

    I will begin by this : Each connection must be closed when it's no more needed. This is the better way to save system resources.

    That means that you must not have a unique SqlConnection object that you use in all the application, but when you need to get/push some data to the database you open a new connection and after that u close and dispose it. Don't care about performance when connecting because ADO.NET uses pooling to make an open bridge between the application and the database even you close the connection. Simply use the same connection string for the application.

    HTH.

    Saturday, February 10, 2007 6:46 AM
  • The Databases are all on the same machine and on the same instance. In the source code, I usualy close connections immediately, the two possible connections will not be open at the same time.

    ---

    "Simply use the same connection string for the application."

    ---

     

    Hmm. Do you mean  pooling will stop working, or be inefficent, if I use a second SqlConnection object with anohter connection string in a aspx page?

    Saturday, February 10, 2007 6:52 PM
  • Hi,

    By the "same connection string", i mean that :

    "Data source=(local);database=db1;user id =sa" is different from

    "Data source=(local);user id =sa;database=db1"

    It's the same connection but pooling will consider them as two different connections.

    I am not sure but it's also case sensitive.

    I exeprienced some problems with pooling when i used a function to build connections strings from given parameters. When the parameters order changes the pooling will create a new bridge for the "new" connection even it's the same.

    So when u have your connection string stored in the config file or in a global var it's ok.

    Monday, February 12, 2007 8:40 AM
  • Couple of things I would add to this:

    1) While closing connections when you're done is more efficient for system resources, it can also lead to wrongly detected "denial of service" attacks on the SQL Server. This will depend on what your app is doing and how many hits it is likely to take.

    2) With SQL Server, you can query tables in other databases on the same server by qualifying the table with the database name. So you can use Select <DB>.<Table>.<Column> to access any database that you have access to on your current connection.

    3) ADO.NET pooling is turned on by default, but you can turn it off on the connection string (pooling=false). There are times where I have needed to do this as connections can be left lying around even though you've got rid of them

     

    HTH

    -aj

    Monday, February 12, 2007 10:16 AM
  • "2) With SQL Server, you can query tables in other databases on the same server by qualifying the table with the database name. So you can use Select <DB>.<Table>.<Column> to access any database that you have access to on your current connection."

     

    Doesn't work on my tryings. Can you be a bit more specific?

     

    "to access any database that you have access to on your current connection"

    Does that mean, I can access with one connection string multiple DB's?

    Monday, February 12, 2007 8:04 PM
  • Sorry, duff information.

    I've used it in the past with the information schema views but obviously not with tables.

    you can run the following SQL script

    use master

    select * from northwind.information_schema.tables

    to get the schema information but normal views don't work and tables don't work - how frustrating

     

    Sorry about that....

    Tuesday, February 13, 2007 8:49 AM
  •  VeryBoringNickname wrote:

    Does that mean, I can access with one connection string multiple DB's?

    Yes, and you can use for your situation. Maybe is better to leave the current database structure. You will have stored procedures in DB1 and DB2 that will get data from DB3. So to get some data from table located in DB3 from procedure located in DB1 you will have this sql code:

    Select * from DB3.dbo.MyDataTable

    which means you can access table with name 'MyDataTable' from different database using the sintax of [DatabaseName].[DatabaseOwner].[TableName]

    Tuesday, February 13, 2007 11:11 AM
  • Thank you all.

    Now, I don't want to beat a dead horse. And I don't want to annoy, but, how is the performance if I use stored SP's to access the tables of other databases? Will it be at the same speed as if the SP is accessing its own database? Or slightly slower? Or not so slightly? If slightly, I can live with it.

    Wednesday, February 14, 2007 6:35 PM
  • There is a difference but not something you should worry about. If your databases disign is right for you, go for it.
    Wednesday, February 14, 2007 9:59 PM