Sql connection performance advice please!

  • Thread starter Thread starter Steven Blair
  • Start date Start date
S

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
 
You need to provide the connection string, as alot of variables in it can
sometimes affect performance.
 
Steven said:
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.
Click to expand...

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#)
------------------------------------------------------------------------
 
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
 
Here is an exmaple of a connection string:

Data Source=localhost;Initial Catalog="TS3 Help Desk";Persist Security
Info=True;User ID=sa
 
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.
 
|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
|
|
|
|

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.
 
Sorry, my bad.
SqlTransaction should have been SqlConnection.

No Transactions are being used in this example.
 
Back
Top