Sql connection performance advice please!

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Steven Blair
    #1

    Sql connection performance advice please!

    I have a DB connection performance issue in my C# app.
    I have used the Stopwatch to track how long it takes to do a couple
    things.

    Creating a SqlConnection object takes 19ms, and Opening a connection
    (Sql2005) takes roughly 120ms.
    Connecting to Sql2000 is around 64ms.
    Does this sound correct, and can I speed this, since 140ms is
    unacceptable for creating a connection in this app.

    This was tested on a desktop PC. We have some older software which uses
    Visual Basic code which connects a lot quicker.
    On one Server (very high spec) we hav had all our Database activity done
    in <1ms!

    Any help on this would be appreciated.

    Steven



    *** Sent via Developersdex http://www.developersdex.com ***
    Tags: None
    • sloan
      #2
      Re: Sql connection performance advice please!


      You need to provide the connection string, as alot of variables in it can
      sometimes affect performance.




      "Steven Blair" <steven.blair@b tinternet.com> wrote in message
      news:eXLYmuSdGH A.1436@TK2MSFTN GP05.phx.gbl...[color=blue]
      > I have a DB connection performance issue in my C# app.
      > I have used the Stopwatch to track how long it takes to do a couple
      > things.
      >
      > Creating a SqlConnection object takes 19ms, and Opening a connection
      > (Sql2005) takes roughly 120ms.
      > Connecting to Sql2000 is around 64ms.
      > Does this sound correct, and can I speed this, since 140ms is
      > unacceptable for creating a connection in this app.
      >
      > This was tested on a desktop PC. We have some older software which uses
      > Visual Basic code which connects a lot quicker.
      > On one Server (very high spec) we hav had all our Database activity done
      > in <1ms!
      >
      > Any help on this would be appreciated.
      >
      > Steven
      >
      >
      >
      > *** Sent via Developersdex http://www.developersdex.com ***[/color]


        Comment

        • Frans Bouma [C# MVP]
          #3
          Re: Sql connection performance advice please!

          Steven Blair wrote:
          [color=blue]
          > I have a DB connection performance issue in my C# app.
          > I have used the Stopwatch to track how long it takes to do a couple
          > things.
          >
          > Creating a SqlConnection object takes 19ms, and Opening a connection
          > (Sql2005) takes roughly 120ms.
          > Connecting to Sql2000 is around 64ms.
          > Does this sound correct, and can I speed this, since 140ms is
          > unacceptable for creating a connection in this app.
          >
          > This was tested on a desktop PC. We have some older software which
          > uses Visual Basic code which connects a lot quicker.
          > On one Server (very high spec) we hav had all our Database activity
          > done in <1ms!
          >
          > Any help on this would be appreciated.[/color]

          a first physical connection can take time. Though if you enable
          connection pooling (it's enabled by default) you'll see that creating a
          connection will take < 20ms after the first connection.

          FB

          --
          ------------------------------------------------------------------------
          Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
          LLBLGen Pro website: http://www.llblgen.com
          My .NET blog: http://weblogs.asp.net/fbouma
          Microsoft MVP (C#)
          ------------------------------------------------------------------------

            Comment

            • Steven Blair
              #4
              Re: Sql connection performance advice please!

              I have tried using Sql Authentication and windows authentication. Both
              are slow.

              Regarding pooling, if this pseudo code mirrored my app, would 2nd
              connection be quicker?:

              Create SqlTransaction
              Open Connection
              Close Connection

              Open Connection
              Close Connection.

              The problem I have, is my dll is created, Db connection opened, some Db
              work, close connection, then dll is finished. That instance of the dll
              wont be used again.
              At some point a new instance will be created and we have to go through
              all this again.

              It really does seem quite a performance hit using these objects, and as
              hard as it is for me to say, the Visual Basic code appears to be
              quicker!


              Steven


              *** Sent via Developersdex http://www.developersdex.com ***

                Comment

                • Steven Blair
                  #5
                  Re: Sql connection performance advice please!

                  Here is an exmaple of a connection string:

                  Data Source=localhos t;Initial Catalog="TS3 Help Desk";Persist Security
                  Info=True;User ID=sa



                  *** Sent via Developersdex http://www.developersdex.com ***

                    Comment

                    • Nicholas Paldino [.NET/C# MVP]
                      #6
                      Re: Sql connection performance advice please!

                      Steven,

                      If you are using SqlTransaction, you should create it after you open
                      your connection, and then assign it to any commands that are going to use
                      it. It's much slower to create the transaction first.


                      --
                      - Nicholas Paldino [.NET/C# MVP]
                      - mvp@spam.guard. caspershouse.co m

                      "Steven Blair" <steven.blair@b tinternet.com> wrote in message
                      news:O375%23ITd GHA.3888@TK2MSF TNGP02.phx.gbl. ..[color=blue]
                      >I have tried using Sql Authentication and windows authentication. Both
                      > are slow.
                      >
                      > Regarding pooling, if this pseudo code mirrored my app, would 2nd
                      > connection be quicker?:
                      >
                      > Create SqlTransaction
                      > Open Connection
                      > Close Connection
                      >
                      > Open Connection
                      > Close Connection.
                      >
                      > The problem I have, is my dll is created, Db connection opened, some Db
                      > work, close connection, then dll is finished. That instance of the dll
                      > wont be used again.
                      > At some point a new instance will be created and we have to go through
                      > all this again.
                      >
                      > It really does seem quite a performance hit using these objects, and as
                      > hard as it is for me to say, the Visual Basic code appears to be
                      > quicker!
                      >
                      >
                      > Steven
                      >
                      >
                      > *** Sent via Developersdex http://www.developersdex.com ***[/color]


                        Comment

                        • Willy Denoyette [MVP]
                          #7
                          Re: Sql connection performance advice please!


                          "Steven Blair" <steven.blair@b tinternet.com> wrote in message
                          news:eXLYmuSdGH A.1436@TK2MSFTN GP05.phx.gbl...
                          |I have a DB connection performance issue in my C# app.
                          | I have used the Stopwatch to track how long it takes to do a couple
                          | things.
                          |
                          | Creating a SqlConnection object takes 19ms, and Opening a connection
                          | (Sql2005) takes roughly 120ms.
                          | Connecting to Sql2000 is around 64ms.
                          | Does this sound correct, and can I speed this, since 140ms is
                          | unacceptable for creating a connection in this app.
                          |
                          | This was tested on a desktop PC. We have some older software which uses
                          | Visual Basic code which connects a lot quicker.
                          | On one Server (very high spec) we hav had all our Database activity done
                          | in <1ms!
                          |
                          | Any help on this would be appreciated.
                          |
                          | Steven
                          |
                          |
                          |
                          | *** Sent via Developersdex http://www.developersdex.com ***

                          The first connection involves authentication plus the establishment of a
                          physical DB connection, authentication is the most expensive part, but 120
                          msec. looks real good.
                          The second connection (in the context of the same process) will use a
                          connection from the pool (the same as used before), should take less than a
                          tenth of a millisecond.

                          Willy.




                            Comment

                            • Steven Blair
                              #8
                              Re: Sql connection performance advice please!

                              Sorry, my bad.
                              SqlTransaction should have been SqlConnection.

                              No Transactions are being used in this example.



                              *** Sent via Developersdex http://www.developersdex.com ***

                                Comment

                                Previous template Next
                                Working...