Interesting fact was uncovered during our enterprise deployment project, and I wanted to share it.
The client has Oracle SAP that they are using across the globe, and we were supplementing user profile properties that have very limited information in AD through BDC to Oracle. Import through BDC was very slow, I mean VERY slow.
If AD full import was taking 1 1/2 hour to import 70,000 user profiles, then BDC part would take around 20+ hours. with this numbers, there was no way for us to meet SLAs.
After Oracle team, Microsoft team, and us started investigating the issue, some interesting things were uncovered.
Apparently in our ADF we were using simple select statement, to oracle side our “identifier” or record ID (ex:“win_no”) field was being passed from SharePoint as a numeric field. In the world I know, this is how you treat record ID.
But on the Oracle side this field was defined as a character. In this scenario, Oracle disabled the use of the index because the field types do not match. With the index disabled the process of retrieving records was slow.
The issue was solved by the “to_char” we put in select statement to make sure that BDC “does not lose anything in the translation”. Final testing showed that BDC import was brought down from 20+ hours to under 2.
When I was at NY User Group meeting in June, I have heard someone saying that their BDC import was taking about 8 hours to finish, and for a while, I thought that it was normal. Well… It is, if your field types do not match! :-)
Enjoy
No comments:
Post a Comment