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

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

    XMLWordPrintable

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

          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.