Solved

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

Posted on 2008-10-27
24
278 Views
Last Modified: 2013-11-26
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

        Try

            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

                .Connection.Open()

 

                ' Execute SQL

                intRows = .ExecuteNonQuery()

            End With

 

        Catch

            Throw

 

        Finally

            ' Close the connection

            With cmd.Connection

                If .State = ConnectionState.Open Then

                    .Close()

                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)

            .Connection.Open()

 

            .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

 

        Try

            ' Create New DataAdapter

            da = New SqlDataAdapter(SQL, ConnectionString)

 

            ' Fill DataSet from DataAdapter

            ds = New DataSet()

            da.Fill(ds)

 

        Catch

            Throw

 

        End Try

 

        Return ds

    End Function

Open in new window

Untitled-1.gif
Untitled-2.gif
0
Comment
Question by:16spam16
  • 14
  • 4
  • 4
  • +1
24 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 20 total points
ID: 22815250
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...
0
 
LVL 1

Author Comment

by:16spam16
ID: 22815374
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 192.168.254.71
and i assume the port for SQLS is 1433
0
 
LVL 1

Author Comment

by:16spam16
ID: 22815419
I used the following CS:

Private Const ConnectionString As String = "UID=myuser;PWD=password;Network Library=DBMSSOCN;Data Source=192.168.254.71,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...
0
 
LVL 20

Expert Comment

by:brwwiggins
ID: 22815444
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22815461
maybe on the client the tcp ip protocol is not enabled?
0
 
LVL 1

Author Comment

by:16spam16
ID: 22815462
brwwiggins,
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.
0
 
LVL 20

Expert Comment

by:brwwiggins
ID: 22815572
this site will help explain the website precompilation
http://msdn.microsoft.com/en-us/library/399f057w(VS.80).aspx

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 !!
0
 
LVL 1

Author Comment

by:16spam16
ID: 22815607
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.
0
 
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 30 total points
ID: 22815684
try this
Private Const ConnectionString As String = "User ID=myuser;Password=password;Data Source=192.168.254.71\SQLEXPRESS;Database=MyDB;"
0
 
LVL 1

Author Comment

by:16spam16
ID: 22815695
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 ==========
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22815740
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
0
 
LVL 1

Author Comment

by:16spam16
ID: 22815857
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Comment

by:16spam16
ID: 22815949
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?

0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22815956
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
0
 
LVL 20

Expert Comment

by:brwwiggins
ID: 22816000
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
0
 
LVL 1

Author Comment

by:16spam16
ID: 22816003
Here are my screenshots.....
i gotta be misunderstanding something.
Untitled-1.gif
Untitled-2.gif
0
 
LVL 1

Author Comment

by:16spam16
ID: 22816024
maybe i have a website, and not a Web Application, hence why my right-click shows "convert to Web application"?
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22818938
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
0
 
LVL 1

Author Comment

by:16spam16
ID: 22818990
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
0
 
LVL 1

Author Comment

by:16spam16
ID: 22819024
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
0
 
LVL 1

Author Comment

by:16spam16
ID: 22828138
Does anyone have any idea how to precompile a website that is the root?
i.e.
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.

0
 
LVL 20

Accepted Solution

by:
brwwiggins earned 50 total points
ID: 22833934
did you try aspnet_compiler -p C:\Mysite -v /

see last section here http://msdn.microsoft.com/en-us/library/ms227972.aspx
0
 
LVL 1

Author Comment

by:16spam16
ID: 22834754
I thought I did...but i dont recall if i put a -v at the end, ill try it again.
0
 
LVL 1

Author Closing Comment

by:16spam16
ID: 31510431
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now