CODEWITHSUNDEEP
sql-serverexceloledb
sickless
sickless

Reputation: 71

Slow oledb connection - Excel <-> SQL Server

I have 2 SQL Servers and I'm using following connection string to open connection to them in Excel VBA:

  1. Provider=sqloledb;Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI
  2. Provider=SQLOLEDB;Data Source=myserver2,1433;Initial Catalog=mydb;User ID=login; Password=pass!

I need to pull data from SQL Server tables into an Excel worksheet, simple select *:

Dim sql As Variant

    Dim conn As New ADODB.Connection
    
    With conn
        .ConnectionTimeout = 120
        .Open SQL_CONN0
        strQuery = "select * from refGlbAccess"
        sql = SQL_ReturnArray(conn, strQuery, True)
        ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Resize(UBound(sql, 1), UBound(sql, 2)).Value = sql
    
        sql = SQL_ReturnArray(conn, strQuery, True)
        ThisWorkbook.Sheets("Sheet2").Cells(1, 1).Resize(UBound(sql, 1), UBound(sql, 2)).Value = sql
        .Close
    End With

I've made comparison of 2 execution times and noticed and conn.Open with 1st string and 1st server is taking 4.25 seconds and with 2nd string and 2nd server it's taking 0.6 seconds.

Is it possible I'm using the wrong provider to connect to the 1st server?

Can the connection open time depend this much on server location? 1st server is in Singapore and 2nd is in West Europe (I'm located in Moscow).

Is authentication method affecting connection open time?

EDIT 12.12:

I updated provider to msoledbsql and split execution code in different timings: enter image description here

Here's SQL return array function, it's also running longer than old server:

Public Function SQL_ReturnArray(ByVal conn As ADODB.Connection, ByVal strQuery As String, ByVal column_names As Boolean) As Variant

Dim rs As New ADODB.Recordset
Dim n_arr, r, c As Variant

With rs
    .Open strQuery, conn
    If rs.State = 0 Then
        ReDim n_arr(1 To 1, 1 To 1)
        n_arr(1, 1) = "Empty"
        SQL_ReturnArray = n_arr
        Exit Function
    End If
    If .EOF Then
        ReDim n_arr(1 To 1, 1 To 1)
        n_arr(1, 1) = "Empty"
        SQL_ReturnArray = n_arr
        Exit Function
    End If
    If Not InStr(strQuery, "SCOPE_IDENTITY()") > 0 Then .MoveFirst
    If column_names = False Then
        SQL_ReturnArray = Application.Transpose(rs.GetRows)
    Else
        n_arr = rs.GetRows
        m = UBound(n_arr, 1)
        n = UBound(n_arr, 2)
        ReDim Preserve n_arr(0 To m, 0 To n + 1)
        n_arr = Application.Transpose(n_arr)
        m = UBound(n_arr, 1)
        For iCols = 0 To rs.Fields.Count - 1
            n_arr(m, iCols + 1) = rs.Fields(iCols).Name
        Next
        SQL_ReturnArray = n_arr
    End If
    .Close
End With
    
Set rs = Nothing

End Function

Upvotes: 0

Views: 74

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

Can the connection open time depend this much on server location? 1st server is in Singapore and 2nd is in West Europe (I'm located in Moscow).

Yes. This depends on your network, in addition to the routes your traffic uses across the globe.

Is authentication method affecting connection open time?

Perhaps. NTLM can and Kerberos must involve communicating with a domain controller. So again, the network can come into play.

Upvotes: 0

Related Questions