title: Alternative to running DB Lookup functoid through a loop?
body: <p>I'm really not sure how to phrase this, so here's my best shot-  I have a table which stores values by a 'TypeId' and the actual value.  The 'TypeId' maps to a row in a lookup table which houses the actual plain-text description.  When inserting data into my Database via BizTalk Server 2009, I need to perform a lookup on each value provided from the incoming file to get its correct TypeId, and then populate the original table.</p>
  
  <p>The only way I see to do that is with the DB Lookup functoid, but as this function may be called literally hundreds of times per input file, I don't look forward to the performance hit I'd take from that.  Does anyone know of another way to perform that lookup at run time, or perhaps a way do the lookup from SQL just once, and then have BizTalk refer to some in-memory representation of the table?</p>
  
  <p>EDIT:  I did have an idea, but would like someone in the SO community to tell me if it's a bad one and, if so, why.  Here it is:</p>
  
  <p>I could take the incoming document and convert straight to XML, then feed the resulting XMLDocument to an external .NET Assembly.  That assembly could then pull in the lookup table as a Dictionary (or some other type of IEnumerable) and modify the original document by adding in the correct TypeId values in place of the original type descriptor in the original document.  This would, in theory, allow me to avoid the overhead of a large number of DB Lookups by substituting the (theoretically) much lower overhead of calling an external assembly.</p>
  
  <p>Can anyone think of a reason <i>not</i> to use this approach?</p>