Solved

Connection.Open takes over 7 sec to execute.

Posted on 2008-10-21
8
184 Views
Last Modified: 2012-05-05
.Connection.Open() takes about 5 seconds to execute.  Not sure why.

I am using SQL Server Express 2005 and my SQL statement is small(under 5 fields).

My SQL Server is local, but i have tested on a network SQL Server and it takes just as long.  I would have thought this would be almost instant.

Because of the time issue it inturn makes my website run sluggish.

Am I better off using OLEDB? Or does it not matter. (see code below)

My IIS is 6.0
I am running.Net 2.0/3.5
Coding in VS 2008

Could it be something in my web.config?
my connection string
"UID=MySite;PWD=MySite;server=WORK5\SQLEXPRESS;database=MySite;"
 
 
    Public Shared Function GetDataReader(ByVal SQLStatement As String, ByVal ConnectionString 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
 
 
 
am I better off using OLEDB? Or does it not matter
 
    Public Shared Function GetDataReader(ByVal SQLStatement As String, ByVal ConnectionString As String) As OleDbDataReader
 
        Dim dr As OleDbDataReader
        Dim cmd As New OleDbCommand
 
        With cmd
            .Connection = New OleDbConnection(ConnectionString) '
            .Connection.Open()
            .CommandText = SQLStatement
            dr = .ExecuteReader(CommandBehavior.CloseConnection)
        End With
        Return dr
    End Function

Open in new window

0
Comment
Question by:16spam16
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 1

Author Comment

by:16spam16
ID: 22767133
I'd like to add this is only happens the first time i run my application
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22767295
>>Am I better off using OLEDB?

No, the SqlConnection will give you better performance than the OleDBConnection.

If you go out to a command prompt and PING WORKS5, how quickly do you get a response?  I'm wondering about a name resolution problem.
0
 
LVL 1

Author Comment

by:16spam16
ID: 22767335
(4 times) Reply from 192.168.254.71: bytes=32 time<1ms TTL=128

Ping stats
Packets: sent=4, received=4, lost=0

Roundtrip
Min=0ms, max=0ms,avg=0ms
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 75 total points
ID: 22767464
OK, it seems that the SQL Server Express is going to sleep. Related links:
0
 
LVL 1

Author Comment

by:16spam16
ID: 22767553
pardon my ignorance, but I am fairly new to SQL Server (formly a Access guy) where in SQL can I find the Auto Close /Sleep setting?

That makes sense...b/c it funs like crap the first time, then it is blazing fast after that.  Then if i log in 5 min later it is slow again.
0
 
LVL 1

Author Comment

by:16spam16
ID: 22767592
When i open SQL Management Studio
and I right my Database and goto Properties
I see an "Auto Close" setting set to True  is that what I am looking for?

or is it a global SQL setting not a Database specific setting?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22767684
>>I see an "Auto Close" setting set to True  is that what I am looking for?

Yeah, you found it!
0
 
LVL 1

Author Closing Comment

by:16spam16
ID: 31508260
amazing what a simple boolean will do to your application.  thank you
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

738 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