• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

Connection.Open takes over 7 sec to execute.

.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
16spam16
Asked:
16spam16
  • 5
  • 3
1 Solution
 
16spam16Author Commented:
I'd like to add this is only happens the first time i run my application
0
 
Daniel WilsonCommented:
>>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
 
16spam16Author Commented:
(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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Daniel WilsonCommented:
OK, it seems that the SQL Server Express is going to sleep. Related links:
0
 
16spam16Author Commented:
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
 
16spam16Author Commented:
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
 
Daniel WilsonCommented:
>>I see an "Auto Close" setting set to True  is that what I am looking for?

Yeah, you found it!
0
 
16spam16Author Commented:
amazing what a simple boolean will do to your application.  thank you
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now