Repository: pandas-dev/pandas
Issue category: Feature Request

Issue:
Use multi-row inserts for massive speedups on to_sql over high latency connections
I have been trying to insert ~30k rows into a mysql database using pandas-0.15.1, oursql-0.9.3.1 and sqlalchemy-0.9.4. Because the machine is as across the atlantic from me, calling `data.to_sql` was taking >1 hr to insert the data. On inspecting with wireshark, the issue is that it is sending an insert for every row, then waiting for the ACK before sending the next, and, long story short, the ping times are killing me.

However, following the instructions from [SQLAlchemy](http://docs.sqlalchemy.org/en/latest/core/dml.html#sqlalchemy.sql.expression.Insert.values), I changed 

``` python
def _execute_insert(self, conn, keys, data_iter):
    data = [dict((k, v) for k, v in zip(keys, row)) for row in data_iter]
    conn.execute(self.insert_statement(), data)
```

to 

``` python
def _execute_insert(self, conn, keys, data_iter):
    data = [dict((k, v) for k, v in zip(keys, row)) for row in data_iter]
    conn.execute(self.insert_statement().values(data))
```

and the entire operation completes in less than a minute. (To save you a click, the difference is between multiple calls to `insert into foo (columns) values (rowX)` and one massive `insert into foo (columns) VALUES (row1), (row2), row3)`). Given how often people are likely to use pandas to insert large volumes of data, this feels like a huge win that would be great to be included more widely.

Some challenges:
- Not every database supports multirow inserts (SQLite and SQLServer didn't in the past, though they do now). I don't know how to check for this via SQLAlchemy
- The MySQL server I was using didn't allow me to insert the data all in one go, I had to set the chunksize (5k worked fine, but I guess the full 30k was too much). If we made this the default insert, most people would have to add a chunk size (which might be hard to calculate, as it might be determined by the maximum packet size of the server).

The easiest way to do this, would be to add a `multirow=` boolean parameter (default `False`) to the `to_sql` function, and then leave the user responsible for setting the chunksize, but perhaps there's a better way?

Thoughts?



Hints:
This seems reasonable. Thanks for investigating this!

For the implementation, it will depend on how sqlalchemy deals with database flavors that does not support this (I can't test this at the moment, but it seems that sqlalchemy raises an error (eg http://stackoverflow.com/questions/23886764/multiple-insert-statements-in-mssql-with-sqlalchemy). Also, if it has the consequence that a lot of people will have to set chunksize, this is indeed not a good idea to do as default (unless we set chunksize to a value by default). 
So adding a keyword seems maybe better.

@artemyk @mangecoeur @hayd @danielballan

Apparently SQLAlchemy has a flag `dialect.supports_multivalues_insert` (see e.g. http://pydoc.net/Python/SQLAlchemy/0.8.3/sqlalchemy.sql.compiler/ , possibly called `supports_multirow_insert` in other versions, https://www.mail-archive.com/mediawiki-commits@lists.wikimedia.org/msg202880.html ).

Since this has the potential to speed up inserts a lot, and we can check for support easily, I'm thinking maybe we could do it by default, and also set chunksize to a default value (e.g. 16kb chunks... not sure what's too big in most situations).  If the multirow insert fails, we could throw an exception suggesting lowering the chunksize?

Now I just need to persuade the SQLAlchemy folks to set `supports_multivalues_insert` to true on SQL Server >2005 (I hacked it into the code and it works fine, but it's not on by default).

On a more on-topic note, I think the chunksize could be tricky. On my mysql setup (which I probably configured to allow large packets), I can set chunksize=5000, on my SQLServer setup, 500 was too large, but 100 worked fine. However, it's probably true that most of the benefits from this technique come from going from inserting 1 row at a time to 100, rather than 100 to 1000.

What if `chunksize=None` meant "Adaptively choose a chunksize"? Attempt something like 5000, 500, 50, 1. Users could turn this off by specifying a chunksize. If the overhead from these attempts is too large, I like @maxgrenderjones suggestion: `chunksize=10` is a better default than `chunksize=1`.

On that last comment "`chunksize=10` is a better default than `chunksize=1`" -> that is not fully true I think. The current situation is to do _one_ execute statement that consists of multiline single-row insert statements (which is not a chunksize of 1), while `chunksize=10` would mean doing a lot of execute statements with each time one multi-row insert. 
And I don't know if this is necessarily faster, but much depends on the situation. For example with the current code and with a local sqlite database:

```
In [4]: engine = create_engine('sqlite:///:memory:') #, echo='debug')

In [5]: df = pd.DataFrame(np.random.randn(50000, 10))

In [6]: %timeit df.to_sql('test_default', engine, if_exists='replace')
1 loops, best of 3: 956 ms per loop

In [7]: %timeit df.to_sql('test_default', engine, if_exists='replace', chunksize=10)
1 loops, best of 3: 2.23 s per loop
```

But of course this does not use the multi-row feature

We've figured out how to monkey patch - might be useful to someone else. Have this code before importing pandas.

```
from pandas.io.sql import SQLTable

def _execute_insert(self, conn, keys, data_iter):
    print "Using monkey-patched _execute_insert"
    data = [dict((k, v) for k, v in zip(keys, row)) for row in data_iter]
    conn.execute(self.insert_statement().values(data))

SQLTable._execute_insert = _execute_insert

```

Maybe we can just start with adding this feature through a new `multirow=True` keyword (with a default of False for now), and then we can later always see if we can enable it by default?

@maxgrenderjones @nhockham interested to do a PR to add this?

@jorisvandenbossche I think it's risky to start adding keyword arguments to address specific performance profiles. If you can guarantee that it's faster in all cases (if necessary by having it determine the best method based on the inputs) then you don't need a flag at all.

Different DB-setups may have different performance optimizations (different DB perf profiles, local vs network, big memory vs fast SSD, etc, etc), if you start adding keyword flags for each it becomes a mess.

I would suggest creating subclasses of SQLDatabase and SQLTable to address performance specific implementations, they would be used through the object-oriented API. Perhaps a "backend switching" method could be added but frankly using the OO api is very simple so this is probably overkill for what is already a specialized use-case.

I created such a sub-class for loading large datasets to Postgres (it's actually much faster to save data to CSV then use the built-in non-standard COPY FROM sql commands than to use inserts, see https://gist.github.com/mangecoeur/1fbd63d4758c2ba0c470#file-pandas_postgres-py). To use it you just do `PgSQLDatabase(engine, <args>).to_sql(frame, name,<kwargs>)`

Just for reference,  I tried running the code by @jorisvandenbossche (Dec 3rd post) using the multirow feature.  It's quite a bit slower.  So the speed-tradeoffs here is not trivial:

```
In [4]: engine = create_engine('sqlite:///:memory:') #, echo='debug')

In [5]: df = pd.DataFrame(np.random.randn(50000, 10))

In [6]: 

In [6]: %timeit df.to_sql('test_default', engine, if_exists='replace')
1 loops, best of 3: 1.05 s per loop

In [7]: 

In [7]: from pandas.io.sql import SQLTable

In [8]: 

In [8]: def _execute_insert(self, conn, keys, data_iter):
   ...:         data = [dict((k, v) for k, v in zip(keys, row)) for row in data_iter]
   ...:         conn.execute(self.insert_statement().values(data))
   ...:     

In [9]: SQLTable._execute_insert = _execute_insert

In [10]: 

In [10]: reload(pd)
Out[10]: <module 'pandas' from '/usr/local/lib/python2.7/site-packages/pandas/__init__.pyc'>

In [11]: 

In [11]: %timeit df.to_sql('test_default', engine, if_exists='replace', chunksize=10)
1 loops, best of 3: 9.9 s per loop
```

Also, I agree that adding keyword parameters is risky.  However, the multirow feature seems pretty fundamental.  Also, 'monkey-patching' is probably not more robust to API changes than keyword parameters.

Its as i suspected. Monkey patching isn't the solution I was suggesting - rather that we ship a number of performance oriented subclasses that the informed user could use through the OO interface (to avoid loading the functional api with too many options)

-----Original Message-----
From: "Artemy Kolchinsky" notifications@github.com
Sent: ‎26/‎02/‎2015 17:13
To: "pydata/pandas" pandas@noreply.github.com
Cc: "mangecoeur" jon.chambers3001@gmail.com
Subject: Re: [pandas] Use multi-row inserts for massive speedups on to_sqlover high latency connections (#8953)

Just for reference, I tried running the code by @jorisvandenbossche (Dec 3rd post) using the multirow feature. It's quite a bit slower. So the speed-tradeoffs here is not trivial:
In [4]: engine = create_engine('sqlite:///:memory:') #, echo='debug')

In [5]: df = pd.DataFrame(np.random.randn(50000, 10))

In [6]: 

In [6]: %timeit df.to_sql('test_default', engine, if_exists='replace')
1 loops, best of 3: 1.05 s per loop

In [7]: 

In [7]: from pandas.io.sql import SQLTable

In [8]: 

In [8]: def _execute_insert(self, conn, keys, data_iter):
   ...:         data = [dict((k, v) for k, v in zip(keys, row)) for row in data_iter]
   ...:         conn.execute(self.insert_statement().values(data))
   ...:     

In [9]: SQLTable._execute_insert = _execute_insert

In [10]: 

In [10]: reload(pd)
Out[10]: <module 'pandas' from '/usr/local/lib/python2.7/site-packages/pandas/__init__.pyc'>

In [11]: 

In [11]: %timeit df.to_sql('test_default', engine, if_exists='replace', chunksize=10)
1 loops, best of 3: 9.9 s per loop
Also, I agree that adding keyword parameters is risky. However, the multirow feature seems pretty fundamental. Also, 'monkey-patching' is probably not more robust to API changes than keyword parameters.
—
Reply to this email directly or view it on GitHub.

As per the initial ticket title, I don't think this approach is going to be preferable in all cases, so I wouldn't make it the default. However, without it, the pandas `to_sql` unusable for me, so it's important enough for me to continue to request the change. (It's also become the first thing I change when I upgrade my pandas version). As for sensible `chunksize` values, I don't think there is one true `n`, as the packet size will depend on how many columns there are (and what's in them) in hard to predict ways. Unfortunately SQLServer fails with an error message that looks totally unrelated (but isn't) if you set the `chunksize` too high (which is probably why multirow inserts aren't turned on except with a patch in SQLAlchemy), but it works fine with `mysql`. Users may need to experiment to determine what value of `n` is likely to result in an acceptably large packet size (for whatever their backing database is). Having pandas chose `n` is likely to get land us way further down in the implementation details than we want to be (i.e. the opposite direction from the maximum-possible-abstraction SQLALchemy approach)

In short, my recommendation would be to add it as a keyword, with some helpful commentary about how to use it. This wouldn't be the first time a keyword was used to select an implementation (see: http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.apply.html) but that perhaps isn't the best example, as I haven't the first idea about what `raw=` means, even having read the explanation!

I have noticed that it also consumes a huge amount of memory. Like a 1.6+ GB DataFrame with some 700,000 rows and 301 columns requires almost 34 GB during insert! That is like over the top inefficient. Any ideas on why that might be the case? Here is a screen clip:

![image](https://cloud.githubusercontent.com/assets/8196363/17013812/02b51276-4ede-11e6-8006-9fd32cb01e14.png)

Hi guys,
any progress on this issue?

I am try to insert around 200K rows using to_sql but it takes forever and consume a huge amount of memory! Using chuncksize helps with the memory but still the speed is very slow. 

My impression, looking at the MSSQL DBase trace is that the insertion is actually performed one row at the time. 

The only viable approach now is to dump to a csv file on a shared folder and use BULK INSERT. But it very annoying and inelegant!

@andreacassioli You can use [odo](http://odo.pydata.org/) to insert a DataFrame into an SQL database through an intermediary CSV file. See [Loading CSVs into SQL Databases](http://odo.pydata.org/en/latest/perf.html). 

I don't think you can come even close to `BULK INSERT` performance using ODBC.

@ostrokach thank you, indeed I am using csv files now. If I could get close, I would trade a bit of time for simplicity!

I thought this might help somebody:
http://docs.sqlalchemy.org/en/latest/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow

@indera pandas does not use the ORM, only sqlalchemy Core (which is what the doc entry there suggests to use for large inserts)
is there any consensus on how to work around this in the meantime?  I'm inserting a several million rows into postgres and it takes forever.  Is CSV / odo the way to go?
@russlamb a practical way to solve this problem is simply to bulk upload. This is someone db specific though, so ``odo`` has solutions for ``postgresl`` (and may be ``mysql``) I think. for something like sqlserver you have to 'do this yourself' (IOW you have to write it). 
For sqlserver I used the FreeTDS driver (http://www.freetds.org/software.html and https://github.com/mkleehammer/pyodbc ) with SQLAlchemy entities which resulted in very fast inserts (20K rows per data frame):

```
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


class DemographicEntity(Base):
    __tablename__ = 'DEMOGRAPHIC'

    patid = db.Column("PATID", db.Text, primary_key=True)
    """
    patid = db.Column("PATID", db.Text, primary_key=True, autoincrement=False, nullable=True)
    birth_date = db.Column("BIRTH_DATE", db.Date)
    birth_time = db.Column("BIRTH_TIME", db.Text(5))
    sex = db.Column("SEX", db.Text(2))

def get_db_url(db_host, db_port, db_name, db_user, db_pass):
    params = parse.quote(
        "Driver={{FreeTDS}};Server={};Port={};"
        "Database={};UID={};PWD={};"
        .format(db_host, db_port, db_name, db_user, db_pass))
    return 'mssql+pyodbc:///?odbc_connect={}'.format(params)

def get_db_pool():
    """
    Create the database engine connection.
    @see http://docs.sqlalchemy.org/en/latest/core/engines.html

    :return: Dialect object which can either be used directly
            to interact with the database, or can be passed to
            a Session object to work with the ORM.
    """
    global DB_POOL

    if DB_POOL is None:
        url = get_db_url(db_host=DB_HOST, db_port=DB_PORT, db_name=DB_NAME,
                         db_user=DB_USER, db_pass=DB_PASS)
        DB_POOL = db.create_engine(url,
                                   pool_size=10,
                                   max_overflow=5,
                                   pool_recycle=3600)

    try:
        DB_POOL.execute("USE {db}".format(db=DB_NAME))
    except db.exc.OperationalError:
        logger.error('Database {db} does not exist.'.format(db=DB_NAME))

    return DB_POOL


def save_frame():
    db_pool = get_db_pool()
    records = df.to_dict(orient='records')
    result = db_pool.execute(entity.__table__.insert(), records)
 
```
>  Is CSV / odo the way to go?

This solution will almost always be faster I think, regardless of the multi-row / chunksize settings. 

But, @russlamb, it is always interesting to hear whether such a multi-row keyword would be an improvement in your case. See eg https://github.com/pandas-dev/pandas/issues/8953#issuecomment-76139975 on a way to easily test this out.

I think there is agreement that we want to have a way to specify this (without necessarily changing the default). So if somebody wants to make a PR for this, that is certainly welcome. 
There was only some discussion on how to add this ability (new keyword vs subclass using OO api).
@jorisvandenbossche The document I linked above mentions "Alternatively, the SQLAlchemy ORM offers the Bulk Operations suite of methods, which provide hooks into subsections of the unit of work process in order to emit Core-level INSERT and UPDATE constructs with a small degree of ORM-based automation."

What I am suggesting is to implement a sqlserver specific version for `to_sql` which under the hood uses the SQLAlchemy ORMs for speedups as in the code I posted above.
This was proposed before. The way you go is to implement an pandas sql
class optimised for a backend. I posted a gist in the past for using
postgres COPY FROM command which is much faster. However something similar
is now available in odo, and built in a more robust way. There isn't much
point IMHO in duplicating work from odo.



On 7 Mar 2017 00:53, "Andrei Sura" <notifications@github.com> wrote:

> @jorisvandenbossche <https://github.com/jorisvandenbossche> The document
> I linked above mentions "Alternatively, the SQLAlchemy ORM offers the Bulk
> Operations suite of methods, which provide hooks into subsections of the
> unit of work process in order to emit Core-level INSERT and UPDATE
> constructs with a small degree of ORM-based automation."
>
> What I am suggesting is to implement a sqlserver specific version for
> "to_sql" which under the hood uses the SQLAlchemy core for speedups.
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/issues/8953#issuecomment-284437587>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AAtYVDXKLuTlsh9ycpMQvU5C0hs_RxuYks5rjCwBgaJpZM4DCjLh>
> .
>

Also noticed you mentioned sqlalchemy could core instead. Unless something
has changed a lot, only sqlalchemy core is used in any case, no orm. If you
want to speed up more than using core you have to go to lower level, db
specific optimisation

On 7 Mar 2017 00:53, "Andrei Sura" <notifications@github.com> wrote:

> @jorisvandenbossche <https://github.com/jorisvandenbossche> The document
> I linked above mentions "Alternatively, the SQLAlchemy ORM offers the Bulk
> Operations suite of methods, which provide hooks into subsections of the
> unit of work process in order to emit Core-level INSERT and UPDATE
> constructs with a small degree of ORM-based automation."
>
> What I am suggesting is to implement a sqlserver specific version for
> "to_sql" which under the hood uses the SQLAlchemy core for speedups.
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/issues/8953#issuecomment-284437587>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AAtYVDXKLuTlsh9ycpMQvU5C0hs_RxuYks5rjCwBgaJpZM4DCjLh>
> .
>

Is this getting fixed/taken care of? As of now inserting pandas dataframes into a SQL db is extremely slow unless it's a toy dataframe.  Let's decide on a solution and push it forward?
@dfernan As mentioned above, you may want to look at the [odo](http://odo.pydata.org/en/latest/perf.html). Using an intermediary CSV file will always be orders of magnitude faster that going through sqlalchemy, no matter what kind of improvements happen here...
@ostrokach, I'm unconvinced that odo's behavior is what the typical Pandas user wants.  Multi-row inserts over ODBC are probably fast enough for most analysts.

To speak for myself, I just spent a few hours switching from the monkey patch above to odo.  Plain pandas runtime was 10+ hours, RBAR.  The monkey patch runs in 2 on the same data set. 
 The odo/CSV route was faster, as expected, but not by enough to make it worth the effort.  I fiddled with CSV conversion issues I didn't care much about, all in the name of avoiding the monkey patch.   I'm importing 250K rows from ~10 mysql and PG DBs into a common area in Postgres, for NLP analysis.  

I'm intimately familiar with the bulk loading approaches odo espouses.  I've used them for years where I'm starting with CSV data.  There are key limitations to them:

1. For the df->CSV->Postgres case, shell access and a scp step is needed to get the CSV on the PG host.  It looks like @mangecoeur has gotten around this with a stream to STDIN.
2. For my purpose (250K rows of comments, with lots of special cases in the text content) I struggled to get CSV parameters right.  I didn't want performance gains badly enough to keep investing in this.

I switch back to the the patch, so I could get on to analysis work.  

I agree with @jorisvandenbossche, @maxgrenderjones.  An option (not a default) to choose this would be immensely useful.  @artemyk's point about dialect.supports_multivalues_insert might even make this a reasonable default.

I'm glad to submit a PR if that'd move this forward.
just to add my experience with odo, it has not worked for MS Sql bulk inserts because of known issue with encoding.  imho m-row insert are good practical solution for most ppl. 
@markschwarz an option to enable this to work faster would be very welcome!
Tracing the queries using sqlite, I do seem to be getting in multi-inserts when using `chunksize`:

```
2017-09-28 00:21:39,007 INFO sqlalchemy.engine.base.Engine INSERT INTO country_hsproduct_year (location_id, product_id, year, export_rca, import_value, cog, export_value, distance, location_level, product_level) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2017-09-28 00:21:39,007 INFO sqlalchemy.engine.base.Engine ((75, 1237, 1996, 1.7283086776733398, 273487116.0, 0.0, 514320160.0, 0.5413745641708374, 'country', '4digit'), (75, 1237, 1997, 1.7167805433273315, 312047528.0, 0.0, 592372864.0, 0.5314807891845703, 'country', '4digit'), (75, 1237, 1998, 1.2120152711868286, 341676961.0, 0.0, 468860608.0, 0.5472233295440674, 'country', '4digit'), (75, 1237, 1999, 1.236651062965393, 334604240.0, 0.0, 440722336.0, 0.5695921182632446, 'country', '4digit'), (75, 1237, 2000, 1.189828872680664, 383555023.0, 0.0, 426384832.0, 0.5794379711151123, 'country', '4digit'), (75, 1237, 2001, 0.9920380115509033, 374157144.0, 0.3462945520877838, 327031392.0, 0.6234743595123291, 'country', '4digit'), (75, 1237, 2002, 1.0405025482177734, 471456583.0, 0.0, 377909376.0, 0.6023964285850525, 'country', '4digit'), (75, 1237, 2003, 1.147829532623291, 552441401.0, 0.0, 481313504.0, 0.5896202325820923, 'country', '4digit')  ... displaying 10 of 100000 total bound parameter sets ...  (79, 1024, 2015, 0.0, None, 0.8785018920898438, 0.0, 0.9823430776596069, 'country', '4digit'), (79, 1025, 1995, 0.0, None, 0.5624096989631653, 0.0, 0.9839603304862976, 'country', '4digit'))
```
(without the monkey patch, that is)
Interestingly, with the monkey patch, it breaks when i give it chunksize of 10^5, but not 10^3. The error is "too many sql variables" on sqlite.
@makmanalp, I haven't traced on PG to check for that behavior, yet, but I nearly always set chunksize on insert.  In my example above, I randomly set it to 5 values between 200-5000.  I didn't see drastic elapsed time differences among those choices, without the monkey patch.  With the patch, elapsed time dropped ~80%.
https://github.com/pandas-dev/pandas/issues/8953#issuecomment-76139975

Is this monkey patch still working? I tried it on MS SQL Server but didn't see an improvement. Also, it throw an exception:

```(pyodbc.Error) ('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')```
@hangyao I think that patch is implementation-specific, one of those things that python DBAPI leaves to the DBAPI driver on how to handle. So it could be faster or not.  RE: the syntax error, unsure about that.
I am thinking about adding the function below in the `/io/sql.py` file on line 507 within the `_engine_builder` function within a new IF clouse on line 521 making the 'new' `_engine_builder` the below snippet. I've tested it briefly on my environment and it works great for MSSQL databases, achieving >100x speed-ups. I haven't tested it on other databases yet.

The thing from witholding me to make a PR is that I think it's more effort to make it neat and safe than just inserting it like below, this might not always be the desired spec and adding a boolean switch, which turns this setting on/off , (e.g. `fast_executemany=True`) in the `to_sql` seemed like a bit too large endeavor to just do without asking, I reckon.

So my questions are:

- Does the below function work and also increase the INSERT speed for PostgreSQL? 

- Does pandas event want this snippet in their source? If so:

- Is it desired to add this function to the default `sql.py` functionality or is there a better place to add this? 



Love to hear some comments.

Source for the answer: https://stackoverflow.com/questions/48006551/speeding-up-pandas-dataframe-to-sql-with-fast-executemany-of-pyodbc/48861231#48861231 

```
@event.listens_for(engine, 'before_cursor_execute')
def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
    if executemany:
        cursor.fast_executemany = True
``` 
```
def _engine_builder(con):
    """
    Returns a SQLAlchemy engine from a URI (if con is a string)
    else it just return con without modifying it.
    """
    global _SQLALCHEMY_INSTALLED
    if isinstance(con, string_types):
        try:
            import sqlalchemy
        except ImportError:
            _SQLALCHEMY_INSTALLED = False
        else:
            con = sqlalchemy.create_engine(con)

    @event.listens_for(engine, 'before_cursor_execute')
    def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
        if executemany:
            cursor.fast_executemany = True
return con
```
@tsktsktsk123 There has recently been a PR merged related to this: https://github.com/pandas-dev/pandas/pull/19664. I didn't yet look in detail to your post, and it's certainly not exactly the same (it uses `supports_multivalues_insert` attribute of the sqlalchemy engine), but just to make sure you are aware of it in case this already helps as well.
That's great news! I haven't looked into the PR  but will compare it this weekend and get back with the results. Thanks for the heads up.
I was just trying 0.23.0 RC2 (on postgresql) and instead of having a performance boost my script got significant slower. The DB query got much faster but measuring the time for `to_sql()` it actually became up to 1.5 times slower (like from 7 to 11 seconds)... 

Not sure the slow down comes from this PR though as I just tested the RC.

Anyone else has experienced same problem?
@schettino72 How much data were you inserting?
Around 30K rows with 10 columns. But really almost anything I try is slower (SQL is faster but overall slower). It is creating a huge SQL statement where there is value interpolation for EVERY value. Something like 

```
 %(user_id_m32639)s, %(event_id_m32639)s, %(colx_m32639)s,
```


Patch:
diff --git a/doc/source/io.rst b/doc/source/io.rst
index 9aff1e54d8e98..fa6a8b1d01530 100644
--- a/doc/source/io.rst
+++ b/doc/source/io.rst
@@ -4989,6 +4989,54 @@ with respect to the timezone.
 timezone aware or naive. When reading ``TIMESTAMP WITH TIME ZONE`` types, pandas
 will convert the data to UTC.
 
+.. _io.sql.method:
+
+Insertion Method
+++++++++++++++++
+
+.. versionadded:: 0.24.0
+
+The parameter ``method`` controls the SQL insertion clause used.
+Possible values are:
+
+- ``None``: Uses standard SQL ``INSERT`` clause (one per row).
+- ``'multi'``: Pass multiple values in a single ``INSERT`` clause.
+  It uses a *special* SQL syntax not supported by all backends.
+  This usually provides better performance for analytic databases
+  like *Presto* and *Redshift*, but has worse performance for
+  traditional SQL backend if the table contains many columns.
+  For more information check the SQLAlchemy `documention
+  <http://docs.sqlalchemy.org/en/latest/core/dml.html#sqlalchemy.sql.expression.Insert.values.params.*args>`__.
+- callable with signature ``(pd_table, conn, keys, data_iter)``:
+  This can be used to implement a more performant insertion method based on
+  specific backend dialect features.
+
+Example of a callable using PostgreSQL `COPY clause
+<https://www.postgresql.org/docs/current/static/sql-copy.html>`__::
+
+  # Alternative to_sql() *method* for DBs that support COPY FROM
+  import csv
+  from io import StringIO
+
+  def psql_insert_copy(table, conn, keys, data_iter):
+      # gets a DBAPI connection that can provide a cursor
+      dbapi_conn = conn.connection
+      with dbapi_conn.cursor() as cur:
+          s_buf = StringIO()
+          writer = csv.writer(s_buf)
+          writer.writerows(data_iter)
+          s_buf.seek(0)
+
+          columns = ', '.join('"{}"'.format(k) for k in keys)
+          if table.schema:
+              table_name = '{}.{}'.format(table.schema, table.name)
+          else:
+              table_name = table.name
+
+          sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
+              table_name, columns)
+          cur.copy_expert(sql=sql, file=s_buf)
+
 Reading Tables
 ''''''''''''''
 
diff --git a/doc/source/whatsnew/v0.24.0.rst b/doc/source/whatsnew/v0.24.0.rst
index 018820b09dba4..6cb08afadec31 100644
--- a/doc/source/whatsnew/v0.24.0.rst
+++ b/doc/source/whatsnew/v0.24.0.rst
@@ -373,6 +373,7 @@ Other Enhancements
 - :meth:`DataFrame.between_time` and :meth:`DataFrame.at_time` have gained the an ``axis`` parameter (:issue:`8839`)
 - The ``scatter_matrix``, ``andrews_curves``, ``parallel_coordinates``, ``lag_plot``, ``autocorrelation_plot``, ``bootstrap_plot``, and ``radviz`` plots from the ``pandas.plotting`` module are now accessible from calling :meth:`DataFrame.plot` (:issue:`11978`)
 - :class:`IntervalIndex` has gained the :attr:`~IntervalIndex.is_overlapping` attribute to indicate if the ``IntervalIndex`` contains any overlapping intervals (:issue:`23309`)
+- :func:`pandas.DataFrame.to_sql` has gained the ``method`` argument to control SQL insertion clause. See the :ref:`insertion method <io.sql.method>` section in the documentation. (:issue:`8953`)
 
 .. _whatsnew_0240.api_breaking:
 
diff --git a/pandas/core/generic.py b/pandas/core/generic.py
index 63d9b5265cdc7..3c28fef024b79 100644
--- a/pandas/core/generic.py
+++ b/pandas/core/generic.py
@@ -2386,7 +2386,7 @@ def to_msgpack(self, path_or_buf=None, encoding='utf-8', **kwargs):
                                   **kwargs)
 
     def to_sql(self, name, con, schema=None, if_exists='fail', index=True,
-               index_label=None, chunksize=None, dtype=None):
+               index_label=None, chunksize=None, dtype=None, method=None):
         """
         Write records stored in a DataFrame to a SQL database.
 
@@ -2424,6 +2424,17 @@ def to_sql(self, name, con, schema=None, if_exists='fail', index=True,
             Specifying the datatype for columns. The keys should be the column
             names and the values should be the SQLAlchemy types or strings for
             the sqlite3 legacy mode.
+        method : {None, 'multi', callable}, default None
+            Controls the SQL insertion clause used:
+
+            * None : Uses standard SQL ``INSERT`` clause (one per row).
+            * 'multi': Pass multiple values in a single ``INSERT`` clause.
+            * callable with signature ``(pd_table, conn, keys, data_iter)``.
+
+            Details and a sample callable implementation can be found in the
+            section :ref:`insert method <io.sql.method>`.
+
+            .. versionadded:: 0.24.0
 
         Raises
         ------
@@ -2505,7 +2516,7 @@ def to_sql(self, name, con, schema=None, if_exists='fail', index=True,
         from pandas.io import sql
         sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
                    index=index, index_label=index_label, chunksize=chunksize,
-                   dtype=dtype)
+                   dtype=dtype, method=method)
 
     def to_pickle(self, path, compression='infer',
                   protocol=pkl.HIGHEST_PROTOCOL):
diff --git a/pandas/io/sql.py b/pandas/io/sql.py
index e54d29148c6d0..6093c6c3fd0fc 100644
--- a/pandas/io/sql.py
+++ b/pandas/io/sql.py
@@ -8,6 +8,7 @@
 
 from contextlib import contextmanager
 from datetime import date, datetime, time
+from functools import partial
 import re
 import warnings
 
@@ -395,7 +396,7 @@ def read_sql(sql, con, index_col=None, coerce_float=True, params=None,
 
 
 def to_sql(frame, name, con, schema=None, if_exists='fail', index=True,
-           index_label=None, chunksize=None, dtype=None):
+           index_label=None, chunksize=None, dtype=None, method=None):
     """
     Write records stored in a DataFrame to a SQL database.
 
@@ -429,6 +430,17 @@ def to_sql(frame, name, con, schema=None, if_exists='fail', index=True,
         Optional specifying the datatype for columns. The SQL type should
         be a SQLAlchemy type, or a string for sqlite3 fallback connection.
         If all columns are of the same type, one single value can be used.
+    method : {None, 'multi', callable}, default None
+        Controls the SQL insertion clause used:
+
+        - None : Uses standard SQL ``INSERT`` clause (one per row).
+        - 'multi': Pass multiple values in a single ``INSERT`` clause.
+        - callable with signature ``(pd_table, conn, keys, data_iter)``.
+
+        Details and a sample callable implementation can be found in the
+        section :ref:`insert method <io.sql.method>`.
+
+        .. versionadded:: 0.24.0
     """
     if if_exists not in ('fail', 'replace', 'append'):
         raise ValueError("'{0}' is not valid for if_exists".format(if_exists))
@@ -443,7 +455,7 @@ def to_sql(frame, name, con, schema=None, if_exists='fail', index=True,
 
     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
                       index_label=index_label, schema=schema,
-                      chunksize=chunksize, dtype=dtype)
+                      chunksize=chunksize, dtype=dtype, method=method)
 
 
 def has_table(table_name, con, schema=None):
@@ -568,8 +580,29 @@ def create(self):
         else:
             self._execute_create()
 
-    def insert_statement(self):
-        return self.table.insert()
+    def _execute_insert(self, conn, keys, data_iter):
+        """Execute SQL statement inserting data
+
+        Parameters
+        ----------
+        conn : sqlalchemy.engine.Engine or sqlalchemy.engine.Connection
+        keys : list of str
+           Column names
+        data_iter : generator of list
+           Each item contains a list of values to be inserted
+        """
+        data = [dict(zip(keys, row)) for row in data_iter]
+        conn.execute(self.table.insert(), data)
+
+    def _execute_insert_multi(self, conn, keys, data_iter):
+        """Alternative to _execute_insert for DBs support multivalue INSERT.
+
+        Note: multi-value insert is usually faster for analytics DBs
+        and tables containing a few columns
+        but performance degrades quickly with increase of columns.
+        """
+        data = [dict(zip(keys, row)) for row in data_iter]
+        conn.execute(self.table.insert(data))
 
     def insert_data(self):
         if self.index is not None:
@@ -612,11 +645,18 @@ def insert_data(self):
 
         return column_names, data_list
 
-    def _execute_insert(self, conn, keys, data_iter):
-        data = [dict(zip(keys, row)) for row in data_iter]
-        conn.execute(self.insert_statement(), data)
+    def insert(self, chunksize=None, method=None):
+
+        # set insert method
+        if method is None:
+            exec_insert = self._execute_insert
+        elif method == 'multi':
+            exec_insert = self._execute_insert_multi
+        elif callable(method):
+            exec_insert = partial(method, self)
+        else:
+            raise ValueError('Invalid parameter `method`: {}'.format(method))
 
-    def insert(self, chunksize=None):
         keys, data_list = self.insert_data()
 
         nrows = len(self.frame)
@@ -639,7 +679,7 @@ def insert(self, chunksize=None):
                     break
 
                 chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
-                self._execute_insert(conn, keys, chunk_iter)
+                exec_insert(conn, keys, chunk_iter)
 
     def _query_iterator(self, result, chunksize, columns, coerce_float=True,
                         parse_dates=None):
@@ -1085,7 +1125,8 @@ def read_query(self, sql, index_col=None, coerce_float=True,
     read_sql = read_query
 
     def to_sql(self, frame, name, if_exists='fail', index=True,
-               index_label=None, schema=None, chunksize=None, dtype=None):
+               index_label=None, schema=None, chunksize=None, dtype=None,
+               method=None):
         """
         Write records stored in a DataFrame to a SQL database.
 
@@ -1115,7 +1156,17 @@ def to_sql(self, frame, name, if_exists='fail', index=True,
             Optional specifying the datatype for columns. The SQL type should
             be a SQLAlchemy type. If all columns are of the same type, one
             single value can be used.
+        method : {None', 'multi', callable}, default None
+            Controls the SQL insertion clause used:
+
+            * None : Uses standard SQL ``INSERT`` clause (one per row).
+            * 'multi': Pass multiple values in a single ``INSERT`` clause.
+            * callable with signature ``(pd_table, conn, keys, data_iter)``.
+
+            Details and a sample callable implementation can be found in the
+            section :ref:`insert method <io.sql.method>`.
 
+            .. versionadded:: 0.24.0
         """
         if dtype and not is_dict_like(dtype):
             dtype = {col_name: dtype for col_name in frame}
@@ -1131,7 +1182,7 @@ def to_sql(self, frame, name, if_exists='fail', index=True,
                          if_exists=if_exists, index_label=index_label,
                          schema=schema, dtype=dtype)
         table.create()
-        table.insert(chunksize)
+        table.insert(chunksize, method=method)
         if (not name.isdigit() and not name.islower()):
             # check for potentially case sensitivity issues (GH7815)
             # Only check when name is not a number and name is not lower case
@@ -1442,7 +1493,8 @@ def _fetchall_as_list(self, cur):
         return result
 
     def to_sql(self, frame, name, if_exists='fail', index=True,
-               index_label=None, schema=None, chunksize=None, dtype=None):
+               index_label=None, schema=None, chunksize=None, dtype=None,
+               method=None):
         """
         Write records stored in a DataFrame to a SQL database.
 
@@ -1471,7 +1523,17 @@ def to_sql(self, frame, name, if_exists='fail', index=True,
             Optional specifying the datatype for columns. The SQL type should
             be a string. If all columns are of the same type, one single value
             can be used.
+        method : {None, 'multi', callable}, default None
+            Controls the SQL insertion clause used:
+
+            * None : Uses standard SQL ``INSERT`` clause (one per row).
+            * 'multi': Pass multiple values in a single ``INSERT`` clause.
+            * callable with signature ``(pd_table, conn, keys, data_iter)``.
+
+            Details and a sample callable implementation can be found in the
+            section :ref:`insert method <io.sql.method>`.
 
+            .. versionadded:: 0.24.0
         """
         if dtype and not is_dict_like(dtype):
             dtype = {col_name: dtype for col_name in frame}
@@ -1486,7 +1548,7 @@ def to_sql(self, frame, name, if_exists='fail', index=True,
                             if_exists=if_exists, index_label=index_label,
                             dtype=dtype)
         table.create()
-        table.insert(chunksize)
+        table.insert(chunksize, method)
 
     def has_table(self, name, schema=None):
         # TODO(wesm): unused?


Test patch:
diff --git a/pandas/tests/io/test_sql.py b/pandas/tests/io/test_sql.py
index eeeb55cb8e70c..c346103a70c98 100644
--- a/pandas/tests/io/test_sql.py
+++ b/pandas/tests/io/test_sql.py
@@ -375,12 +375,16 @@ def _read_sql_iris_named_parameter(self):
         iris_frame = self.pandasSQL.read_query(query, params=params)
         self._check_iris_loaded_frame(iris_frame)
 
-    def _to_sql(self):
+    def _to_sql(self, method=None):
         self.drop_table('test_frame1')
 
-        self.pandasSQL.to_sql(self.test_frame1, 'test_frame1')
+        self.pandasSQL.to_sql(self.test_frame1, 'test_frame1', method=method)
         assert self.pandasSQL.has_table('test_frame1')
 
+        num_entries = len(self.test_frame1)
+        num_rows = self._count_rows('test_frame1')
+        assert num_rows == num_entries
+
         # Nuke table
         self.drop_table('test_frame1')
 
@@ -434,6 +438,25 @@ def _to_sql_append(self):
         assert num_rows == num_entries
         self.drop_table('test_frame1')
 
+    def _to_sql_method_callable(self):
+        check = []  # used to double check function below is really being used
+
+        def sample(pd_table, conn, keys, data_iter):
+            check.append(1)
+            data = [dict(zip(keys, row)) for row in data_iter]
+            conn.execute(pd_table.table.insert(), data)
+        self.drop_table('test_frame1')
+
+        self.pandasSQL.to_sql(self.test_frame1, 'test_frame1', method=sample)
+        assert self.pandasSQL.has_table('test_frame1')
+
+        assert check == [1]
+        num_entries = len(self.test_frame1)
+        num_rows = self._count_rows('test_frame1')
+        assert num_rows == num_entries
+        # Nuke table
+        self.drop_table('test_frame1')
+
     def _roundtrip(self):
         self.drop_table('test_frame_roundtrip')
         self.pandasSQL.to_sql(self.test_frame1, 'test_frame_roundtrip')
@@ -1193,7 +1216,7 @@ def setup_connect(self):
             pytest.skip(
                 "Can't connect to {0} server".format(self.flavor))
 
-    def test_aread_sql(self):
+    def test_read_sql(self):
         self._read_sql_iris()
 
     def test_read_sql_parameter(self):
@@ -1217,6 +1240,12 @@ def test_to_sql_replace(self):
     def test_to_sql_append(self):
         self._to_sql_append()
 
+    def test_to_sql_method_multi(self):
+        self._to_sql(method='multi')
+
+    def test_to_sql_method_callable(self):
+        self._to_sql_method_callable()
+
     def test_create_table(self):
         temp_conn = self.connect()
         temp_frame = DataFrame(
@@ -1930,6 +1959,36 @@ def test_schema_support(self):
             res2 = pdsql.read_table('test_schema_other2')
             tm.assert_frame_equal(res1, res2)
 
+    def test_copy_from_callable_insertion_method(self):
+        # GH 8953
+        # Example in io.rst found under _io.sql.method
+        # not available in sqlite, mysql
+        def psql_insert_copy(table, conn, keys, data_iter):
+            # gets a DBAPI connection that can provide a cursor
+            dbapi_conn = conn.connection
+            with dbapi_conn.cursor() as cur:
+                s_buf = compat.StringIO()
+                writer = csv.writer(s_buf)
+                writer.writerows(data_iter)
+                s_buf.seek(0)
+
+                columns = ', '.join('"{}"'.format(k) for k in keys)
+                if table.schema:
+                    table_name = '{}.{}'.format(table.schema, table.name)
+                else:
+                    table_name = table.name
+
+                sql_query = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
+                    table_name, columns)
+                cur.copy_expert(sql=sql_query, file=s_buf)
+
+        expected = DataFrame({'col1': [1, 2], 'col2': [0.1, 0.2],
+                              'col3': ['a', 'n']})
+        expected.to_sql('test_copy_insert', self.conn, index=False,
+                        method=psql_insert_copy)
+        result = sql.read_sql_table('test_copy_insert', self.conn)
+        tm.assert_frame_equal(result, expected)
+
 
 @pytest.mark.single
 class TestMySQLAlchemy(_TestMySQLAlchemy, _TestSQLAlchemy):