This is an automated email from the ASF dual-hosted git repository. hvanhovell pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push: new bd027f6 [SPARK-26656][SQL] Benchmarks for date and timestamp functions bd027f6 is described below commit bd027f6e0e64b7e12ba9d3c77e0797a00c61e72d Author: Maxim Gekk <max.g...@gmail.com> AuthorDate: Mon Jan 28 14:21:21 2019 +0100 [SPARK-26656][SQL] Benchmarks for date and timestamp functions ## What changes were proposed in this pull request? Added the following benchmarks: - Extract components from timestamp like year, month, day and etc. - Current date and time - Date arithmetic like date_add, date_sub - Format dates and timestamps - Convert timestamps from/to UTC Closes #23661 from MaxGekk/datetime-benchmark. Authored-by: Maxim Gekk <max.g...@gmail.com> Signed-off-by: Herman van Hovell <hvanhov...@databricks.com> --- sql/core/benchmarks/DateTimeBenchmark-results.txt | 416 +++++++++++++++++++++ .../execution/benchmark/DateTimeBenchmark.scala | 123 ++++++ 2 files changed, 539 insertions(+) diff --git a/sql/core/benchmarks/DateTimeBenchmark-results.txt b/sql/core/benchmarks/DateTimeBenchmark-results.txt new file mode 100644 index 0000000..8bbe310 --- /dev/null +++ b/sql/core/benchmarks/DateTimeBenchmark-results.txt @@ -0,0 +1,416 @@ +================================================================================================ +Extract components +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +cast to timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +cast to timestamp wholestage off 276 / 290 36.2 27.6 1.0X +cast to timestamp wholestage on 254 / 267 39.4 25.4 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +year of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +year of timestamp wholestage off 699 / 700 14.3 69.9 1.0X +year of timestamp wholestage on 680 / 689 14.7 68.0 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +quarter of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +quarter of timestamp wholestage off 848 / 864 11.8 84.8 1.0X +quarter of timestamp wholestage on 784 / 797 12.8 78.4 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +month of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +month of timestamp wholestage off 652 / 653 15.3 65.2 1.0X +month of timestamp wholestage on 671 / 677 14.9 67.1 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +weekofyear of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +weekofyear of timestamp wholestage off 1233 / 1233 8.1 123.3 1.0X +weekofyear of timestamp wholestage on 1236 / 1240 8.1 123.6 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +day of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +day of timestamp wholestage off 649 / 655 15.4 64.9 1.0X +day of timestamp wholestage on 670 / 678 14.9 67.0 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +dayofyear of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +dayofyear of timestamp wholestage off 692 / 704 14.5 69.2 1.0X +dayofyear of timestamp wholestage on 695 / 698 14.4 69.5 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +dayofmonth of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +dayofmonth of timestamp wholestage off 660 / 660 15.1 66.0 1.0X +dayofmonth of timestamp wholestage on 667 / 671 15.0 66.7 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +dayofweek of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +dayofweek of timestamp wholestage off 798 / 802 12.5 79.8 1.0X +dayofweek of timestamp wholestage on 804 / 820 12.4 80.4 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +weekday of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +weekday of timestamp wholestage off 759 / 760 13.2 75.9 1.0X +weekday of timestamp wholestage on 774 / 813 12.9 77.4 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +hour of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +hour of timestamp wholestage off 465 / 468 21.5 46.5 1.0X +hour of timestamp wholestage on 443 / 451 22.6 44.3 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +minute of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +minute of timestamp wholestage off 439 / 440 22.8 43.9 1.0X +minute of timestamp wholestage on 448 / 452 22.3 44.8 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +second of timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +second of timestamp wholestage off 438 / 448 22.8 43.8 1.0X +second of timestamp wholestage on 430 / 445 23.3 43.0 1.0X + + +================================================================================================ +Current date and time +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +current_date: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +current_date wholestage off 212 / 213 47.2 21.2 1.0X +current_date wholestage on 225 / 230 44.4 22.5 0.9X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +current_timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +current_timestamp wholestage off 216 / 217 46.3 21.6 1.0X +current_timestamp wholestage on 210 / 216 47.7 21.0 1.0X + + +================================================================================================ +Date arithmetic +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +cast to date: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +cast to date wholestage off 463 / 464 21.6 46.3 1.0X +cast to date wholestage on 502 / 508 19.9 50.2 0.9X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +last_day: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +last_day wholestage off 693 / 701 14.4 69.3 1.0X +last_day wholestage on 697 / 706 14.3 69.7 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +next_day: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +next_day wholestage off 553 / 570 18.1 55.3 1.0X +next_day wholestage on 595 / 597 16.8 59.5 0.9X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_add: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_add wholestage off 459 / 463 21.8 45.9 1.0X +date_add wholestage on 473 / 477 21.1 47.3 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_sub: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_sub wholestage off 473 / 475 21.1 47.3 1.0X +date_sub wholestage on 479 / 497 20.9 47.9 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +add_months: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +add_months wholestage off 898 / 908 11.1 89.8 1.0X +add_months wholestage on 898 / 906 11.1 89.8 1.0X + + +================================================================================================ +Formatting dates +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +format date: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +format date wholestage off 7181 / 7228 1.4 718.1 1.0X +format date wholestage on 7303 / 7356 1.4 730.3 1.0X + + +================================================================================================ +Formatting timestamps +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +from_unixtime: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +from_unixtime wholestage off 7472 / 7476 1.3 747.2 1.0X +from_unixtime wholestage on 7453 / 7460 1.3 745.3 1.0X + + +================================================================================================ +Convert timestamps +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +from_utc_timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +from_utc_timestamp wholestage off 894 / 895 11.2 89.4 1.0X +from_utc_timestamp wholestage on 881 / 883 11.4 88.1 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +to_utc_timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +to_utc_timestamp wholestage off 909 / 910 11.0 90.9 1.0X +to_utc_timestamp wholestage on 903 / 906 11.1 90.3 1.0X + + +================================================================================================ +Intervals +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +cast interval: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +cast interval wholestage off 245 / 256 40.7 24.5 1.0X +cast interval wholestage on 227 / 230 44.0 22.7 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +datediff: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +datediff wholestage off 735 / 738 13.6 73.5 1.0X +datediff wholestage on 687 / 689 14.6 68.7 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +months_between: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +months_between wholestage off 1653 / 1654 6.1 165.3 1.0X +months_between wholestage on 1630 / 1636 6.1 163.0 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +window: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +window wholestage off 1636 / 1661 0.6 1635.9 1.0X +window wholestage on 19997 / 20240 0.1 19997.4 0.1X + + +================================================================================================ +Truncation +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc YEAR: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc YEAR wholestage off 889 / 892 11.2 88.9 1.0X +date_trunc YEAR wholestage on 831 / 837 12.0 83.1 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc YYYY: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc YYYY wholestage off 910 / 917 11.0 91.0 1.0X +date_trunc YYYY wholestage on 834 / 849 12.0 83.4 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc YY: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc YY wholestage off 897 / 901 11.2 89.7 1.0X +date_trunc YY wholestage on 836 / 841 12.0 83.6 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc MON: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc MON wholestage off 942 / 944 10.6 94.2 1.0X +date_trunc MON wholestage on 881 / 887 11.3 88.1 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc MONTH: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc MONTH wholestage off 935 / 937 10.7 93.5 1.0X +date_trunc MONTH wholestage on 881 / 886 11.4 88.1 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc MM: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc MM wholestage off 934 / 935 10.7 93.4 1.0X +date_trunc MM wholestage on 878 / 880 11.4 87.8 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc DAY: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc DAY wholestage off 472 / 478 21.2 47.2 1.0X +date_trunc DAY wholestage on 415 / 418 24.1 41.5 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc DD: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc DD wholestage off 472 / 485 21.2 47.2 1.0X +date_trunc DD wholestage on 414 / 417 24.1 41.4 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc HOUR: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc HOUR wholestage off 451 / 462 22.2 45.1 1.0X +date_trunc HOUR wholestage on 422 / 429 23.7 42.2 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc MINUTE: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc MINUTE wholestage off 291 / 291 34.4 29.1 1.0X +date_trunc MINUTE wholestage on 268 / 272 37.3 26.8 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc SECOND: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc SECOND wholestage off 290 / 290 34.5 29.0 1.0X +date_trunc SECOND wholestage on 266 / 270 37.6 26.6 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc WEEK: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc WEEK wholestage off 794 / 797 12.6 79.4 1.0X +date_trunc WEEK wholestage on 754 / 761 13.3 75.4 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +date_trunc QUARTER: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +date_trunc QUARTER wholestage off 5261 / 5271 1.9 526.1 1.0X +date_trunc QUARTER wholestage on 5145 / 5151 1.9 514.5 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +trunc year: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +trunc year wholestage off 233 / 233 43.0 23.3 1.0X +trunc year wholestage on 213 / 216 46.9 21.3 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +trunc yyyy: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +trunc yyyy wholestage off 232 / 233 43.1 23.2 1.0X +trunc yyyy wholestage on 214 / 223 46.7 21.4 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +trunc yy: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +trunc yy wholestage off 228 / 228 43.9 22.8 1.0X +trunc yy wholestage on 215 / 219 46.6 21.5 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +trunc mon: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +trunc mon wholestage off 232 / 232 43.0 23.2 1.0X +trunc mon wholestage on 216 / 217 46.4 21.6 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +trunc month: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +trunc month wholestage off 233 / 233 43.0 23.3 1.0X +trunc month wholestage on 215 / 217 46.4 21.5 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +trunc mm: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +trunc mm wholestage off 226 / 233 44.2 22.6 1.0X +trunc mm wholestage on 214 / 220 46.7 21.4 1.1X + + +================================================================================================ +Parsing +================================================================================================ + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +to timestamp str: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +to timestamp str wholestage off 166 / 170 6.0 165.8 1.0X +to timestamp str wholestage on 165 / 167 6.1 164.9 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +to_timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +to_timestamp wholestage off 1439 / 1445 0.7 1439.4 1.0X +to_timestamp wholestage on 1366 / 1368 0.7 1365.7 1.1X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +to_unix_timestamp: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +to_unix_timestamp wholestage off 1386 / 1389 0.7 1385.6 1.0X +to_unix_timestamp wholestage on 1388 / 1392 0.7 1387.8 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +to date str: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +to date str wholestage off 163 / 166 6.1 163.3 1.0X +to date str wholestage on 160 / 163 6.2 160.3 1.0X + +Java HotSpot(TM) 64-Bit Server VM 1.8.0_202-ea-b03 on Mac OS X 10.14.2 +Intel(R) Core(TM) i7-4850HQ CPU @ 2.30GHz +to_date: Best/Avg Time(ms) Rate(M/s) Per Row(ns) Relative +------------------------------------------------------------------------------------------------ +to_date wholestage off 1474 / 1474 0.7 1473.9 1.0X +to_date wholestage on 1460 / 1465 0.7 1459.6 1.0X + + diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/DateTimeBenchmark.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/DateTimeBenchmark.scala new file mode 100644 index 0000000..cbd51b4 --- /dev/null +++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/DateTimeBenchmark.scala @@ -0,0 +1,123 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.spark.sql + +import org.apache.spark.sql.execution.benchmark.SqlBasedBenchmark + +/** + * Synthetic benchmark for date and timestamp functions. + * To run this benchmark: + * {{{ + * 1. without sbt: + * bin/spark-submit --class <this class> --jars <spark core test jar> <sql core test jar> + * 2. build/sbt "sql/test:runMain <this class>" + * 3. generate result: + * SPARK_GENERATE_BENCHMARK_FILES=1 build/sbt "sql/test:runMain <this class>" + * Results will be written to "benchmarks/DateTimeBenchmark-results.txt". + * }}} + */ +object DateTimeBenchmark extends SqlBasedBenchmark { + private def doBenchmark(cardinality: Int, exprs: String*): Unit = { + spark.range(cardinality).selectExpr(exprs: _*).write.format("noop").save() + } + + private def run(cardinality: Int, name: String, exprs: String*): Unit = { + codegenBenchmark(name, cardinality) { + doBenchmark(cardinality, exprs: _*) + } + } + + private def run(cardinality: Int, func: String): Unit = { + codegenBenchmark(s"$func of timestamp", cardinality) { + doBenchmark(cardinality, s"$func(cast(id as timestamp))") + } + } + + override def runBenchmarkSuite(mainArgs: Array[String]): Unit = { + val N = 10000000 + runBenchmark("Extract components") { + run(N, "cast to timestamp", "cast(id as timestamp)") + run(N, "year") + run(N, "quarter") + run(N, "month") + run(N, "weekofyear") + run(N, "day") + run(N, "dayofyear") + run(N, "dayofmonth") + run(N, "dayofweek") + run(N, "weekday") + run(N, "hour") + run(N, "minute") + run(N, "second") + } + runBenchmark("Current date and time") { + run(N, "current_date", "current_date") + run(N, "current_timestamp", "current_timestamp") + } + runBenchmark("Date arithmetic") { + val dateExpr = "cast(cast(id as timestamp) as date)" + run(N, "cast to date", dateExpr) + run(N, "last_day", s"last_day($dateExpr)") + run(N, "next_day", s"next_day($dateExpr, 'TU')") + run(N, "date_add", s"date_add($dateExpr, 10)") + run(N, "date_sub", s"date_sub($dateExpr, 10)") + run(N, "add_months", s"add_months($dateExpr, 10)") + } + runBenchmark("Formatting dates") { + val dateExpr = "cast(cast(id as timestamp) as date)" + run(N, "format date", s"date_format($dateExpr, 'MMM yyyy')") + } + runBenchmark("Formatting timestamps") { + run(N, "from_unixtime", "from_unixtime(id, 'yyyy-MM-dd HH:mm:ss.SSSSSS')") + } + runBenchmark("Convert timestamps") { + val timestampExpr = "cast(id as timestamp)" + run(N, "from_utc_timestamp", s"from_utc_timestamp($timestampExpr, 'CET')") + run(N, "to_utc_timestamp", s"to_utc_timestamp($timestampExpr, 'CET')") + } + runBenchmark("Intervals") { + val (start, end) = ("cast(id as timestamp)", "cast((id+8640000) as timestamp)") + run(N, "cast interval", start, end) + run(N, "datediff", s"datediff($start, $end)") + run(N, "months_between", s"months_between($start, $end)") + run(1000000, "window", s"window($start, 100, 10, 1)") + } + runBenchmark("Truncation") { + val timestampExpr = "cast(id as timestamp)" + Seq("YEAR", "YYYY", "YY", "MON", "MONTH", "MM", "DAY", "DD", "HOUR", "MINUTE", + "SECOND", "WEEK", "QUARTER").foreach { level => + run(N, s"date_trunc $level", s"date_trunc('$level', $timestampExpr)") + } + val dateExpr = "cast(cast(id as timestamp) as date)" + Seq("year", "yyyy", "yy", "mon", "month", "mm").foreach { level => + run(N, s"trunc $level", s"trunc('$level', $dateExpr)") + } + } + runBenchmark("Parsing") { + val n = 1000000 + val timestampStrExpr = "concat('2019-01-27 11:02:01.', cast(mod(id, 1000) as string))" + val pattern = "'yyyy-MM-dd HH:mm:ss.SSS'" + run(n, "to timestamp str", timestampStrExpr) + run(n, "to_timestamp", s"to_timestamp($timestampStrExpr, $pattern)") + run(n, "to_unix_timestamp", s"to_unix_timestamp($timestampStrExpr, $pattern)") + val dateStrExpr = "concat('2019-01-', cast(mod(id, 25) as string))" + run(n, "to date str", dateStrExpr) + run(n, "to_date", s"to_date($dateStrExpr, 'yyyy-MM-dd')") + } + } +} --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org