Solved

Connection.Open takes over 7 sec to execute.

Posted on 2008-10-21
8
178 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
  • 5
  • 3
8 Comments
 
LVL 1

Author Comment

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

Expert Comment

by:Daniel Wilson
Comment Utility
>>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
Comment Utility
(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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 75 total points
Comment Utility
OK, it seems that the SQL Server Express is going to sleep. Related links:
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:16spam16
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
amazing what a simple boolean will do to your application.  thank you
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction (All good things must come to an end (http://en.wikipedia.org/wiki/All_Good_Things...)) The original MySQL API (http://php.net/manual/en/book.mysql.php) has gone away, deprecated by PHP in Version 5.5, and removed from PHP in all curre…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

763 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

16 Experts available now in Live!

Get 1:1 Help Now