ADODB & Jet Engine VS Pure VBA - Performance

[emosms]

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

[Bob Phillips]

If the first VBA loop is slow, why are you suggesting changing the second to VBA (and you don't say whether the second is slow or not).

It also depends what happens in the loop, whether the loop can be dispensed with. More info is needed to help there.

Best way is to try all loops as many ways as you can, time them all, and see which is faster.

[emosms]

What I found up til now is that the UPDATE statement to an Excel table is really slow.
(A named range/table in excel could be treated by the JET engine as DB table, except posibility to DELETE)
The trick with turning off and on the screen updating does not help at all. It is even more frustrating to wait unknown time and nothing shows it works onscreen :D.
---
In the second loop i sweep the 10-30k rows table to set certain values.
Actually, I don't need them all to do what I have to do, so I refined the SQL query to process less of the table.
The performance increased signifficantly thanks to the fact I don't need to process all.
---
But if needed, an UPDATE on 10k+ rows in an excel sheet, using ADODB/JET seems to be pretty poor.
UPDATE on 1k + rows is allready slow.
---
The topic is kind of open for anyone who experienced such problems and possibly found a solution.

Kind Regards

[emosms]

A sideway optimization still goes on.
Actually, I can get the values in the big table directly from the database with a join.
Excell is good to follow the dataflow step by step in tables, but better not insert and update :D.
Another excel table using ADODB/JET and insert could be taken directly from the database, with UNION of 3 queries
(never used SQL Union in a real case, so I completely forgot it.).
BTW, I tried to set values in a table with VBA instead of JET SQL Insert, but it does not seem to be faster.
On the other hand, using ms query table connected to database works pretty well and updates pretty fast.
Conclusion - use MS query for insert and take the data from external, real database.
---
If you need the data result set to be processed before displaying the result in a spreadsheet - How to achieve that from the excel VBA host?
Is it possible?
Regards