PERF: Some faster date/time string formatting
State: closed

"Easy" fixes related to #44764, taken from the big PR #46116:

 - Performance improvement in `BusinessHour`, `repr` is now 4 times faster
 - Performance improvement in `DatetimeArray` and `DatetimeIndex`: string formatting is now up to 80% faster (as fast as default) when one of the default strftime formats `"%Y-%m-%d %H:%M:%S"` or `"%Y-%m-%d %H:%M:%S.%f"` is used (see full bench in PR #46116).
 - Performance improvement in `Series.to_excel` and `DataFrame.to_excel` (see `ExcelFormatter`): processing dates can be up to 4% faster.
 - Performance improvement in `Series.to_sql` and `DataFrame.to_sql` (`SQLiteTable`): processing time arrays can be up to 65% faster !

I also added a few code comments about datetime formatting sections, to improve maintenance.


- [ ] closes #xxxx (Replace xxxx with the Github issue number)
- [ ] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Labels:
name: Performance
description: Memory or execution speed performance

Comments:
`BusinessHour` repr improvements mini-bench:

```python
get_repr = lambda: repr(self.offset1)
glob = globals()
glob.update(locals())
res = Timer("get_repr()", globals=glob).repeat(3, 10000)
```

before: [0.15039550000000013, 0.16580459999999952, 0.12403839999999988]
after: [0.035295299999999585, 0.0369325000000007, 0.03622369999999897]

= 4 times faster!
SQL mini-bench:

```python
import sqlite3
con = sqlite3.connect(':memory:')
dt = pd.date_range('2014-01-01', periods=1000, freq='10h')
df = pd.DataFrame({'time': dt.time})

from timeit import Timer
to_assess = lambda: df.to_sql('test_datetime', con, index=False, if_exists='replace')
glob = globals()
glob.update(locals())
res = Timer("to_assess()", globals=glob).repeat(5, 300)
print(res)
```

before: [2.667514099999991, 2.711463600000002, 2.7073388000000023, 2.6616616999999962, 2.7392122000000114]
after: [0.9359815999999981, 0.9132108000000017, 0.9482149999999976, 0.9659205999999969, 0.9622573000000045]
65% improvement
Ready for review
Thanks to both of you for the quick review ! I took care of all comments.

> do these have specific asv's that test performance? if not pls add. pls show them in the PR

Ok. I never used asv, so I'll have to check how this works. It may take a bit of time until I come back with something


So I finally took the time to write a few ASVs. Here are the results:

```
>>> asv compare c90294db 76035d5d

All benchmarks:

       before           after         ratio
     [c90294db]       [76035d5d]
     <feature/46319_locale_strftime_period^2>       <feature/44764_strftime_perf_datetimes>
-      8.69±0.8ms      2.56±0.05ms     0.29  tslibs.strftime.BusinessHourStrftime.time_frame_offset_repr(1000)
-        83.7±1ms       27.0±0.8ms     0.32  tslibs.strftime.BusinessHourStrftime.time_frame_offset_repr(10000)
-      9.17±0.3ms       3.09±0.2ms     0.34  tslibs.strftime.BusinessHourStrftime.time_frame_offset_str(1000)
-        82.5±3ms         25.9±1ms     0.31  tslibs.strftime.BusinessHourStrftime.time_frame_offset_str(10000)
       8.07±0.5ms       7.23±0.3ms    ~0.90  tslibs.strftime.DatetimeStrftime.time_frame_date_formatting(1000)
         73.1±2ms       68.5±0.5ms     0.94  tslibs.strftime.DatetimeStrftime.time_frame_date_formatting(10000)
       8.22±0.3ms       7.26±0.3ms    ~0.88  tslibs.strftime.DatetimeStrftime.time_frame_date_no_formatting(1000)
         78.6±3ms         71.4±2ms    ~0.91  tslibs.strftime.DatetimeStrftime.time_frame_date_no_formatting(10000)
-      9.17±0.2ms      1.36±0.02ms     0.15  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting(1000)
-        113±40ms       12.7±0.4ms     0.11  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting(10000)
         12.7±2ms         9.14±1ms    ~0.72  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_date_only(1000)
         78.9±3ms         69.8±2ms    ~0.88  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_date_only(10000)
-      10.1±0.3ms      1.84±0.05ms     0.18  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_with_float(1000)
-         116±9ms       15.3±0.4ms     0.13  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_with_float(10000)
         1.70±1ms      1.47±0.05ms    ~0.86  tslibs.strftime.DatetimeStrftime.time_frame_datetime_no_formatting(1000)
       14.5±0.6ms       12.9±0.5ms    ~0.89  tslibs.strftime.DatetimeStrftime.time_frame_datetime_no_formatting(10000)
```

These results do match the perf improvements that I had empirically measured on a few runs.

I chose to have a dedicated ASV file for this strftime topic rather than having a few functions here and there.
I did not write an ASV for the SQL perf improvements, as the one in io/sql.py could make the job but the `time` type is actually not present, and all types are passed at once to the `to_sql` method. So if I add a column the perf will degrade. Another option is to split this ASV test into several by parametrizing the column to send.

Note: I also realized that `BusinessHour` is missing from the `offsets.py` ASVs, but that's for another ticket/PR.

If you pay a close attention to the numbers, you see that the date-only formatting is catastrophic. I therefore propose to update this PR to handle the default date format with the same speed as the other default formats. Do you agree ?

I decided to go for the date handling proposal.
I also updated the benchmark with additional tests showing that custom formats are not handled.

Unfortunately I cannot yet make ASV to compile pandas correctly with the latest commits (something related to asv, since it compiles manualy). I'll wait for the next update of master and retry.

I ran the ASV bench against an older commit, where ASV was still working on master. It looks good:

```
asv compare 009c4c62 5eee0792

All benchmarks:

       before           after         ratio
     [009c4c62]       [5eee0792]
     <feature/44764_strftime_perf_datetimes^2~31>       <feature/44764_strftime_perf_datetimes~1>
-      9.30±0.9ms       2.41±0.1ms     0.26  tslibs.strftime.BusinessHourStrftime.time_frame_offset_repr(1000)
-        76.6±2ms         23.1±1ms     0.30  tslibs.strftime.BusinessHourStrftime.time_frame_offset_repr(10000)
-      7.71±0.4ms       3.30±0.4ms     0.43  tslibs.strftime.BusinessHourStrftime.time_frame_offset_str(1000)
-        75.0±3ms       23.4±0.5ms     0.31  tslibs.strftime.BusinessHourStrftime.time_frame_offset_str(10000)
       7.75±0.4ms       8.03±0.4ms     1.04  tslibs.strftime.DatetimeStrftime.time_frame_date_formatting_custom(1000)
         74.8±3ms         75.8±4ms     1.01  tslibs.strftime.DatetimeStrftime.time_frame_date_formatting_custom(10000)
-      7.01±0.1ms         815±20μs     0.12  tslibs.strftime.DatetimeStrftime.time_frame_date_formatting_default(1000)
-        66.9±1ms       6.76±0.3ms     0.10  tslibs.strftime.DatetimeStrftime.time_frame_date_formatting_default(10000)
-      7.42±0.2ms         736±40μs     0.10  tslibs.strftime.DatetimeStrftime.time_frame_date_to_str(1000)
-        71.3±2ms       6.37±0.2ms     0.09  tslibs.strftime.DatetimeStrftime.time_frame_date_to_str(10000)
-      14.9±0.3ms      9.23±0.08ms     0.62  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_custom(1000)
-         150±4ms         92.0±2ms     0.61  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_custom(10000)
-      9.06±0.7ms      1.45±0.06ms     0.16  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_default(1000)
-        86.9±4ms       13.1±0.6ms     0.15  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_default(10000)
-      7.15±0.4ms         840±20μs     0.12  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_default_date_only(1000)
-       76.6±10ms      6.33±0.03ms     0.08  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_default_date_only(10000)
-        17.5±2ms      1.76±0.03ms     0.10  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_default_with_float(1000)
-        180±40ms       15.5±0.3ms     0.09  tslibs.strftime.DatetimeStrftime.time_frame_datetime_formatting_default_with_float(10000)
       1.50±0.1ms      1.37±0.04ms     0.91  tslibs.strftime.DatetimeStrftime.time_frame_datetime_to_str(1000)
         15.4±3ms      12.5±0.09ms    ~0.81  tslibs.strftime.DatetimeStrftime.time_frame_datetime_to_str(10000)
```

Once this is merged, the next PR will focus on the ASVs above with suffix "_custom", for which I found a solution already (demonstrated in https://github.com/pandas-dev/pandas/pull/46116#issuecomment-1065894261).

@jreback let me know if this is sufficient for you.
Thanks for the review @jreback ! I made all requested changes.

I finally made the missing benchmark, for the `to_sql` improvement (that is not a side effect but a dedicated modification). For this I edited the existing `io/sql.py` ASV benchmark by adding the date and time column types to all tests. Note that I added them not only to the SQL writing tests but also to the SQL reading ones, so that it can improve the global ASV coverage.

Below are the corresponding tests (I do not include the ones related to strftime as they are identical to https://github.com/pandas-dev/pandas/pull/46759#issuecomment-1126709997)

```
>asv compare 009c4c62 5eee0792

All benchmarks:

       before           after         ratio
     [009c4c62]       [5eee0792]
     <feature/44764_strftime_perf_datetimes~5^2~31>       <feature/44764_strftime_perf_datetimes~6>
         96.5±2ms         94.8±1ms     0.98  io.sql.ReadSQLTable.time_read_sql_table_all
       21.3±0.5ms       20.5±0.3ms     0.96  io.sql.ReadSQLTable.time_read_sql_table_parse_dates
       15.4±0.8ms         16.6±4ms     1.08  io.sql.ReadSQLTableDtypes.time_read_sql_table_column('bool')
       24.7±0.5ms       25.4±0.6ms     1.03  io.sql.ReadSQLTableDtypes.time_read_sql_table_column('date')
       38.6±0.4ms       40.3±0.9ms     1.04  io.sql.ReadSQLTableDtypes.time_read_sql_table_column('datetime')
       14.1±0.4ms       14.3±0.5ms     1.02  io.sql.ReadSQLTableDtypes.time_read_sql_table_column('float')
       14.4±0.4ms       14.5±0.7ms     1.01  io.sql.ReadSQLTableDtypes.time_read_sql_table_column('float_with_nan')
       15.3±0.2ms       16.9±0.5ms    ~1.11  io.sql.ReadSQLTableDtypes.time_read_sql_table_column('int')
       15.2±0.5ms         15.4±1ms     1.01  io.sql.ReadSQLTableDtypes.time_read_sql_table_column('string')
       25.9±0.8ms       25.6±0.8ms     0.99  io.sql.ReadSQLTableDtypes.time_read_sql_table_column('time')
       45.3±0.7ms         50.6±3ms    ~1.12  io.sql.SQL.time_read_sql_query('sqlalchemy')
         42.0±2ms       43.0±0.7ms     1.03  io.sql.SQL.time_read_sql_query('sqlite')
          160±2ms         163±10ms     1.02  io.sql.SQL.time_to_sql_dataframe('sqlalchemy')
-         138±2ms         97.4±2ms     0.71  io.sql.SQL.time_to_sql_dataframe('sqlite')
       13.4±0.9ms       13.1±0.2ms     0.98  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlalchemy', 'bool')
       12.4±0.2ms       13.0±0.5ms     1.05  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlalchemy', 'date')
       12.3±0.4ms       13.0±0.3ms     1.05  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlalchemy', 'datetime')
       12.0±0.4ms       11.6±0.3ms     0.96  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlalchemy', 'float')
       11.8±0.6ms       11.6±0.3ms     0.98  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlalchemy', 'float_with_nan')
       13.3±0.4ms       13.2±0.2ms     0.99  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlalchemy', 'int')
       12.0±0.2ms       12.2±0.7ms     1.02  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlalchemy', 'string')
       12.9±0.3ms       13.1±0.4ms     1.02  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlalchemy', 'time')
       9.16±0.3ms       9.24±0.6ms     1.01  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlite', 'bool')
       8.54±0.2ms       8.44±0.3ms     0.99  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlite', 'date')
       8.70±0.4ms       8.56±0.5ms     0.98  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlite', 'datetime')
       7.87±0.2ms       8.18±0.3ms     1.04  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlite', 'float')
       7.94±0.3ms       7.80±0.2ms     0.98  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlite', 'float_with_nan')
       9.10±0.3ms       9.59±0.5ms     1.05  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlite', 'int')
       8.45±0.5ms       8.51±0.2ms     1.01  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlite', 'string')
       8.55±0.2ms       8.67±0.4ms     1.01  io.sql.WriteSQLDtypes.time_read_sql_query_select_column('sqlite', 'time')
         53.4±3ms         53.1±2ms     0.99  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlalchemy', 'bool')
         63.7±2ms         64.7±2ms     1.02  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlalchemy', 'date')
       76.4±0.8ms         81.6±4ms     1.07  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlalchemy', 'datetime')
       49.2±0.3ms         50.0±3ms     1.02  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlalchemy', 'float')
         50.6±2ms       49.6±0.6ms     0.98  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlalchemy', 'float_with_nan')
         48.7±2ms         49.0±2ms     1.01  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlalchemy', 'int')
         53.1±4ms         51.2±1ms     0.97  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlalchemy', 'string')
         63.8±3ms         66.7±3ms     1.04  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlalchemy', 'time')
         29.3±1ms       30.8±0.7ms     1.05  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlite', 'bool')
         36.9±2ms       35.9±0.3ms     0.97  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlite', 'date')
         43.3±1ms       45.3±0.7ms     1.05  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlite', 'datetime')
       25.8±0.3ms         29.5±4ms    ~1.14  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlite', 'float')
       26.9±0.5ms       26.9±0.3ms     1.00  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlite', 'float_with_nan')
       25.2±0.2ms       25.9±0.5ms     1.03  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlite', 'int')
       25.8±0.3ms       27.0±0.4ms     1.05  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlite', 'string')
-        85.2±2ms         45.3±1ms     0.53  io.sql.WriteSQLDtypes.time_to_sql_dataframe_column('sqlite', 'time')
(...)
```

Notice the two lines with the "-" upfront: here is where the progress is.
This is now again ready for a review. Thanks !
It seems that everything is ok now. Thanks for this review!
@jreback : could we move forward on this ? thanks in advance!
Note after updating the branch to the latest `main`: a recent mod in `format_array_from_datetime` introduced a new parameter `reso`, without documenting it in the docstring :( 
I tried my best to guess what I should do for the "basic format day" branch but maybe it is not the right interpretation.

@jbrockmendel @jreback @mroeschke if one of you originated this mod, could you please suggest a docstring sentence and check that my interpretation (of not taking it into account for basic format day branch) is still relevant ?

EDIT: @jbrockmendel answered so this is OK now.
Ping ?
I rebased one more time, and once again it seems that there are some unrelated issues in two runners.
@jbrockmendel , @mroeschke , can you confirm @jreback 's approval and proceed to merge ? Thanks in advance
I updated the PR to the latest head, it builds ok. @jreback approved. 

Could we proceed ? @jreback @mroeschke @jbrockmendel
thanks @smarie 
Great ! Thanks all for the reviews