5 Replies Latest reply: Jun 11, 2012 7:45 AM by veejai24 RSS

    Database lookup option in SOA 11g

    Sridhar-SOA
      Hi,


      We need to call a database table ( look up table) for getting some information based on some search criteria , with in a SOA 11g BPEL component.
      We are considering the following options , but at the same time see some cons too.

      1.DB Adapter - select operation
      Cons : Not SOAP but making a DB call every time is costlier option. Looking for some caching support.

      2. Create an ADF-BC / SDO service on top of table and access it using ADF-BC service in SOA. Now access this using Entity variables in SOA.
      This would not create SOAP bindings at the same time we have EO Cache support which would further help increase in performance.

      Do you see any flaw in this understanding ?

      In Approach 2 above , I see an issue. Would EO caching retain for more than one instance ? As far as I know in ADF BC - EO cache works for each user session. So does that mean that EO cache is refreshed for another instance of SOA ?

      Any other ways to do caching apart from ADF BC ?

      Regards,
      Sridhar.
        • 1. Re: Database lookup option in SOA 11g
          veejai24
          I would recommand to use Database Functions available in XSLT to query or to do look up on tables. This will reduce your maintenance of code and less hit on performance.

          http://docs.oracle.com/cd/E23943_01/dev.1111/e10224/bp_appx_functs.htm

          Thanks,
          Vijay
          • 2. Re: Database lookup option in SOA 11g
            Sridhar-SOA
            @veejai24- How would xslt xpath functions improve the performance. Can you share me the oracle doc on the performance improvement because of this ?
            • 3. Re: Database lookup option in SOA 11g
              veejai24
              what i meant is that XSLT functions and the DBAdapter would use the same datasource that you have configured in the server, there is no need to create a new connection to the datasource. As you know the database interaction is one of the costliest interms of performance.

              Thanks,
              Vijay
              • 4. Re: Database lookup option in SOA 11g
                Sridhar-SOA
                Thanks for your time . But I still couldn't make out the performance enhancement that XSLT function would provide over DBAdapter as both are using the JDBC behhind the scenes.
                Any other ways to do a DB call by introducing caching ?
                • 5. Re: Database lookup option in SOA 11g
                  veejai24
                  If you are using DB Adapter, you have to define your web service, with assoicated schema file, in runtime the server will spent time to make sure your message is pertaining to the schema and the service is exposed as web service, in times you will configure the JCA adapter service with performance configurations.

                  You need to do the above along with database performance tuning, when you use DBAdapter.

                  But if you use the XSLT database functions it will be using only the database performance tuning only.

                  Thanks,
                  Vijay