Binary dumping using a non-primary index

[TristanB]

I have been experimenting on timings on a binary dump.

I've managed to dump a 1.5G/15 million record table in 2 hours using the primary index.

However if I dump using one of the other indexes i can get the dump down to 10 minutes!!

Question: Does it make a difference what index you use for a binary dump?

Details: OE 10.1CSP3 on Win2K3 32Bit

 

[TomBascom]

Yes, it makes a difference.

Using the smallest index is generally fastest.

But there is a trade-off -- the data will be loaded in the order of whatever index you used to dump with (because it was dumped in that order).

This may be important if your dominant access pattern uses a different order.

On the bright side most primary indexes are not very well thought through and are probably not the ideal from an ordering perspective in the first place. (The "bright side" of that is that you probably aren't screwing up things too much because they were probably already screwed up...)

Also, if records get created in a different logical order than the dump & load order the table will become "logically scattered" as it grows. One way that you might see this is that in some future d&l the first portion of the dump will go really fast (because the logical and physical orders match and everything is nice and tightly packed and sequential) and then it will seemingly come to a crawl as the data suddenly becomes scattered and the dump process now has to jump all over the place to find it. (You may also see similar behavior in large queries.)