Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SQL Server Administration (2008)  Problem with delay after idle connection

Author  Topic  benseb Starting Member 3 Posts Posted - 2009-09-20 : 07:14:48 Hi I'm developing an application using VB.NET (C#) on Windows Mobile 5 We have successfully created the app, which connects to a SQL Server 2008 Standaard database. This works fine when in constant use. However, if the mobile device is left for longer than 10 minutes, when I next call mycon.open() it takes approx 10-20 seconds to open the database connection. The app is still fully awake and responsive, it just hangs when trying to run the first query. To give more background info, up to 8 mobile devices will be in use at any one time connecting to the same SQL server. I have contemplated the following: 1) Wifi is disconnecting: I've disabled all power saving and can successfully ping the server after 10 mins so don't think this is the issue 2) Database is auto closing: autoClose is set to FALSE and confirmed this. 3) Name lookup for hostname in connection string is causing delay - but would it be 10 secs plus? 4) Connection pool is getting closed: most probable. At the moment I can't eliminate 3) as when I change my connection string to using an IP address it wont connect so need more work here. 4) Seems most probable, and I'd like to try something like 'min pool size=1' on my connection string, but I can't seem to do this in windows compact framework (invalid parameter) My conn strings go like this: public string dataString = "rapidscan\\rapidscan;Initial Catalog=rapidscanclub;User ID=scanner;Password=12345"; public bool openDatabase() { try { myConnection = new SqlConnection(); myConnection.ConnectionString = "Data Source = " + dataString; myConnection.Open(); return true; } catch (SqlException e) { MessageBox.Show("Error opening local database!\n" + e.Message, "Database Error"); return false; } public bool closeDatabase() { myConnection.Close(); return true; } And each query does this: bool ok; //MessageBox.Show("STARTING"); ok = this.openDatabase(); // MessageBox.Show("CONNECTED"); if (!ok) { return null; } cmd = new SqlCommand("SELECT name,redeemed,redeemedTime FROM tickets WHERE barcode='" + strBarcode + "'", myConnection); myReader = cmd.ExecuteReader (CommandBehavior.SingleRow); I can confirm the delay occurs between 'STARTING' and 'CONNECTED' above. Because my database functions are within a seperate class, each time I do a query, I'm effectively creating a new 'data' class instance, creating a new connection and opening it, then closing it and destroying it after. Does anyone have any ideas how I can eliminate the delay, either by stopping the connection pool from closing all connections or leaving them open longer? Remember this is using SqlConnection on Windows Mobile so I'm not sure all connection params are available? Thanks Ben