]> BizTalk Database Lookup Functoids 🌐:aligrant.com

BizTalk Database Lookup Functoids

Alastair Grant | Friday 6 October 2006

I've been working on a BizTalk 2006 solution that utilises the Database Lookup Functoids in the BizTalk Mapper.

I use the Database Lookup functoid to define the connection etc. And then I use the Value Extractor about 10 times to get the individual values out of a view.

It wasn't working as I expected though, so I fired up SQL Profiler to have a ganders and was shocked at what I saw.

I saw about 10 hits to the database running a SELECT * FROM [myview] for each functoid!

Instead of doing a single select with the Database Lookup (as one would expect), it repeated the same query for each value extracted!

If I had written the same thing into my code manually, a DBA would have probably battered me into the ground. I'm wondering how long it'll go before somebody notices...

Breaking from the voyeuristic norms of the Internet, any comments can be made in private by contacting me.