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

x
?
Solved

Initial Connection to SQL Express database is...slow.

Posted on 2007-12-06
1
Medium Priority
?
758 Views
Last Modified: 2013-12-16
Just set up a SQL Express database using "Microsoft SQL Server Management Studio Express" on a dedicated machine on our network.

In my program, I am basically using a SqlConnection to connect to this database via...



SqlConnectionStringBuilder sqlconnbuild = new SqlConnectionStringBuilder();

            sqlconnbuild.DataSource = "xxx.xxx.xxx.xxx\\xxxxxxxx";  //E.G. 192.168.25.24\\DBServer
            sqlconnbuild.InitialCatalog = "DatabaseNameHere";              //For example
            sqlconnbuild.ConnectTimeout = 120;                                    //I needed to set this to prevent T/O

SqlConnection conn = new SqlConnection(sqlconnbuild.ToString());
SqlDataReader reader;

SqlCommand comm = new SqlCommand("Insert SQL SELECT statement here", conn);

try
{
     conn.Open();
     reader = comm.ExecuteReader();
     //Insert general code here
}
catch (SqlException e1)
{
     MessageBox.Show(e1.Message);
}
finally
{
conn.Close();
}


But here is what happens.

I run the program (F5), I type in my login details, and then wait approximately 25 seconds and then it finally finishes and allows me access into the program.

However

If I run the program (F5), and I type the wrong login details for example, wait again for approximately 25 seconds, and obvisouly I get a message saying that I typed my login incorrectly, but if immediately after I type my correct details, the connection opens and finishes like lightning probably about 0.1 of a second or lower.

Unfortionetly every the program restarts I get the same problem on the initial login, which obviously people are only ever going to log in once so its a big problem.

Any insight would be great, thanks!

(Also the database in SQLExpress has auto-close to false, I checked that one ;o))
0
Comment
Question by:recruitit
1 Comment
 
LVL 35

Accepted Solution

by:
David Todd earned 750 total points
ID: 20439249
Hi,

SQL Express set autoclose true for user databases.

Change this to false.

Cheers
  David

PS Running the script against model will make sure that all future databases don't auto_close. Otherwise change model to the name of your database.
alter database model
set auto_close off

Open in new window

0

Featured Post

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!

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

972 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