Slow connection from Excel VBA to PostgreSQL database
<p>I have a view in a PostgreSQL database. Executing the view in pgAdmin is very fast (10,000 records). But executing "Select * From myView;" from VBA is VERY slow. I connect to the database using ADO and ODBC. Can anyone give me a hint on how to speed up things?</p>

<p>A small example:</p>

<pre><code>Sub TEST()

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset

Dim Data   As Variant
Dim SQL    As String
Dim ConStr As String

ConStr = "Server=11.22.33.44;" &amp; _
         "DSN=PostgreSQL35W 32bit;" &amp; _
         "UID=xxx;" &amp; _
         "PWD=xxx;" &amp; _
         "Database=xxx;" _ &amp;
         "Port=5432;" &amp; _
         "CommandTimeout=12"

SQL = "Select * From myView;"

Debug.Print Now()

CN.ConnectionString = ConStr
CN.Open
Debug.Print Now()

RS.ActiveConnection = CN
RS.Source = SQL
RS.CursorType = adOpenStatic
RS.LockType = adLockReadOnly
Debug.Print Now()

RS.Open
Debug.Print Now()

Data = RS.GetRows
Debug.Print Now()

RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing

End Sub
</code></pre>

<p>This gives output:</p>

<pre><code>10/08/2016 16:14:26 
10/08/2016 16:14:26
10/08/2016 16:14:26
10/08/2016 16:14:38
10/08/2016 16:18:50
</code></pre>

<p>That is "RS.Open" takes 00:00:12, and "Data = RS.GetRows" 00:04:12.
In pgAdmin it takes less than a second to show all 10,000 records.</p>