Slow connection from Excel VBA to PostgreSQL database

Question

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?

A small example:

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;" & _
         "DSN=PostgreSQL35W 32bit;" & _
         "UID=xxx;" & _
         "PWD=xxx;" & _
         "Database=xxx;" _ &
         "Port=5432;" & _
         "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

This gives output:

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

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.

Do you see any improvements when using adOpenForwardOnly versus adOpenStatic ? — Jimmy Smith
– Jimmy Smith, Commented Aug 10, 2016 at 16:20
Also, when you use a DSN, it has to go to the registry before making the connection and it's slower. Check this out for some tips that may help — Jimmy Smith
– Jimmy Smith, Commented Aug 10, 2016 at 16:22
Try ASynch processing too. See: support.microsoft.com/en-ca/kb/190988 — Ryan Wildry
– Ryan Wildry, Commented Aug 10, 2016 at 23:04
Also try setting RS.CusorLocation = adUseClient, see if that helps. — Ryan Wildry
– Ryan Wildry, Commented Aug 10, 2016 at 23:12
All four combinations of adOpenStatic/adOpenForwardOnly and adUseServer/adUseClient give the same execution time. — Helge
– Helge, Commented Aug 11, 2016 at 8:57

Helge · Accepted Answer · 2016-08-11 10:31:49Z

2

I found out to use OLE DB. And it is FAST!
Downloaded PgOleDb: https://www.connectionstrings.com/pgoledb/info-and-download
Copied the two DLLs to C:\Windows\SysWOW64.
Ran "Regsvr32 PGOLEDB.DLL".
Connection string: https://www.connectionstrings.com/pgoledb

Provider=PostgreSQL OLE DB Provider;Data Source=myServerAddress;
location=myDataBase;User ID=myUsername;password=myPassword;

The command "timeout=1000;" does not function.

answered Aug 11, 2016 at 10:31

Helge

7722 gold badges6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Slow connection from Excel VBA to PostgreSQL database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related