First feedback on performance — oracle-tech

    Forum Stats

  • 3,702,305 Users
  • 2,239,629 Discussions
  • 7,835,898 Comments

Discussions

First feedback on performance

Franck PachotFranck Pachot Posts: 912 Bronze Trophy
edited October 2017 in Multilingual Engine

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.

Harshad Kasture-OracleFranck Pachot

Comments

  • edited October 2017

    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

    Harshad Kasture-OracleFranck Pachot
  • gsalem-Oraclegsalem-Oracle Posts: 143 Employee
    edited October 2017

    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

  • Franck PachotFranck Pachot Posts: 912 Bronze Trophy
    edited October 2017

    >> 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.

  • Gerald Venzl-OracleGerald Venzl-Oracle Posts: 85 Employee
    edited October 2017

    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!

    Franck Pachot
Sign In or Register to comment.