Wer als (APEX-)Entwickler heutzutage Web-Applikationen erstellt, stößt immer häufiger auf Anforderungen der Art „Integration von Daten einer fremden Quelle“.

Also beispielsweise die Integration von Wetterdaten eines Wetterservices. Während vor einigen Jahren überwiegend XML als Format für derartige Datenaustausche verwendet wurde, gehen aktuelle Entwicklungen immer mehr dazu über an dieser Stelle JSON-Strukturen zu verwenden.

Vor allem in etwas älteren APEX-Applikationen kann das mitunter zu Performance-Problemen führen.

In Oracle Datenbank Version 11 gab es etwa noch gar keine native Unterstützung für die Verarbeitung von JSON-Daten – weder in PL/SQL noch in SQL.

Die erste Möglichkeit überhaupt war das Package APEX_JSON, das mit APEX 5.0 eingeführt wurde und mangels Alternativen recht weite Verbreitung fand.

Erst mit 12.1.0.2 begann die Unterstützung in SQL mit der Einführung von JSON_TABLE, mit 12.2 in PL/SQL durch die Einführung der Types JSON_ELEMENT_T, JSON_OBJECT_T etc.

Seither werden diese Funktionalitäten laufend verbessert und erweitert, so dass es sich je nach konkretem Anwendungsfall also durchaus lohnen kann, früher implementierte JSON-Verarbeitung mittels APEX_JSON zumindest zu hinterfragen und bei Bedarf durch native Funktionalitäten abzulösen.

Ein kleiner Test soll die Unterschiede in der Performance aufzeigen:

Vorbereitungen

Um keine Test-Daten generieren zu müssen, verwenden wir an dieser Stelle einen Service des US Geological Survey (USGS): den Erdbeben-Katalog (API Dokumentation: https://earthquake.usgs.gov/fdsnws/event/1/ ). Ein kleines Script erzeugt eine Tabelle für die Testdaten und befüllt diese auch gleich mit einem CLOB mit 20.000 Einträgen im JSON-Format:

CREATE TABLE test_json (id NUMBER GENERATED ALWAYS AS IDENTITY,
data CLOB);
INSERT INTO test_json (data)
VALUES (apex_web_service.make_rest_request(p_url => 'https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&limit=20000',
p_http_method => 'GET'));
SELECT t1.id,
dbms_lob.getlength(t1.data)
FROM test_json t1;

ID DBMS_LOB.GETLENGTH(DATA) 
--- -------------------------
1 15727921

Zur Verdeutlichung der Performance-Unterschiede reicht folgende Anforderung aus: Wir wollen ermitteln wie viele Elemente/Erdbeben das Array FEATURES innerhalb der JSON-Daten enthält (auch wenn wir aufgrund der Parameter des Webservice-Calls bereits erahnen können, dass die Antwort 20.000 lauten wird).

Bei der Verwendung von APEX_JSON gehen wir wie folgt vor:

DECLARE
v_count NUMBER;
v_json_data CLOB;
v_time TIMESTAMP;
BEGIN
SELECT t1.data
INTO v_json_data
FROM test_json t1
WHERE t1.id = 1;

v_time := SYSTIMESTAMP;
apex_json.parse(p_source => v_json_data);
dbms_output.put_line('Parsing Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));

v_time := SYSTIMESTAMP;
v_count := apex_json.get_count(p_path => 'features' );
dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Counting Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Parsing Time: 23,200156
Feature Count: 20000
Counting Time: 0,000012

Für eine Applikation, die „nur mal eben live ausgeben soll“, wie viele Erdbeben es in einem bestimmten Zeitraum gegeben hat, sind 23 Sekunden zu viel.

Sehen wir uns daher die Verwendung neuerer Funktionalitäten in PL/SQL an:

DECLARE
v_count NUMBER;
v_json_data CLOB;
v_json_object json_object_t;
v_time TIMESTAMP;
BEGIN
SELECT t1.data
INTO v_json_data
FROM test_json t1
WHERE t1.id = 1;

v_time := SYSTIMESTAMP;
v_json_object := json_object_t.parse(jsn => v_json_data);
dbms_output.put_line('Parsing Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));

v_time := SYSTIMESTAMP;
v_count := v_json_object.get_array('features').get_size;
dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Counting Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Parsing Time: 6,513685
Feature Count: 20000
Counting Time: 0,000067

Das ist schon deutlich schneller als in der Variante mit APEX_JSON – für moderne Applikationen aber sind 6sec für vermeintlich einfache Datenabfragen zu lange.

Daher versuchen wir nun die Anzahl primär per SQL zu ermitteln:

DECLARE
v_count NUMBER;
v_time TIMESTAMP;
BEGIN
v_time := SYSTIMESTAMP;

SELECT COUNT(*)
INTO v_count
FROM test_json t1, JSON_TABLE(data, '$.features[*]' COLUMNS (row_number FOR ORDINALITY,
mag NUMBER PATH '$.properties.mag',
eq_id VARCHAR2(30) PATH '$.id')) AS t2
WHERE t1.id = 1;

dbms_output.put_line('Feature Count: ' || v_count);
dbms_output.put_line('Total Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_time)));
END;

Feature Count: 20000
Total Time: 0,165338

Obwohl hier alles in einem einzigen Schritt ausgeführt wird, braucht die gesamte Abarbeitung der Anfrage nur mehr knappe 0,2sec und ist damit um einen Faktor deutlich jenseits der 100 schneller als die APEX_JSON-Implementierung.

Wer also größere JSON-Objekte in 12.2+ noch mit APEX_JSON verarbeitet, sollte dringend einmal über eine kleine Frischzellenkur nachdenken…