BizTalk - References Data (Xref) Caching

Most resources on the topic of Biztalk reference data states that the Xref 'Get Value' functoids will cache values after the first DB call which in turn reduces the number of database round trips - but does it really?
Well for the most part yes but when the adapter, and in my case SFTP, submits a batch to the messagebox well then a call is made for every single message (During map execution) in that batch. Running a profile against the Management database revealed that the below Stored procedure was executed for every map execution in my first batch. 
exec dbo.xref_GetCommonValue @valueXRef=N'XXXXXX',@appType=N'XX',@appValue=N'XXXX',@commonValue=@p4 output,@valueAndAppExist=@p5 output
select @p4, @p5
The next batch did however make use of the cached values as the stored procedure was not executed again. So there you have it; the Xref Get Common Value functoid, cached values, will only be availble after the batch has completed and not after the first execution of the functoid in the map. Just a little somethng worth considering when hitting your Biztalk application with a large batch as the very first transaction which in my case was 10 000 messages.
Written by Nigel Sheldon at 00:00

Categories :



Comments closed