Topic
  • 6 replies
  • Latest Post - ‏2015-02-17T15:00:06Z by BLAIS
BLAIS
BLAIS
20 Posts

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
    1211 Posts
    ACCEPTED ANSWER

    Re: Loading data from a database asynchronously

    ‏2013-10-01T18:43:20Z  
    • BLAIS
    • ‏2013-10-01T13:21:46Z

    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?

    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

    ‏2015-02-17T15:00:06Z  

    Hi,

     

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

     

    regards

    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
  • AlexFleischer
    AlexFleischer
    1211 Posts

    Re: Loading data from a database asynchronously

    ‏2013-10-01T06:57:20Z  

    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

    Re: Loading data from a database asynchronously

    ‏2013-10-01T13:21:46Z  

    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

    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
    1211 Posts

    Re: Loading data from a database asynchronously

    ‏2013-10-01T18:43:20Z  
    • BLAIS
    • ‏2013-10-01T13:21:46Z

    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?

    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

    Re: Loading data from a database asynchronously

    ‏2013-10-01T19:21:27Z  

    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

    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
    1211 Posts

    Re: Loading data from a database asynchronously

    ‏2013-10-01T21:20:05Z  
    • BLAIS
    • ‏2013-10-01T19:21:27Z

    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?

    Hi,

     

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

     

    regards

  • BLAIS
    BLAIS
    20 Posts

    Re: Loading data from a database asynchronously

    ‏2015-02-17T15:00:06Z  

    Hi,

     

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

     

    regards

    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