emosms
02-19-2015, 10:07 AM
Hi, I am working on an excel/vba app and I am using ADODB and SQL queries within the excel tables extensively.
In general I get quite slow performance with one of the algorithms.
The algorighm is basically three nested loops.
---
The first loop reads a column of 50-300 values (depending on the input data), using plain VBA.
The second loop uses ADODB to grab a recordset from a table of 10-30 000 records. The resulting recordset may vary from 1 to lets say 50 rows.
The third loop again uses ADODB to grab a recordset from 100-200 records table, the resulting set varies 1 to 10 rows.
---
The performance is very bad. For 10 items in the first loop I might wait for 1-2 minutes. Trying to process 150 items resulted in more than 15 minutes waiting.
Shall I switch to pure vba for the two inner loops?
The second loop is a concern, since I might need to reed the whole table of 10-30 000 thousand recods to get what I need.
The values I am going to get are next to each other, the table is sorted by them, but still....
---
First Idea is to experiment and leave the connection open each time I call nested loop one and two queries.
Kind Regards
In general I get quite slow performance with one of the algorithms.
The algorighm is basically three nested loops.
---
The first loop reads a column of 50-300 values (depending on the input data), using plain VBA.
The second loop uses ADODB to grab a recordset from a table of 10-30 000 records. The resulting recordset may vary from 1 to lets say 50 rows.
The third loop again uses ADODB to grab a recordset from 100-200 records table, the resulting set varies 1 to 10 rows.
---
The performance is very bad. For 10 items in the first loop I might wait for 1-2 minutes. Trying to process 150 items resulted in more than 15 minutes waiting.
Shall I switch to pure vba for the two inner loops?
The second loop is a concern, since I might need to reed the whole table of 10-30 000 thousand recods to get what I need.
The values I am going to get are next to each other, the table is sorted by them, but still....
---
First Idea is to experiment and leave the connection open each time I call nested loop one and two queries.
Kind Regards