Slow first connection to SQL Server

[Tommy Martin]

I have just started on a new web project. When I make the first connection
to sql server it takes about 10 to 30 seconds (varies each time) to make the
connection to the server. I am using the following code:

Dim cConnStr as String = "myconnectionstring"
Dim oCon as new SqlConnection(cConnStr)
oCon.Open() <-- Slow part

The Open line takes quite a long time.

I can use the exact same code in a windows app with exact same connect
string and it runs immediately on the same machine.

Any ideas what is going on?

I am running XP Pro SP2 and I tried disabling the firewall but it didn't
change anything. I didin't have this problem before I upgraded to Sp2.

Thanks.

 

[Cowboy \(Gregory A. Beamer\)]

Is this the first connection after the application has been running, or the
first time the page is hit after a recompile? If the later, it is common to
see a JIT hit. You can avoid it a bit with ngen-ing your assemblies (besides
web app), but you will still see a small hit.

Version 2.0 of the Framework makes this much nicer with precompilation
methods. NOTE: consider a project walker to hit all pages (compile) for your
web apps.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************

 

[Tommy]

It is the connection itself that is the problem. The page loads very fast.
Its when i click my button and hit the db to get some data that the problem
arises.

The db server is not the same as my development box. I do see that when I
run the code on the same box as the db it is very fast.

I have tried the machine name as well as the ip address in my connect string
and get the same results.

Thanks.

 

[Tommy]

Also, here is my connection string if it helps.
"Server=myserver;Database=mydatabase;uid=myid;pwd=mypass;"

 

[Lucas Tam]

It is the connection itself that is the problem. The page loads very
fast. Its when i click my button and hit the db to get some data that
the problem arises.

I'm having the same problem as well... I know .NET uses connection pooling,
so maybe the connection pools need to be reinitialized if the connections
have been idle too long?

I haven't figured out why it takes so long relatively speaking to
reconnect.

 

[Tommy Martin]

I thought about that. I created a windows forms app and used the same
technique to connect and it does not show that problem. The connection is
instantanious as it should be.

Only ASP.NET apps are having this problem.

Tommy

 

[Mark Rae]

I thought about that. I created a windows forms app and used the same
technique to connect and it does not show that problem. The connection is
instantanious as it should be.

Only ASP.NET apps are having this problem.

Hmm - I wonder if it's credentials. When you're using your WinForms app,
you're actually logged on to the machine as yourself but ASP.NET of course
is using its own network login (I don't mean the credentials in the SQL
Connection string).

As a test, can you modify the Web.config temporarily to impersonate yourself
and see if that makes any difference?

 

[Tommy Martin]

Right after <system.web> i added:

<identity impersonate="true"/>

It made no difference.

Tommy

 

[Mark Rae]

Right after <system.web> i added:

<identity impersonate="true"/>

It made no difference.

Yes, but which user did you tell it to impersonate...?

 

[Tommy]

Ok.. i told it to use my name and pass and the connection is pretty fast
again. First one is 1 to 2 secs. I can live with that.

This did not used to happen. I wonder if there is some config setting on my
server that needs to be changed or something else in my web.config. I am
using forms authentication in my app.

Thanks for the help.