PERF: avoid copy of dataframe while writing to SQL DBs
State: closed

- [ ] closes #xxxx
- [ ] tests added / passed
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] This change avoids making an entire copy of the dataframe while writing to SQL DBs with a chunksize. Instead we convert slices of the dataframe into a data list and write to the DB. Saves significant memory when writing large dataframes to SQL DBs. 


Labels:
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
Hello @sridhu88! Thanks for updating the PR.



Cheers ! There are no PEP8 issues in this Pull Request. :beers: 

##### Comment last updated on February 10, 2019 at 01:28 Hours UTC
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=h1) Report
> Merging [#25255](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/5f73594d49ea06ada4c6779006c00aaf41935595?src=pr&el=desc) will **decrease** coverage by `49.78%`.
> The diff coverage is `n/a`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/25255/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=tree)

```diff
@@             Coverage Diff             @@
##           master   #25255       +/-   ##
===========================================
- Coverage   91.93%   42.14%   -49.79%     
===========================================
  Files         166      166               
  Lines       52187    52187               
===========================================
- Hits        47978    21995    -25983     
- Misses       4209    30192    +25983
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `?` | |
| #single | `42.14% <ø> (ø)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/formats/latex.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvbGF0ZXgucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/core/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY2F0ZWdvcmljYWwucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/sas/sas\_constants.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXNfY29uc3RhbnRzLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/plotting.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvcGxvdHRpbmcucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvY29udmVydGVyLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/formats/html.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvaHRtbC5weQ==) | `0% <0%> (-99.35%)` | :arrow_down: |
| [pandas/core/groupby/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ3JvdXBieS9jYXRlZ29yaWNhbC5weQ==) | `0% <0%> (-95.46%)` | :arrow_down: |
| [pandas/io/sas/sas7bdat.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXM3YmRhdC5weQ==) | `0% <0%> (-91.17%)` | :arrow_down: |
| [pandas/io/sas/sas\_xport.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXNfeHBvcnQucHk=) | `0% <0%> (-90.15%)` | :arrow_down: |
| [pandas/core/tools/numeric.py](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvdG9vbHMvbnVtZXJpYy5weQ==) | `10.44% <0%> (-89.56%)` | :arrow_down: |
| ... and [123 more](https://codecov.io/gh/pandas-dev/pandas/pull/25255/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=footer). Last update [5f73594...d0a0c16](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=h1) Report
> Merging [#25255](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/5f73594d49ea06ada4c6779006c00aaf41935595?src=pr&el=desc) will **not change** coverage.
> The diff coverage is `n/a`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/25255/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=tree)

```diff
@@           Coverage Diff           @@
##           master   #25255   +/-   ##
=======================================
  Coverage   91.93%   91.93%           
=======================================
  Files         166      166           
  Lines       52187    52187           
=======================================
  Hits        47978    47978           
  Misses       4209     4209
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.48% <ø> (ø)` | :arrow_up: |
| #single | `42.14% <ø> (ø)` | :arrow_up: |


------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=footer). Last update [5f73594...d0a0c16](https://codecov.io/gh/pandas-dev/pandas/pull/25255?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

> you would need to test this

Sorry I'm new to Pandas development. Are you suggesting adding a test to demonstrate performance or just the correctness? If correctness, I thought there are already quite a few tests for the to_sql function which this change affects.
@sridhu88 a performance test that this actually improves things, see the asv_bench dir
Jeff, I am not sure it is easy to test this.  There are already sql tests in ASV, so we can run them and see if we see a difference, but I am not sure we will (copying is relatively cheap compared to the writing to sql). And we don't test memory in ASV. 
@jorisvandenbossche  https://github.com/pandas-dev/pandas/pull/25255#issuecomment-463202722

I agree, which is why I don't think this PR actually helps any, but I could be wrong. 
Actually, a specific benchmark that uses `chunksize` might be good to add (just checked and that is something we don't yet have) *in general*.

I quickly did a test with/without chunksize on master and this PR, and this PR is actually slower. Which is somewhat to be expected. We now chunk the dataframe before converting to arrays, while before it was slicing the arrays (and slicing arrays is faster than slicing dataframes). I still think this PR can be worth it, as it is a trade-off between memory and performance.
(Jeff, didn't see your comment before posting the last one, github is sometimes annoying :-))

@sridhu88 do you have a benchmark that measures memory that shows the impact of this PR? 
Without necessarily including that as a test, being able to show that is probably essential for deciding whether we want to merge this or not.
Thanks Jeff, Joris. Just to clarify, I labeled this PR as "PERF" purely for memory reasons. I was seeing that to insert a dataframe of ~2G, the peak memory during to_sql is approximately 2-3 times the dataframe size. With this change, the peak memory will remain very close to the actual size of the dataframe. As you mentioned, there may be a small hit to runtime. But in my tests, I didn't see a significant slowdown. Let me set up some benchmarking tests and get back to you soon.
Is this not something we can measure with ASV?

https://asv.readthedocs.io/en/stable/writing_benchmarks.html#memory

@sridhu88 can you see if we can get the ASV running and merge master?
Closing as stale. Ping if you'd like to continue