2020-12-11

SQL json_object VS PL SQL json_object_t

Comparing performance for simple json generation in Oracle 12.2
SQL’s json_object performs 1.5 times faster.
Result

SQL json_object: 41
PL/SQL JSON_OBJECT_T: 64

Test case

DECLARE
   l_result CLOB;
  
   l_cnt NUMBER := 10000;
   l_start_time NUMBER;
   
   l_key VARCHAR2(100) := 'testkey';
   l_val VARCHAR2(32767) := lpad('x', 5000, 'x');
   
   FUNCTION simple_json(p_name IN VARCHAR2, p_value IN CLOB) RETURN CLOB IS
      l_result JSON_OBJECT_T := JSON_OBJECT_T;
   BEGIN
      l_result.put(p_name, p_value);
      RETURN l_result.to_clob;
   END;
BEGIN
   l_start_time := dbms_utility.get_time;
   FOR i IN 1 .. l_cnt LOOP
      l_val := lpad('x', 5000, 'x') || systimestamp;
      SELECT json_object(key l_key VALUE l_val RETURNING CLOB) json INTO l_result FROM dual;  
      --dbms_output.put_line(l_result);     
   END LOOP;
   dbms_output.put_line('SQL json_object: ' || (dbms_utility.get_time - l_start_time)); 

   l_start_time := dbms_utility.get_time;
   FOR i IN 1 .. l_cnt LOOP
      l_val := lpad('x', 5000, 'x') || systimestamp;
      l_result := simple_json(l_key, l_val);
      --dbms_output.put_line(l_result);     
   END LOOP;
   dbms_output.put_line('PL/SQL JSON_OBJECT_T: ' || (dbms_utility.get_time - l_start_time)); 
END;

Комментариев нет: