Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 763
  • Last Modified:

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

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
recruitit
Asked:
recruitit
1 Solution
 
David ToddSenior DBACommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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