Need a more efficient way to convert JDBC resultset to a JSON array

Question

Currently I have the following code that converts my resultset to a JSONObject and then appended to a JSONArry, but the the processing time is way too long for large data sets. I am looking for an alternative way or libraries to improve my current computing time.

Currently it takes 16 to 17 seconds to process 14k records from Oracle DB

            int total_rows = rs.getMetaData().getColumnCount();

            while (rs.next()) {
                JSONObject obj = new JSONObject();
                for (int i = 0; i < total_rows; i++) {

                    obj.put(rs.getMetaData().getColumnLabel(i + 1)
                            .toLowerCase(), rs.getObject(i + 1));

                }
                jsonArray.put(obj);

            }

Adjust your fetch size... what's "too long" and what's "large data sets" and what database? — Elliott Frisch, Jun 26 '19 at 20:18
Consider reading the metadata just once, and storing the downcased results in an array or List. But even with that, you're doing I/O (to the database) and I/O is one of the slowest thing you can do. In particular, unless you have comparatively few results, the initial query that produces your `ResultSet` very likely does not transfer all of the result rows. As you step through, new rows will periodically need to be fetched from the DB. — John Bollinger, Jun 26 '19 at 20:23
@ElliottFrisch it takes 16 seconds to process 14k records from Oracle DB — Chris_Z, Jun 26 '19 at 20:31
@a_horse_with_no_name the db is untouchable I can't make any changes to it — Chris_Z, Jun 26 '19 at 20:34
I didn't mean to change the DB, but to write a SQL statement that would generate the JSON directly in the database. But that's pretty complicated in Oracle. — , Jun 26 '19 at 20:35
You might want to stream the json objects directly instead of marshaling to an array first. But without more code, it's hard to say. — Elliott Frisch, Jun 26 '19 at 20:50

Francisco Sitja · Accepted Answer · 2019-06-27T19:20:33.013

If you are using Oracle 12cR2 you can utilize Oracle supplied functions (JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG and so on) to generate JSON directly from the database, ready to read. It will be substantially faster and easier to code.

You have not posted additional details about your data model so we can help with something more specific, but do feel free to explore the documentation below.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html

I created one quick example below to show a bit about how it works:

FSITJA@db01 2019-06-26 14:15:02> select json_object('name'         value username,
  2                     'default_ts'   value default_tablespace,
  3                     'temp_ts'      value temporary_tablespace,
  4                     'common'       value case when common = 'Y' then 'YES' else 'NO' end,
  5                     'oracle_maint' value case when oracle_maintained = 'Y' then 'YES' else 'NO' end
  6                     format json) as json_obj
  7    from dba_users u
  8   where oracle_maintained = 'Y'
  9         and rownum <= 5;

JSON_OBJ
-----------------------------------------------------------------------------------------------------------
{"name":"SYS","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}
{"name":"SYSTEM","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}
{"name":"GSMCATUSER","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}
{"name":"XS$NULL","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}
{"name":"MDDATA","default_ts":"SYSTEM","temp_ts":"TEMP","common":"NO","oracle_maint":"YES"}

This would've solved my problem but we are using Oracle 12.1 :( — Chris_Z, Jun 27 '19 at 14:32
That's a bummer. In this case although it will be more trouble recreating the logic, I would probably try and write a PL/SQL stored procedure to do it, it will at least save you the network roundtrips of data. But performance will still hinge on the efficiency of the algorith used. Not a simple task to code it, but I would suggest you try that route. — Francisco Sitja, Jun 27 '19 at 19:27

score 1 · Answer 2 · answered Jun 26 '19 at 20:31

1

I'd do the following test: F := the time it takes to fetch from the database; J := time it takes to serialize JSON from pre-fetched. I suspect that your F >> J, which would indicate that what you need is not a different JSON lib, but a faster read from the database. This may or may not be soluble. However, if F << J, consider parallelizing your serialization to take advantage of extra cores, when available.

answered Jun 26 '19 at 20:31

Igor Urisman

717
1
6
22

Unfortunately, the read is surprisingly fast, it took around one second to read but 16 seconds to serialize. – Chris_Z Jun 26 '19 at 20:43
This is surprising. Jackson is a widely used and solid JSON library. I use it indirectly and haven't had perf problems, but I haven't run into very long arrays. I suppose it's possible that the underlying array implementation requires a full list traversal in order to append, but I don't know. Parallelization will certainly work, and shouldn't take too much extra code. – Igor Urisman Jun 26 '19 at 21:10

Need a more efficient way to convert JDBC resultset to a JSON array

2 Answers2