How to have something like fast_executemany with SQLAlchemy 1.3.18 with Sybase to improve the speed of insert into?

Question

I've made a program to insert into a Sybase database in Python, i use pandas 1.0.4 and SQLAlchemy 1.3.18.

I've made the link between sybase and my script, but actually the speed of insert into tables is really really slow (10 minutes for 30k rows...)

In SQL Server, with the same code (except the parameter fast_executemany=True in create_engine, and parameters method='multi', chunksize = 500 for dataframe.to_sql()), i've made the insert into 3 secondes.

DO you have any idea to fix this?

Have a nice day !

Using `method='multi'` defeats the purpose of using `fast_executemany=True`. The former makes Pandas issue separate `INSERT ... VALUES (...), (...), ...` statements (which is why you need a suitable chunksize), while the latter speeds up `executemany()` (that Pandas/SQLA defaults to). — Ilja Everilä, Aug 07 '20 at 12:29
Ok thanks for this. But actually it don't fix my problem. I don' tunderstand why method='multi' and chunksize working on SQL Server and don't work on Sybase — Neal Poidras, Aug 07 '20 at 13:09
Does Sybase support `VALUES (...), (...), ...` clause in INSERT (specifically multiple tuples)? I've 0 experience with Sybase, unfortunately, but this seems to hint that it (some version of "it") does not: https://stackoverflow.com/questions/24635327/inserting-multiple-rows-in-sybase-ase — Ilja Everilä, Aug 07 '20 at 13:21
I've juste seen the link you've sent. So it should be better to make df.read_sql_query and put a big request with Union ALL created before with a loop. But it's really boring :( — Neal Poidras, Aug 07 '20 at 14:30
Related: https://stackoverflow.com/questions/62678036/failing-bulk-insert-data-from-pandas-dataframe-into-sybase-database-table-using — Ilja Everilä, Aug 10 '20 at 05:38

score 2 · Answer 1 · answered Aug 10 '20 at 10:49

2

The external SAP ASE (Sybase) dialect does support fast_executemany if you use the SAP ASE ODBC driver.

answered Aug 10 '20 at 10:49

Gord Thompson

116,920
32
215
418

Out of curiosity, how big a difference does it make compared to vanilla odbc `executemany`? I remember it having a rather major impact in SQL Server with the odbc driver. – Ilja Everilä Aug 10 '20 at 17:56
2

The improvement in performance for SAP ASE (Sybase) is comparable to that for SQL Server. :) – Gord Thompson Aug 10 '20 at 18:58

Ilja Everilä · Accepted Answer · 2020-08-12T12:15:29.910

It looks like Sybase ASE does not support a multiple values VALUES clause in INSERT. You could form a UNION of SELECT statements, if you need to insert multiple values in a single statement instead of using executemany to simply issue many INSERT statements:

from sqlalchemy import union_all, select, literal

def sybase_insert(sqltable, conn, keys, data_iter):
    sel = union_all(*[select([literal(v) for v in row]) for row in data_iter])
    ins = sqltable.table.insert().from_select(keys, sel)
    conn.execute(ins)

Pass the function as the method when calling to_sql():

In [11]: pd.DataFrame({"A": range(3)}).to_sql("foo", con=engine, method=sybase_insert)
2020-08-10 08:29:44,474 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("foo")
2020-08-10 08:29:44,474 INFO sqlalchemy.engine.base.Engine ()
2020-08-10 08:29:44,475 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("foo")
2020-08-10 08:29:44,475 INFO sqlalchemy.engine.base.Engine ()
2020-08-10 08:29:44,477 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE foo (
        "index" BIGINT, 
        "A" BIGINT
)


2020-08-10 08:29:44,477 INFO sqlalchemy.engine.base.Engine ()
2020-08-10 08:29:44,477 INFO sqlalchemy.engine.base.Engine COMMIT
2020-08-10 08:29:44,478 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_foo_index ON foo ("index")
2020-08-10 08:29:44,478 INFO sqlalchemy.engine.base.Engine ()
2020-08-10 08:29:44,478 INFO sqlalchemy.engine.base.Engine COMMIT
2020-08-10 08:29:44,479 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-08-10 08:29:44,481 INFO sqlalchemy.engine.base.Engine INSERT INTO foo ("index", "A") SELECT ? AS anon_1, ? AS anon_2 UNION ALL SELECT ? AS anon_3, ? AS anon_4 UNION ALL SELECT ? AS anon_5, ? AS anon_6
2020-08-10 08:29:44,481 INFO sqlalchemy.engine.base.Engine (0, 0, 1, 1, 2, 2)
2020-08-10 08:29:44,481 INFO sqlalchemy.engine.base.Engine COMMIT

Thank you for you answer. Could you please tell me wich librarie you're using on this part of code : def sybase_insert(sqltable, conn, keys, data_iter): sel = union_all(*[select([literal(v) for v in row]) for row in data_iter]) ins = sqltable.table.insert().from_select(keys, sel) conn.execute(ins) wich librarie is for sqltable? Thanks you and have a good day — Neal Poidras, Aug 10 '20 at 07:37

How to have something like fast_executemany with SQLAlchemy 1.3.18 with Sybase to improve the speed of insert into?

2 Answers2