ODC Appreciation Day 2018: ORDS AutoREST Performance Improvements from ORDS 3.0.9 to ORDS 18.3.0

von Robert Marz (Kommentare: 0)

In addition to using Oracle REST Data Services (ORDS) as the Apex-Listener, I'm using the AutoREST-Feature of ORDS quite heavily. Take a look at this ODTUG CodeTalk: REST Database Links: Access Oracle Databases in The Cloud Using ORDS, REST, and JSON, if you want to know more... Oracle is actively developing ORDS: There is a new release about every quarter of the year. Let aside the new features, the overall performance of the product is being improved continuously.

#ThanksODC: was the opportunity for me to invest some time and meter the real gains.

I decided to compare the following ORDS Versions:

  • 3.0.9 - Released December 19, 2016
  • 3.0.12 - Released September 29, 2017
  • 17.4.1 - Released December 19, 2017
  • 18.3.0 - Released October 3, 2018

These Releases represent a total of 1.75 years of development. I picked randomly two Releases between 3.0.9 and 18.3.0.

I was first surprised and then impressed by the results... Have a look at these Charts:

ORDS Performance Fetching 10k rows, Bulk Limit 25

ORDS Performance Fetching 10k rows, Bulk Limit 100

ORDS Performance Fetching 10k rows, Bulk Limit 500

ORDS Performance Fetching 10k rows, Bulk Limit 1.000

ORDS Performance Fetching 10k rows, Bulk Limit 5.000

ORDS Performance Fetching 10k rows, Bulk Limit 10.000

That is is a gain of 500% ( from 34 down to 7 seconds at a Bulk Limit of 10.000) performance with no changes other than migrating to the latest release or Oracle ORDS.

As a side effect, I learned, that ORDS had a hard limit on the limit-parameter: 500 rows have been the maximum for quite a while. This limitation vanished sometime between the versions ORDS-17.4.1 and 18.3.0.

I found an OVA of an old OTN Developer VM from March 2017 rusting on my Harddisk. It had ORDS 3.0.9 pre-installed. So, I decided to use that as a starting point for my testing.

After the initial setup, the only thing I had to do was to upgrade ORDS to the next version, which is straightforward.

I generated a table with some test data using Quick SQL (see below) and RESTenabled it.

For the test itself, I wrote a small Bash script:

#!/bin/bash

url=${1:-'http://localhost:8080/ords/perftest/perftest/?offset=0&limit=100'}

while [[ $url =~ http ]]
do
  echo URL: $url
  url=$(curl --request GET   --url $url|jq '.links[]|select (.rel=="next").href')
  url=${url//\"}
done

echo Elapsed seconds: $SECONDS

This script uses curl for the actual REST calls and jq to extract the "next" URL of the following batch of rows.

That's it - I was ready to rumble...

Test setup

Given you have an Oracle Database available with ORDS already installed, choose any schema and REST enable it:

begin
  ords.enable_schema;
  commit;
end;

I used Quick SQL to generate my Testdata. Quick SQL is a very easy to use Markdown like language, that generates DDL and Data. Goto this URL and give it a shot

This short piece of code

perftest /rest /insert 500
  txt01 vc127 /nn
  txt02 vc128
  datim date

generates this script with all DDL and 500 unique INSERT statements of random data:

-- create tables
create table perftest (
    id                             number not null constraint perftest_id_pk primary key,
    txt01                          varchar2(127) not null,
    txt02                          varchar2(128),
    datim                          date
)
;

-- triggers
create or replace trigger perftest_biu
    before insert or update 
    on perftest
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end perftest_biu;
/

-- REST ENABLE tables using Oracle REST Data Services (ORDS)
begin
    ords.enable_object(p_enabled=>TRUE, p_object=>'perftest');
end;
/

-- load data
insert into perftest (
    id,
    txt01,
    txt02,
    datim
) values (
    159447544209327081123296011458475460801,
    'Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque.',
    'Massa pharetra, id mattis risus rhoncus.  Cras vulputate porttitor ligula. Nam semper diam suscipit elementum sodales. Proin sit',
    sysdate - 60
);
-- ...
-- followed by 499 more rows with random data

commit;
-- load data
-- Generated by Quick SQL Thursday October 4, 2018  12:55:14
/*
perftest /rest /insert 500
  txt01 vc127 /nn
  txt02 vc128
  datim date

# settings = { PK: "TRIG", language: "EN" }
*/

This blog post is part of #ThanksODC: A thank you to all the people who help others via the Oracle Tech Net/Oracle Developer Community. If you want to read more articles which will be announced throughout the day, follow #ThanksODC on Twitter.

Zurück

Einen Kommentar schreiben