4 Replies Latest reply on Oct 13, 2017 12:28 AM by Gerald Venzl-Oracle

    First feedback on performance

    Franck Pachot

      Hi,

      Here is my first feedback on performance when calling a JavaScript UDF from SQL, compared with PL/SQL: it is incredibly fast. Seems that there is no context switch overhead, and datatype conversion performance is fine. This is impressive Faster than PL/SQL inline, or even than PL/SQL compiled as UDF.

      I've posted all that on my blog: https://blog.dbi-services.com/oracle-database-multilingual-engine-mle/

      Regards,

      Franck.

        • 1. Re: First feedback on performance
          Laurent Daynes-Oracle

          Hi Frank,

            thanks a lot for the blog.  The whole MLE Oracle Labs team in Zurich has been reading your blog today and we were impressed by the level of details we found.  GCD was one of the example we used in some of our microbenchmark

          althought we used an iterative implementation and your findings match what we observed.

          We worked very hard to make the context switch as efficient as possible while maintaining characteristisc one found in pl/sql. We still miss a number of feature that we are actively adding to the beta and that might slow down this, but at the same time we still have a few additional opportunity for optimizing the context switch.

           

          You are right that we haven't updated all of the dictionary views -- this is one among many of the details that we need to polish out.

           

          Regarding your comment on dbjs: the transpilation is mostly for generating the PL/SQL code that creates the call specification required for the exported JavaScript functions. The JavaScript code is stored as is in the database dictionary and retrieved on demand at execution time before being executed using the GraalVM.

           

          Best regards, and thanks for taking the time to play with Oracle MLE.

           

          Laurent

          • 2. Re: First feedback on performance
            gsalem-Oracle

            Hi all,

            I also did some tests to see how this thing behaves, with SQL. Here's the code I used

            var sql = require("@oracle/sql");

             

             

            module.exports.getcount = function getcount(tablename) {

                for (let row of sql.execute(

                    "SELECT count(*) FROM "+tablename).rows) {

                    return row[0] ;

                }

                return 0;

            };

             

            So, it basically get's an object (table/view) name, and counts the rows in it.

            works very well, except that I have no idea about privileges: what privileges are enforced when a JS function executes a SQL statement? same behavior as PL/SQL?

            When compared to a PL/SQL function doing the same thing, PL/SQL seems faster:

            14:05:25 SQL> select sum(getc(table_name))

              2*  from user_tables, xmltable('1 to 100000');

             

            SUM(GETC(TABLE_NAME))

            ---------------------

                        702300000

             

            Elapsed: 00:00:14.439

            14:05:46 SQL>

             

             

            14:05:50 SQL> select sum(getcount(table_name))

              2  from user_tables, xmltable('1 to 100000');

             

            SUM(GETCOUNT(TABLE_NAME))

            -------------------------

                            702300000

             

            Elapsed: 00:00:21.066

            (getcount is the JS version). The tests lets each engine execute the sql (select count(0) from ....) 3M times, as the schema has got 3 tables.

             

            In all cases, I find this MLE very promising. Eagerly awaiting the Python version (am no JS fan)

             

            Regards

            • 3. Re: First feedback on performance
              Franck Pachot

              >> When compared to a PL/SQL function doing the same thing, PL/SQL seems faster:

              Yes, I've seen the same thing. But not easy to compare: PL/SQL caches the cursor, but with MLE you have a parse call for each call. Even when it is from session cursor cache, it is more expensive.

              • 4. Re: First feedback on performance
                Gerald Venzl-Oracle

                Taking a deep close look at the performance comparison between PL/SQL and MLE is premature in this first beta. There are still a lot of integration points missing for MLE that give it in some cases a clear performance benefit and in others a clear downside. Although MLE can offer a lot of benefits going forward this beta version is yet to soon to draw conclusions on performance numbers. What we would like testers to focus on at this stage is the integration between writing JS functions and execute them in SQL statements and the capabilities of loading third party packages from npm and using those directly within your SQL. Some of the questions we want users to take a look at, are:

                 

                • Is it easy to write JS functions and use them in SELECT, WHERE clauses, sub selects, INSERTs, etc.
                • Is it easy to write JS functions with more sophisticated parameters (more than one, different data types, etc)
                • Is it easy to write SQL within JS functions to get and put data from and into the database
                • Is it convenient to load npm packages, also when they require other npm packages
                • How easy is it to load multiple npm packages in your JS function
                • Do you think you would be allowed loading third party open source packages into a production database in the first place?
                • Do you like the way how you have to declare your functions today, what can be done better, etc.

                 

                The list goes on but just to give everybody an idea.

                We really appreciate all the testing efforts and what has been done already and certainly those are already very good insights. However, as said the focus on this beta version should not be put on purely performance as the performance numbers will change going forward.

                 

                We will post some guidelines which will include some of the above soon!

                 

                Thanks everybody!