Slow initial connection to SQL Server 2008/2012
Posted on 2012-09-06
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.
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
‘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.