Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-27
24
Medium Priority
?
300 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 80 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 143

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 120 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
 
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 200 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 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