Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-40118

JSON_EXTRACT / JSON_DETAILED is slow for large json documents

    XMLWordPrintable

Details

    • Bug
    • Status: Needs Feedback (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 11.4.9
    • None
    • JSON

    Description

      When calling append on the result string, the buffer is only grown by ~8 KB, so for large json documents the algorithm in json_nice has quadratic performance.

      Here is a small sql script that demonstrates this:

      SET @j10 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 10) - 1), '{"a":1,"b":2}]'));
      SET @j11 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 11) - 1), '{"a":1,"b":2}]'));
      SET @j12 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 12) - 1), '{"a":1,"b":2}]'));
      SET @j13 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 13) - 1), '{"a":1,"b":2}]'));
      SET @j14 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 14) - 1), '{"a":1,"b":2}]'));
      SET @j15 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 15) - 1), '{"a":1,"b":2}]'));
      SET @j16 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 16) - 1), '{"a":1,"b":2}]'));
      SET @j17 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 17) - 1), '{"a":1,"b":2}]'));
      SET @j18 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 18) - 1), '{"a":1,"b":2}]'));
      SET @j19 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 19) - 1), '{"a":1,"b":2}]'));
      SET @j20 = (SELECT CONCAT('[', REPEAT('{"a":1,"b":2},', POW(2, 20) - 1), '{"a":1,"b":2}]'));
       
      SET @t0 = NOW(6);
      DO JSON_PRETTY(@j10); SET @t10 = NOW(6);
      DO JSON_PRETTY(@j11); SET @t11 = NOW(6);
      DO JSON_PRETTY(@j12); SET @t12 = NOW(6);
      DO JSON_PRETTY(@j13); SET @t13 = NOW(6);
      DO JSON_PRETTY(@j14); SET @t14 = NOW(6);
      DO JSON_PRETTY(@j15); SET @t15 = NOW(6);
      DO JSON_PRETTY(@j16); SET @t16 = NOW(6);
      DO JSON_PRETTY(@j17); SET @t17 = NOW(6);
      DO JSON_PRETTY(@j18); SET @t18 = NOW(6);
      DO JSON_PRETTY(@j19); SET @t19 = NOW(6);
      DO JSON_PRETTY(@j20); SET @t20 = NOW(6);
       
      SELECT
          TIMESTAMPDIFF(MICROSECOND, @t0, @t10) AS `@t10`,
          TIMESTAMPDIFF(MICROSECOND, @t10, @t11) AS `@t11`,
          TIMESTAMPDIFF(MICROSECOND, @t11, @t12) AS `@t12`,
          TIMESTAMPDIFF(MICROSECOND, @t12, @t13) AS `@t13`,
          TIMESTAMPDIFF(MICROSECOND, @t13, @t14) AS `@t14`,
          TIMESTAMPDIFF(MICROSECOND, @t14, @t15) AS `@t15`,
          TIMESTAMPDIFF(MICROSECOND, @t15, @t16) AS `@t16`,
          TIMESTAMPDIFF(MICROSECOND, @t16, @t17) AS `@t17`,
          TIMESTAMPDIFF(MICROSECOND, @t17, @t18) AS `@t18`,
          TIMESTAMPDIFF(MICROSECOND, @t18, @t19) AS `@t19`,
          TIMESTAMPDIFF(MICROSECOND, @t19, @t20) AS `@t20`;
      

      Result:

      @t10 @t11 @t12 @t13 @t14 @t15 @t16 @t17 @t18 @t19 @t20
      7364 7283 9893 14772 38473 112343 289040 1070648 4102310 16556621 67111647

      Attached is a patch written by Claude, it manual grows the buffer by factor of 2 (not tested)

      Attachments

        Activity

          People

            shipjain Shipra Jain
            M393 Maximilian Krög
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.