Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
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)
|