finding a replacement to the Collection class

[dilettante]

The best way to proceed is to profile your code which will help you discover where your performance problems actualy are. There are many good code profilers around, and some of them come in trial versions that work on small projects.


Collections might well be your performance bottleneck, especially if you are using them as you suggest above:

I am using Collection objects mainly to convert keys to indexes.
All the collections I use (exept one) are a mean to get an index in an array based on the name of something.
(or a double index sometimes)

A Collection isn't intended as a hash index to an external data structure, it is a data structure that contains its own hash index. In other words the object of a search or keyed access is what you are supposed to store in each Collection item, not something like a number to be used to index something else.

I'd think this extra indirection may be what is eating your performance.


Without more specific knowledge of what you are trying to accomplish it is very hard to suggest a specific remedy. It feels as if you are trying to model something like a Collection with multi-values items by using a 2D array and indexing it through a set of single-valued Collections.

There are two standard approaches for doing this. One is to use a single Collection where each item value is a Variant array. This has the limitation of offering just one automatic hash index however.

A more flexible alternative is to use a fabricated ADO 2.5+ Recordset. Here you can add an automatic hash index for any Field simply by setting that Field's "Optimize" ADO dynamic property to True.


In either case the result will typically provide plenty of performance. This performance will often outstrip hand crafted solutions using array sorting and binary searches, which have the disadvantages of being bug-prone and clumsy to maintain as well. This doesn't mean you can't do better with hand coding, but the cost/benefit almost always weighs against it - especially when changes are required.

An ADO solution also offers a host of additional features such as serialization and persistence: you can save and load the data trivially.


The hard part of this is retrofitting it into an existing program that has been written against the "world is flat (arrays)" programming model. However by learning how to use the ADO object model as an internal datastore you can apply the approach in future programs and see an increase in your development productivity.


I'd start with code profiling though. There is no point in guessing where you need to optimize. Have you looked at anything like VB Watch v2 before?

[Ellis Dee]

An ADO solution also offers a host of additional features such as serialization and persistence: you can save and load the data trivially.

Arrays are also trivially easy to save and load.