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

Improve performance of my_{time|date|datetime}_to_str()

Details

    Description

      The MySQL-8.0 code base has an interesting change improving number-to-string conversion:

      commit 8d10f2fff6bbdea7f436b868ebb5fd811defc68a
      		 
      Author: Knut Anders Hatlen <knut.hatlen@oracle.com>
      		 
      Date:   Thu Oct 10 13:55:07 2019 +0200
      		 
       
      		 
          Bug#30472888: IMPROVE THE PERFORMANCE OF INTEGER HANDLING IN THE TEXT PROTOCOL

      The idea is that the division and the remainder operations used to print zerofill numbers appeared to be slow.
      It's cheaper to have a 200-byte long string consisting of precomposed 2-digit substrings strings, e.g. "00", "01", ... "99", and copy a proper 2-digit substring from this string, by index:

      static const char two_digit_numbers[]=
      		 
      {
      		 
        "00010203040506070809"
      		 
        "10111213141516171819"
      		 
        "20212223242526272829"
      		 
        "30313233343536373839"
      		 
        "40414243444546474849"
      		 
        "50515253545556575859"
      		 
        "60616263646566676869"
      		 
        "70717273747576777879"
      		 
        "80818283848586878889"
      		 
        "90919293949596979899"
      		 
      };

      This function prints a 2-digit number:

      static inline char* fmt_number2(uint8 val, char *out)
      		 
      {
      		 
        const char *src= two_digit_numbers + val * 2;
      		 
        DBUG_ASSERT(val < 100);
      		 
        *out++= *src++;
      		 
        *out++= *src++;
      		 
        return out;
      		 
      }

      The performance is achieved by the fact that it prints two digits at a time.

      Let's reuse this optimization in the following functions:

      • my_date_to_str()
      • my_time_to_str()
      • my_datetime_to_str()
      • my_interval_DDhhmmssff_to_str()
      • my_timeval_to_str
      • my_mmssff_to_str() - an internal function used in the above public functions

      Benchmarks

      A preliminary patch to MariaDB (that uses this idea) indeed demonstrates a very impressing query time reduce - 10% to 38%, depending on the exact data type and the number of fractional digits.

      SELECT BENCHMARK(10*1000*1000,CONCAT(TIME'10:20:30'));


      10.5: 0.176 65ee216c35d843da7883d255219ab011e20340c2
      		 
      10.5: 0.167 (my_xxx_to_str() improvements)


      SELECT BENCHMARK(10*1000*1000,CONCAT(TIME'10:20:30.123456'));


      10.5: 0.288 65ee216c35d843da7883d255219ab011e20340c2
      		 
      10.5: 0.261 (my_xxx_to_str() improvements)


      SELECT BENCHMARK(10*1000*1000,CONCAT(DATE'2001-01-01'));


      10.5: 0.258 65ee216c35d843da7883d255219ab011e20340c2
      		 
      10.5: 0.225 (my_xxx_to_str() improvements)


      SELECT BENCHMARK(10*1000*1000,CONCAT(TIMESTAMP'2001-01-01 10:20:30'));


      10.5: 0.319 65ee216c35d843da7883d255219ab011e20340c2
      		 
      10.5: 0.268 (my_xxx_to_str() improvements)


      SELECT BENCHMARK(10*1000*1000,CONCAT(TIMESTAMP'2001-01-01 10:20:30.123456'));


      10.5: 0.438 65ee216c35d843da7883d255219ab011e20340c2
      		 
      10.5: 0.275 (my_xxx_to_str() improvements)

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Description The MySQL-8.0 code base has an interesting change improving number-to-string conversion:

          {noformat}
          commit 8d10f2fff6bbdea7f436b868ebb5fd811defc68a
          Author: Knut Anders Hatlen <knut.hatlen@oracle.com>
          Date: Thu Oct 10 13:55:07 2019 +0200

              Bug#30472888: IMPROVE THE PERFORMANCE OF INTEGER HANDLING IN THE TEXT PROTOCOL
          {noformat}

          The idea is that the division and the remainder operations used to print zerofill numbers appeared to be slow.
          It's cheaper to have a 200-byte long string consisting of precomposed 2-digit substrings strings, e.g. "00", "01", ... "99", and copy a proper 2-digit substring from this string, by index:

          {code:cpp}
          static const char two_digit_numbers[]=
          {
            "00010203040506070809"
            "10111213141516171819"
            "20212223242526272829"
            "30313233343536373839"
            "40414243444546474849"
            "50515253545556575859"
            "60616263646566676869"
            "70717273747576777879"
            "80818283848586878889"
            "90919293949596979899"
          };
          {code}

          This function prints a 2-digit number:
          {code:cpp}
          static inline char* fmt_number2(uint8 val, char *out)
          {
            const char *src= two_digit_numbers + val * 2;
            DBUG_ASSERT(val < 100);
            *out++= *src++;
            *out++= *src++;
            return out;
          }
          {code}


          Let's reuse this optimization in the following functions:
          - my_date_to_str()
          - my_time_to_str()
          - my_datetime_to_str()
          - my_interval_DDhhmmssff_to_str()
          - my_timeval_to_str
          - my_mmssff_to_str() - an internal function used in the above public functions

          		The MySQL-8.0 code base has an interesting change improving number-to-string conversion:

          {noformat}
          commit 8d10f2fff6bbdea7f436b868ebb5fd811defc68a
          Author: Knut Anders Hatlen <knut.hatlen@oracle.com>
          Date: Thu Oct 10 13:55:07 2019 +0200

              Bug#30472888: IMPROVE THE PERFORMANCE OF INTEGER HANDLING IN THE TEXT PROTOCOL
          {noformat}

          The idea is that the division and the remainder operations used to print zerofill numbers appeared to be slow.
          It's cheaper to have a 200-byte long string consisting of precomposed 2-digit substrings strings, e.g. "00", "01", ... "99", and copy a proper 2-digit substring from this string, by index:

          {code:cpp}
          static const char two_digit_numbers[]=
          {
            "00010203040506070809"
            "10111213141516171819"
            "20212223242526272829"
            "30313233343536373839"
            "40414243444546474849"
            "50515253545556575859"
            "60616263646566676869"
            "70717273747576777879"
            "80818283848586878889"
            "90919293949596979899"
          };
          {code}

          This function prints a 2-digit number:
          {code:cpp}
          static inline char* fmt_number2(uint8 val, char *out)
          {
            const char *src= two_digit_numbers + val * 2;
            DBUG_ASSERT(val < 100);
            *out++= *src++;
            *out++= *src++;
            return out;
          }
          {code}
          The performance is achieved by the fact that it prints two digits at a time.

          Let's reuse this optimization in the following functions:
          - my_date_to_str()
          - my_time_to_str()
          - my_datetime_to_str()
          - my_interval_DDhhmmssff_to_str()
          - my_timeval_to_str
          - my_mmssff_to_str() - an internal function used in the above public functions

          bar Alexander Barkov made changes -
          Description The MySQL-8.0 code base has an interesting change improving number-to-string conversion:

          {noformat}
          commit 8d10f2fff6bbdea7f436b868ebb5fd811defc68a
          Author: Knut Anders Hatlen <knut.hatlen@oracle.com>
          Date: Thu Oct 10 13:55:07 2019 +0200

              Bug#30472888: IMPROVE THE PERFORMANCE OF INTEGER HANDLING IN THE TEXT PROTOCOL
          {noformat}

          The idea is that the division and the remainder operations used to print zerofill numbers appeared to be slow.
          It's cheaper to have a 200-byte long string consisting of precomposed 2-digit substrings strings, e.g. "00", "01", ... "99", and copy a proper 2-digit substring from this string, by index:

          {code:cpp}
          static const char two_digit_numbers[]=
          {
            "00010203040506070809"
            "10111213141516171819"
            "20212223242526272829"
            "30313233343536373839"
            "40414243444546474849"
            "50515253545556575859"
            "60616263646566676869"
            "70717273747576777879"
            "80818283848586878889"
            "90919293949596979899"
          };
          {code}

          This function prints a 2-digit number:
          {code:cpp}
          static inline char* fmt_number2(uint8 val, char *out)
          {
            const char *src= two_digit_numbers + val * 2;
            DBUG_ASSERT(val < 100);
            *out++= *src++;
            *out++= *src++;
            return out;
          }
          {code}
          The performance is achieved by the fact that it prints two digits at a time.

          Let's reuse this optimization in the following functions:
          - my_date_to_str()
          - my_time_to_str()
          - my_datetime_to_str()
          - my_interval_DDhhmmssff_to_str()
          - my_timeval_to_str
          - my_mmssff_to_str() - an internal function used in the above public functions

          		The MySQL-8.0 code base has an interesting change improving number-to-string conversion:

          {noformat}
          commit 8d10f2fff6bbdea7f436b868ebb5fd811defc68a
          Author: Knut Anders Hatlen <knut.hatlen@oracle.com>
          Date: Thu Oct 10 13:55:07 2019 +0200

              Bug#30472888: IMPROVE THE PERFORMANCE OF INTEGER HANDLING IN THE TEXT PROTOCOL
          {noformat}

          The idea is that the division and the remainder operations used to print zerofill numbers appeared to be slow.
          It's cheaper to have a 200-byte long string consisting of precomposed 2-digit substrings strings, e.g. "00", "01", ... "99", and copy a proper 2-digit substring from this string, by index:

          {code:cpp}
          static const char two_digit_numbers[]=
          {
            "00010203040506070809"
            "10111213141516171819"
            "20212223242526272829"
            "30313233343536373839"
            "40414243444546474849"
            "50515253545556575859"
            "60616263646566676869"
            "70717273747576777879"
            "80818283848586878889"
            "90919293949596979899"
          };
          {code}

          This function prints a 2-digit number:
          {code:cpp}
          static inline char* fmt_number2(uint8 val, char *out)
          {
            const char *src= two_digit_numbers + val * 2;
            DBUG_ASSERT(val < 100);
            *out++= *src++;
            *out++= *src++;
            return out;
          }
          {code}
          The performance is achieved by the fact that it prints two digits at a time.

          Let's reuse this optimization in the following functions:
          - my_date_to_str()
          - my_time_to_str()
          - my_datetime_to_str()
          - my_interval_DDhhmmssff_to_str()
          - my_timeval_to_str
          - my_mmssff_to_str() - an internal function used in the above public functions

          h2. Benchmarks

          A preliminary patch to MariaDB (that uses this idea) indeed demonstrates a very impressing query time reduce - 10% to 25%, depending on the exact data type and the number of fractional digits.

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIME'10:20:30'));
          {code}
          {noformat}
          10.5: 0.176 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.167 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIME'10:20:30.123456'));
          {code}
          {noformat}
          10.5: 0.288 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.261 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(DATE'2001-01-01'));
          {code}
          {noformat}
          10.5: 0.258 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.225 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIMESTAMP'2001-01-01 10:20:30'));
          {code}
          {noformat}
          10.5: 0.319 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.268 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIMESTAMP'2001-01-01 10:20:30.123456'));
          {code}
          {noformat}
          10.5: 0.438 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.275 (my_xxx_to_str() improvements)
          {noformat}
          bar Alexander Barkov made changes -
          Attachment fmt.diff [ 53630 ]
          bar Alexander Barkov made changes -
          Description The MySQL-8.0 code base has an interesting change improving number-to-string conversion:

          {noformat}
          commit 8d10f2fff6bbdea7f436b868ebb5fd811defc68a
          Author: Knut Anders Hatlen <knut.hatlen@oracle.com>
          Date: Thu Oct 10 13:55:07 2019 +0200

              Bug#30472888: IMPROVE THE PERFORMANCE OF INTEGER HANDLING IN THE TEXT PROTOCOL
          {noformat}

          The idea is that the division and the remainder operations used to print zerofill numbers appeared to be slow.
          It's cheaper to have a 200-byte long string consisting of precomposed 2-digit substrings strings, e.g. "00", "01", ... "99", and copy a proper 2-digit substring from this string, by index:

          {code:cpp}
          static const char two_digit_numbers[]=
          {
            "00010203040506070809"
            "10111213141516171819"
            "20212223242526272829"
            "30313233343536373839"
            "40414243444546474849"
            "50515253545556575859"
            "60616263646566676869"
            "70717273747576777879"
            "80818283848586878889"
            "90919293949596979899"
          };
          {code}

          This function prints a 2-digit number:
          {code:cpp}
          static inline char* fmt_number2(uint8 val, char *out)
          {
            const char *src= two_digit_numbers + val * 2;
            DBUG_ASSERT(val < 100);
            *out++= *src++;
            *out++= *src++;
            return out;
          }
          {code}
          The performance is achieved by the fact that it prints two digits at a time.

          Let's reuse this optimization in the following functions:
          - my_date_to_str()
          - my_time_to_str()
          - my_datetime_to_str()
          - my_interval_DDhhmmssff_to_str()
          - my_timeval_to_str
          - my_mmssff_to_str() - an internal function used in the above public functions

          h2. Benchmarks

          A preliminary patch to MariaDB (that uses this idea) indeed demonstrates a very impressing query time reduce - 10% to 25%, depending on the exact data type and the number of fractional digits.

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIME'10:20:30'));
          {code}
          {noformat}
          10.5: 0.176 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.167 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIME'10:20:30.123456'));
          {code}
          {noformat}
          10.5: 0.288 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.261 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(DATE'2001-01-01'));
          {code}
          {noformat}
          10.5: 0.258 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.225 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIMESTAMP'2001-01-01 10:20:30'));
          {code}
          {noformat}
          10.5: 0.319 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.268 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIMESTAMP'2001-01-01 10:20:30.123456'));
          {code}
          {noformat}
          10.5: 0.438 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.275 (my_xxx_to_str() improvements)
          {noformat}
          		The MySQL-8.0 code base has an interesting change improving number-to-string conversion:

          {noformat}
          commit 8d10f2fff6bbdea7f436b868ebb5fd811defc68a
          Author: Knut Anders Hatlen <knut.hatlen@oracle.com>
          Date: Thu Oct 10 13:55:07 2019 +0200

              Bug#30472888: IMPROVE THE PERFORMANCE OF INTEGER HANDLING IN THE TEXT PROTOCOL
          {noformat}

          The idea is that the division and the remainder operations used to print zerofill numbers appeared to be slow.
          It's cheaper to have a 200-byte long string consisting of precomposed 2-digit substrings strings, e.g. "00", "01", ... "99", and copy a proper 2-digit substring from this string, by index:

          {code:cpp}
          static const char two_digit_numbers[]=
          {
            "00010203040506070809"
            "10111213141516171819"
            "20212223242526272829"
            "30313233343536373839"
            "40414243444546474849"
            "50515253545556575859"
            "60616263646566676869"
            "70717273747576777879"
            "80818283848586878889"
            "90919293949596979899"
          };
          {code}

          This function prints a 2-digit number:
          {code:cpp}
          static inline char* fmt_number2(uint8 val, char *out)
          {
            const char *src= two_digit_numbers + val * 2;
            DBUG_ASSERT(val < 100);
            *out++= *src++;
            *out++= *src++;
            return out;
          }
          {code}
          The performance is achieved by the fact that it prints two digits at a time.

          Let's reuse this optimization in the following functions:
          - my_date_to_str()
          - my_time_to_str()
          - my_datetime_to_str()
          - my_interval_DDhhmmssff_to_str()
          - my_timeval_to_str
          - my_mmssff_to_str() - an internal function used in the above public functions

          h2. Benchmarks

          A preliminary patch to MariaDB (that uses this idea) indeed demonstrates a very impressing query time reduce - 10% to 38%, depending on the exact data type and the number of fractional digits.

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIME'10:20:30'));
          {code}
          {noformat}
          10.5: 0.176 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.167 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIME'10:20:30.123456'));
          {code}
          {noformat}
          10.5: 0.288 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.261 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(DATE'2001-01-01'));
          {code}
          {noformat}
          10.5: 0.258 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.225 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIMESTAMP'2001-01-01 10:20:30'));
          {code}
          {noformat}
          10.5: 0.319 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.268 (my_xxx_to_str() improvements)
          {noformat}

          {code:sql}
          SELECT BENCHMARK(10*1000*1000,CONCAT(TIMESTAMP'2001-01-01 10:20:30.123456'));
          {code}
          {noformat}
          10.5: 0.438 65ee216c35d843da7883d255219ab011e20340c2
          10.5: 0.275 (my_xxx_to_str() improvements)
          {noformat}
          bar Alexander Barkov made changes -
          issue.field.resolutiondate 2020-08-28 11:12:58.0 2020-08-28 11:12:58.62
          bar Alexander Barkov made changes -
          Component/s Data types [ 13906 ]
          Fix Version/s 10.5.6 [ 24508 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          Fix Version/s 10.5.7 [ 25019 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          Fix Version/s 10.5.6 [ 24508 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 112894 ] MariaDB v4 [ 134327 ]

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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