We are integrating systems that contain a lot of products, customers, and companies. The ids for these items are not the same on different systems and so we will be implementing a cross reference table to do the mappings. However, there are potentially tens of thousands of product codes and a lot of transactions taking place all the time. I believe that this could lead to the cross reference table becoming a serious bottleneck in the integration framework.
Does anyone have any suggestions/ways of working that will either reduce the number of rows on the cross ref tables (while still including all of the product, company, and employee codes) or increase the efficiency of the cross reference process? Alternatively, has anyone used this process in large projects and found it to be perfectly ok?
First advice: don’t do performance tuning until you know you have performance problems.
Second advice: Do performance testing to find out if there are performance bottlenecks before you go into production
Third advice: Fix the performance bottleneck now that you know what performance to tune
I know these are all obvious advice, but I had to throw them in.
Now to talk about cross-reference tables. The performance will most likely depend on how you configure your integration system. Will the adapters that need to lookup cross-references reside on the same machine as the database with the x-ref tables?
A common architecture for 4.1.1 was to put resource adapters with the resources and the logic adapters (ATC) on the centralized broker machine which also had the x-ref database.
However, with the advent of the new “intelligent” adapters that combines the resource and logic adapters into one, the adapters tend to reside with the Resource system. The major drawback to this architecture is that the interaction with the x-ref tables is taken care of by the Utility adapter which resides on the centralized machine where the x-ref tables are. Every communication between the resource adapter and the utility adapter goes over the network. Bad idea.
One option to try is to have local databases with x-ref tables with on the resource machines. I have not tried this myself yet, but it should work. I know that the x-ref tables have columns to make it possible to put all the x-ref information on one database, but we can ignore that. If you go with this option you would have to stop using the utility adapter and configure your resource adapter with database connection information and build your own x-ref lookup calls. This is no mean task, but it is doable. The good-old ATC adapters were built this way and oh do I miss them.
One word of warning: The x-ref tables have been overloaded to support two separate features. One is the cross referencing of system ids. The other is preventing infinite loops, a feature known as latching or echo-suppression. Be very careful when using echo-suppression - the way they teach it in their class does not work and will cause data to be lost.