Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
6 replies Latest Post - ‏2015-02-17T15:00:06Z by BLAIS
BLAIS
BLAIS
20 Posts
ACCEPTED ANSWER

Pinned topic Loading data from a database asynchronously

‏2013-09-30T19:40:36Z |

I am loading a lot of data from different query through an Oracle database. I realise that all those query are load sequentially.

So imagine that I have the query A, B and C that take respectively 1 minute, 2 minutes and 1 minute. Normally, the Oracle server can answer to all of those three queries in 2 minutes since all those query can be run in parallel. When I use OPL, those three queries take 4 minutes because OPL run it sequentially.

Is there a way to inform OPL to run some query in parallel?

  • AlexFleischer
    AlexFleischer
    1126 Posts
    ACCEPTED ANSWER

    Re: Loading data from a database asynchronously

    ‏2013-10-01T06:57:20Z  in response to BLAIS

    Hi,

     

    what you could do is an external java call to call 3 different opl runs in // that would read the database. Is that an option ?

    (Like in opl\examples\opl_interfaces\java\ConcurrentProcessing)

     

    regards

    • BLAIS
      BLAIS
      20 Posts
      ACCEPTED ANSWER

      Re: Loading data from a database asynchronously

      ‏2013-10-01T13:21:46Z  in response to AlexFleischer

      This that mean that I have to code my own DBREAD function in Java using JDBC (I'm not sure about the library name since I don't code often in java) and run my model using the OPL Java interface?

      • AlexFleischer
        AlexFleischer
        1126 Posts
        ACCEPTED ANSWER

        Re: Loading data from a database asynchronously

        ‏2013-10-01T18:43:20Z  in response to BLAIS

        HI,

         

        you can use dbread but you would call the .mod and .dat with some java in the external java call. But indeed you need some java to do the multi thread call.

        Or another option would be to write only one dbread into a single tuple set and then derive from this tuple set result the three you need.

        Regards

        • BLAIS
          BLAIS
          20 Posts
          ACCEPTED ANSWER

          Re: Loading data from a database asynchronously

          ‏2013-10-01T19:21:27Z  in response to AlexFleischer

          Thank you for your answer. The second option you mention is not usable for me because my tuplesets didn't have the same type.  For the first option, I will try to implement that in C# .NET.

          Is there an easy way to pass a .NET list of tuple through an OPL tupleset?

          • AlexFleischer
            AlexFleischer
            1126 Posts
            ACCEPTED ANSWER

            Re: Loading data from a database asynchronously

            ‏2013-10-01T21:20:05Z  in response to BLAIS

            Hi,

             

            you have some examples in CPLEX_Studio\opl\examples\opl_interfaces\dotnet

             

            regards

            • BLAIS
              BLAIS
              20 Posts
              ACCEPTED ANSWER

              Re: Loading data from a database asynchronously

              ‏2015-02-17T15:00:06Z  in response to AlexFleischer

              We finally implement a .NET code that parse the .dat file (that contain database query) and create a new .dat file (text file). We use this text file in our OPL model.  The performance gains are great.

              In OPL the data are loaded in 57 seconds. Using our sequential .NET loader didn't improve the loading time. It takes 55 seconds. Doing it asynchronously in .NET with 5 threads improve the load time by 67%. It takes only 18 seconds. What a gain for our clients considering that data loading is longer than optimization time. 

              OPL dblink:                        57 sec.

              .NET sequential:              55 sec.

              .NET async:                          18 sec.

               

              For async dbload in OPL, you can found two RFE:

              http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=61896
               

              http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=43762
               

              Updated on 2015-03-27T19:56:10Z at 2015-03-27T19:56:10Z by BLAIS