Archives of the TeradataForum
Message Posted: Wed, 03 May 2006 @ 22:09:16 GMT
| Subj: | | Re: Hits per DB |
| |
| From: | | John Graas |
| | Is the below formula right in terms of atleast the line of thought? | |
| | No of hits per DB = (No of source rows for multiload / total rows in the target table) * (datablock size/rowsize) | |
Pretty much correct, assuming even distribution -- based on the primary index (PI) -- of the rows being loaded into the target table.
| | What should be the optimum value of this parameter to achieve good performance using the bulkload utilities? | |
With MLoad, the more hits per DB, the better.
For break even, rule-of-thumb is that an average one hit per data block will pretty much match TPump speeds (V2R5). Anything above this will
be faster than TPump; anything lower will be slower than TPump. Note: using ARRAY SUPPORT for TPump under V2R6.x, TPump speeds have increased,
so this will increase the "break even" ratio closer to 1.5 hits per DB.
There are a few things that can help the DB hit rate:
1) Loads where there are multiple hits per PI. All inserts/updates with the same NUPI will be isolated to the same DB(s). TPump
with ARRAY SUPPORT will also take advantage of this.
2) Loads where the Partition Value corresponds to the load data. For example, daily loads where the PPI is date will only hit
blocks in the corresponding partition. Again, TPump with ARRAY SUPPORT will take advantage of this.
jdg
www.jgraas.com
| 
