Sql connection performance advice please!

[Steven Blair]

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 ***

[sloan]

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


"Steven Blair" <st**********@btinternet.com> wrote in message
news:eX**************@TK2MSFTNGP05.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 ***

[Frans Bouma [C# MVP]]

Steven Blair wrote:

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.

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#)
------------------------------------------------------------------------

[Steven Blair]

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 ***

[Steven Blair]

Here is an exmaple of a connection string:

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

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

[Nicholas Paldino [.NET/C# MVP]]

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]
- mv*@spam.guard.caspershouse.com

"Steven Blair" <st**********@btinternet.com> wrote in message
news:O3****************@TK2MSFTNGP02.phx.gbl...

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 ***

[Willy Denoyette [MVP]]

"Steven Blair" <st**********@btinternet.com> wrote in message
news:eX**************@TK2MSFTNGP05.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.

[Steven Blair]

Sorry, my bad.
SqlTransaction should have been SqlConnection.

No Transactions are being used in this example.

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