[vb6] Project Scanner

[LaVolpe]

@LaVolpe: Does building in-memory index with rs.Fields(sSortField).Properties("Optimize").Value = True has any effect on rs.Sort = sSortField?

cheers,
</wqw>

It does, but just creating the index is a large speed hit on large recordsets. Additionally, if optimized, then the index seems to slow down new inserts; probably because the index needs to be modified/rebuilt. And here is the kicker. In a disconnected recordset, the optimized setting does not transfer to a clone, i.e., rs.Clone.

Edited: FYI. Setting Optimize on my tests recordset (500K records). It took nearly 12 seconds. However, searching afterwards was significantly improved using Find/Filter methods. I tried this method first, but was disappointed that the property didn't carry over to clones.

P.S. Don't want to set that before populating a large recordset

Without Optimize, inserting 500K records: 6 seconds
With Optimize set before inserting 500K records: 127 seconds

Update: And now I'll have to re-look at the Optimize property again. Creating a clone after the rs was optimized did not carry over the property. But using the clone for searching/filtering must use the optimized indexes too since there was significant improvement there too -- that's good news. Unfortunately, the bad news is that ADO is kicking out "Unspecified Error" when optimizing multiple fields and/or mixing in clones. That error breaks navigation (EOF & BOF become True while RecordCount=500K). The size of the recordset might be an issue also? Too many unknowns to confidently use the Optimize property.