1

I've written an interface and backend in VBA Excel, which interacts with a Firebird database via the Firebird ODBC driver. I had the database running locally, and speed wasn't an issue. I've recently ported it over to a server, which I access over a VPN. The problem is it's very slow.

Granted originally my code was not efficient so I did streamline it. This query returns 1600 rows, in 6 columns. Total runtime is 5 seconds. Should it be taking this long for a query and subsequent paste into a spreadsheet?

mytimer = Timer

Dim conn2 As ADODB.Connection
Dim box As Variant
Set rst2 = New ADODB.Recordset


Set conn2 = New ADODB.Connection
conn2.Open "SOSA"
sql_string = "SELECT * FROM MACHINES"
rst2.Open sql_string, conn2, adOpenDynamic, adLockOptimistic
box = rst2.GetRows
box = Application.Transpose(box)

Dim NumRows As Long
Dim NumCols As Long
NumRows = UBound(box, 1) - LBound(box, 1) + 1
NumCols = UBound(box, 2) - LBound(box, 2) + 1
Range("A2").Resize(NumRows, NumCols).value = box


conn2.Close
MsgBox ((Timer - mytimer))

A side question, I Also I have some code which takes all the cells in a worksheet and INSERTS into a table, and I loop through each row and generate an SQL insert, and execute it, and then next row. Needless to say this is painfully slow. Is there a better way ?

user3328740
  • 33
  • 4
  • 1
    How long does executing and fully fetching the query take from a different tool (say isql or flamerobin) from the same computer to the same db? Also are any of the columns blobs? – Mark Rotteveel Feb 19 '14 at 18:31
  • Just tried it in Flamerobin and it's 0.784 seconds. Reasonable. Tried it immediately after in excel, and it was now 1.5 seconds which is also reasonable. However tried it 3-4 more times, and the times ranged from 1.5-3 seconds. Very inconsistent. It looks like it is a network/firewall problem. When I run tracert some of the times are as high as 300ms. However the SQL insert method that I'm using is still too slow. Is there a better way ? – user3328740 Feb 19 '14 at 20:21
  • 1
    You could use a parametrized (prepared) query and set and execute for each set of parameters, instead of generating a SQL query for each row. – Mark Rotteveel Feb 19 '14 at 20:29
  • I've been searching high and low for how to perform that, can you point me to a link ? – user3328740 Feb 25 '14 at 17:40
  • 1
    See for example: http://support.microsoft.com/kb/181734 or http://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters – Mark Rotteveel Feb 25 '14 at 17:47
  • Would like to let everyone know that because the program worked over a VPN the ping times were very large, 200-300 ms. Which means that even parameterized queries were not running quickly enough. The solution was to do a block execute "EXECUTE BLOCK AS begin ((FULL INSERT STATEMENT) times as many as you need) END". The only problem is there is a character limitation of about 58k. So you have to modify your block to be no longer than that. This brought down my 621 inserts to 5 seconds. Orders of magnitude better than before. – user3328740 Apr 02 '14 at 17:58

0 Answers0