Slow initial connection to SQL Server 2008/2012

Posted on 2012-09-06
Last Modified: 2012-09-07
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.
Question by:tselectro
    LVL 142

    Accepted Solution

    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

    Author Comment

    Thank you!

    Author Closing Comment

    Thank you!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now