Why does my .net web application SQL Server connection take so long the first time.

I have a .net 2.0 web application with a SQL Server 2005 Express backend.
Why would the connection to the database the first time take so long (about 10 seconds)?  
My inital page does like 6 different SQL statements all pretty simple.
Then once i am in my application it seems to be BLAZING fast, and I am doing some much larger more complex SQLs once in the app.  

I have provide 2 screenshots of my SQL server DB setup.
along with my connection string, and connection code.
and how i execute it.

All my SQLs are in-line not stored. (I wont be switching)

I have already switched Auto Close to False, but that didnt really make a difference.

I am fresh out of ideas.

Any help would be great.

Please let me know if there is any addtl. info you need to assist.

Thank you in advance.
~~~~~~Connection String~~~~~~~
Private Const ConnectionString As String = "User ID=myuser;Password=password;Data Source=SERVER\SQLEXPRESS;Database=MyDB;"
~~~~~~~~This is how i call the below procs (the sample is for the datareader)~~~~~~~~~
dr = DataHandlerSqlClient.GetDataReader(strSQL)
~~~~~~My 3 Connection Procs~~~~~~~
~~~~~~These are the only PROC's i have to execute SQL's(INSERT,UPDATE,DELETE,SELECT)~~~~~~~
   Public Shared Function ExecuteSQL(ByVal SQLStatement As String) As Integer
        Dim cmd As SqlCommand
        Dim intRows As Integer
        cmd = Nothing
            cmd = New SqlCommand()
            With cmd
                ' Create a Connection object
                .Connection = New SqlConnection(ConnectionString)
                ' Fill in command text, set type
                .CommandText = SQLStatement
                .CommandType = CommandType.Text
                ' Open the Connection
                ' Execute SQL
                intRows = .ExecuteNonQuery()
            End With
            ' Close the connection
            With cmd.Connection
                If .State = ConnectionState.Open Then
                End If
            End With
        End Try
        Return intRows
    End Function
    Public Shared Function GetDataReader(ByVal SQLStatement As String) As SqlDataReader
        Dim dr As SqlDataReader
        Dim cmd As New SqlCommand()
        With cmd
            .Connection = New SqlConnection(ConnectionString)
            .CommandText = SQLStatement
            dr = .ExecuteReader(CommandBehavior.CloseConnection)
        End With
        Return dr
    End Function
    Public Shared Function GetDataSet(ByVal SQL As String) As DataSet
        Dim da As SqlDataAdapter
        Dim ds As DataSet
            ' Create New DataAdapter
            da = New SqlDataAdapter(SQL, ConnectionString)
            ' Fill DataSet from DataAdapter
            ds = New DataSet()
        End Try
        Return ds
    End Function

Open in new window

Who is Participating?
brwwigginsConnect With a Mentor IT ManagerCommented:
did you try aspnet_compiler -p C:\Mysite -v /

see last section here http://msdn.microsoft.com/en-us/library/ms227972.aspx
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you are using the sql server name \instance name.... could you try to use the ip address (+ port number) instead?
the last time I have seen a slow initial connection on a database that was not auto-close, it was  the dns name resolution being slow...
16spam16Author Commented:
how does my connection string change with the use of IP

Private Const ConnectionString As String = "User ID=myuser;Password=password;Data Source=SERVER\SQLEXPRESS;Database=MyDB;"

the ip is
and i assume the port for SQLS is 1433
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

16spam16Author Commented:
I used the following CS:

Private Const ConnectionString As String = "UID=myuser;PWD=password;Network Library=DBMSSOCN;Data Source=,1433;database=MyDB;"

and I got this error:

"An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"

I double checked my DB settings and i do allow remote connections.
i also have all SQL protocols(4 of them i think) enabled.

I am sure I am missing something...
brwwigginsIT ManagerCommented:
If you don't pre-compile the application then the server must compile it the first time it is called (or after the worker process has been recycled).

This may be the delay you are seeing and nothing to do with your SQL statements
Guy Hengel [angelIII / a3]Billing EngineerCommented:
maybe on the client the tcp ip protocol is not enabled?
16spam16Author Commented:
maybe you are right...
i am new to both .net and SQL Server.

pardon my ignorance, but how do you "pre-compile the application"

that would make sense, based on what I am seeing.
brwwigginsIT ManagerCommented:
this site will help explain the website precompilation

I'm not sure if you are using the full blown VS or the express version. The full blown has pre-compile options if you go to Build->Publish Website. You can also pre-compile a site via the command line. This document explains it http://msdn.microsoft.com/en-us/library/ms227976(VS.85).aspx

hope this helps !!
16spam16Author Commented:
I have the VS2008 i go into Build->Publish Website but i dont see any options to pre-compile
i could do it via cmd, but that is a hassle, esp if they have an easy way to do it.
Anurag ThakurConnect With a Mentor Technical ManagerCommented:
try this
Private Const ConnectionString As String = "User ID=myuser;Password=password;Data Source=\SQLEXPRESS;Database=MyDB;"
16spam16Author Commented:
i currently use the "Publish" feature of VS2008, i didnt know it compiled it.

------ Build started: Project: MySite, Configuration: Debug Any CPU ------
MySite -> C:\MySite\bin\MySite.dll
------ Publish started: Project: MySite, Configuration: Debug Any CPU ------
Connecting to C:\MYSITE\MySite\...
Publishing folder /...
Publishing folder App_Code...
Publishing folder App_Data...
Publishing folder css...
Publishing folder help...
Publishing folder image...
Publishing folder images...
Publishing folder images/loading...
Publishing folder images/other...
Publishing folder bin...
Publishing folder bin/zh-CHT...
Publishing folder bin/zh-CHS...
Publishing folder bin/tr-TR...
Publishing folder bin/ru...
Publishing folder bin/pt...
Publishing folder bin/nl...
Publishing folder bin/ko...
Publishing folder bin/ja...
Publishing folder bin/it...
Publishing folder bin/hi...
Publishing folder bin/he...
Publishing folder bin/fr...
Publishing folder bin/es...
Publishing folder bin/de...
Publishing folder bin/cs...
Publishing folder bin/ar...
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Publish: 1 succeeded, 0 failed, 0 skipped ==========
Anurag ThakurTechnical ManagerCommented:
thats one of the ways to compile it as each and every pages code behind will be converted to an assembly
now configure the website and try if everything is working for you as desired or not
16spam16Author Commented:
the MSDN article speaks of this setting as part of the "Publish" function in VS2008
"Allow this precompiled site to be updatable option"

i cant seem to find that?

so maybe my site isnt being pre-compiled, just copied.
16spam16Author Commented:
the database calls seem to run faster using the IP, which tells me i may have a DNS issue.

as for the precompile issue, like i said i do use the Publish function in VS, but i also tried to compile it via cmd on my production server, and i got an error saying /MySite was not a valid IIS application.

I ran this locally and it worked. The difference is locally it is  Virtual Folder, on my Production server it is a Website (i have 4 listed in my IIS)
how does that change my cmd statement?

this worked locally...but not on my other server where "Mysite" is the Website, not a Virtual Folder
c:\windows\microsoft.net\framework\v2.0.50727\ aspnet_compiler -v Mysite

any ideas?

Anurag ThakurTechnical ManagerCommented:
when you right click on you website and select publish web site you will see three check boxes
the first one is the option you are looking for
brwwigginsIT ManagerCommented:
when you created the site in Visual Studio did you do File->New Website or new project and select the web application project type?

Not sure if that makes a difference. Most of mine were upgrades from VS 2005 and imported them as websites instead of Web application projects

Maybe you could try to pre-compile via the command line once to see if it boosts performance. Then if so, look at why the options do not appear in VS
16spam16Author Commented:
Here are my screenshots.....
i gotta be misunderstanding something.
16spam16Author Commented:
maybe i have a website, and not a Web Application, hence why my right-click shows "convert to Web application"?
Anurag ThakurTechnical ManagerCommented:
just the give the path where you want your site files will be precompiled (can be http or ftp or disk)
give a disk path and when its published you can move your files from that directory to the place from you want your site to be published
16spam16Author Commented:
yeah i was looking around and it makes the *.compiles files in a temp directory under the .net system folder.(the first time i run the site)

I am going to try this method tomorrow and see if it works.

aspnet_compiler -v /<Name of the website> -p <Path of the project which needs to be compiled> <Path where we need to save the compiled version>

Essentially the site i want to compile is a Root site, not a virtual path/folder
16spam16Author Commented:
does it matter where the *.compiled files are located?
I guess whats the difference if i use the compiler utility to compile or i run the website the first time to get it to compile?
I guess error detection? but then again isnt that what "debuggin" your app is for?

What is the recommended sytax for my aspnet_compiler if i wanted to compile
website= MYSITE(it is a root, not a VD/VP)
file loc= c:\Mysite
16spam16Author Commented:
Does anyone have any idea how to precompile a website that is the root?
If my site was the localhost, how would i Compile that via the aspnet_compiler.exe utility?

It works fine if my site was found via localhost/MySite but not what my site is the localhost.

16spam16Author Commented:
I thought I did...but i dont recall if i put a -v at the end, ill try it again.
16spam16Author Commented:
Thank you guys (or gals) for all of your help.
Basically, the slower app speed seemed to be 2 issues, DNS across the network, and the pre-compile issue.
thank you all for the assistance.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.