|
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)
|
|
fmt.diff