Richard and All,
The generic reason that multi-part file is faster than a single file is
the dividing of indexes on large files.
On a Multi-part file, there is one set of indexes files per part. When a
SELECT is invoked, it works on each index for each of the part files, then
joins them together for the result. I propose that you have an index that
does not have many index.keys, therefore the length of the index.string of
the index.key is HUGE.
I'll explain your situation backwards with a real world example of what we
found (customer was aghast when discovered.):
+ 12 M record file had an index that resolved to (yes, that's right) 12
(for the number of months in a year). The record key length averaged 6
characters.
Index.string length = 6 characters * 1M records = 6M bytes. Not
good.
+ Fixing the index to be more definitive, where less values would be
included, was deemed too hard to change given the pain the customer was
experiencing. They needed a quick fix.
Resolution:
+ Created a 10 multi-part file that was resolved by portions of the key.
At least the longest record length for any index was no longer than 600K
(6M / 10 multi-part files), which was still pretty bad. String searches
were shortened to 1/10th the previous time. At least it gave the staff
enough time to change the indexing scheme within the application.
Continuing on a different, but related topic, the longer the string, the
longer the searches. The longer the record, the more time it takes the UV
file system has to accommodate the large, lumpy, record. (Please refer to
Mark Baldridge's research papers Developer works).
And yes, you get multi-threading on selects.
Also, I will reiterate again, U2 Lab Services are not fans of SANs. The
reason is the defaults and assumptions can really mess up a U2 shop. I'm
not going to go there, for I don't have the time.
Thanks!
Steve
Stephen M. O'Neal
Lab Services Sales for U2
IBM SWG Information Management Lab Services
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
