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

Slow initial connection to SQL Server 2008/2012

We have some connection trouble to SQL Server 2008 R2 and same problem has also appeared on SQL Server 2012. The problem is that connection to the db is very slow. We programming db code in a vba editor in Citect SCADA 7.20 and have also testing the code in VB6 and Excel VBA. The same result overall.

Code ex.

sConnection = "Provider=sqloledb;Network=DBMSSOCN;Data Source=" & sDataSource & ";Initial Catalog=TSElectro;User ID=citect;Password=citect;Persist Security Info = False;Connect Timeout=15;"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

‘This operation is very slow, almost 3000ms
conn.Open sConnection

‘This operation (very easy and few records), about 300ms
rs.Open sSQL, conn, adOpenDynamic, adLockOptimistic

However, it is not only “my code” that is slow. SQL Server Management Studio is also slow. When browsing (expanding…) is showing about 3 second. After right click the table and do a first time “SELECT” the operation is very slow. Executing the query afterwards works fine and browsing the db is also fast. The “mystic” also affect my vba code. When the Management Studio is fast, the vba codes is also fast and execute my code in few ms. If I close the Server Management the vba code executing very slow again.
0
tselectro
Asked:
tselectro
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check if the database is eventually has it's "auto_close" property set to yes.
if that is the case, please change to no, it should work better then
0
 
tselectroAuthor Commented:
Thank you!
0
 
tselectroAuthor Commented:
Thank you!
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.

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