title: Slow lookup and data loading in Vertica
body: <p>I am evaluating Vertica 8.1. I am really happy with its performance at BI side. My reports are running 20 times faster. But it is very slow when it does lookup in the fact loading transformation and also when loading the dimension tables. More the lookup steps I have in a transformations, slower it gets. I have created a lot of projections (one for each column in the dimension tables). I am not using clustering feature of Vertica.</p>
  
  <p>My dimension table surrogate keys are integers. When loading the dimension table, I check the table maximum and add 1 to it in the next row. By any chance, is that a problem? Because, the lookup and data loading with Pentaho is like 30ish rows per second. To load the dimensions, I am using <code>Dimension lookup/update</code> step for SCD Type 2, and to load the combination dimensions I am using <code>Combination lookup/update</code> step. I am loading the fact using <code>Vertica bulk loader</code>. I read the documentation on Vertica's website which was about connections and best practices using Pentaho with Vertica. But it didn't seem helpful. </p>
  
  <p>Suggestions?</p>