SQLAlchemy批量插入比构建原始SQL慢。

Question

SQLAlchemy批量插入比构建原始SQL慢。

pythonsql-serverperformancesqlalchemybulkinsert

5

我正在阅读关于sqlalchemy批量插入性能的这篇文章。我尝试了基准测试中指定的各种方法 - SQLAlchemy ORM bulk_insert_mappings()，SQLAlchemy Core。遗憾的是，对于插入1000行数据，所有这些方法都需要约1分钟的时间来完成。速度非常慢。我还尝试了这里指定的方法 - 这要求我构建一个大型的SQL语句，例如：

INSERT INTO mytable (col1, col2, col3)
VALUES (1,2,3), (4,5,6) ..... --- up to 1000 of these

对于这个原始SQL的插入操作大概是这样的：

MySession.execute('''
insert into MyTable (e, l, a)
values {}
'''.format(",".join(my_insert_str)))

采用这种方法，我将性能提高了50倍以上，插入10000条数据只需10-11秒。

以下是使用内置库的代码。

class MyClass(Base):
    __tablename__ = "MyTable"
    e = Column(String(256), primary_key=True)
    l = Column(String(6))
    a = Column(String(20), primary_key=True)

    def __repr__(self):
        return self.e + " " + self.a+ " " + self.l

.......

        dict_list = []
        for i, row in chunk.iterrows():

            dict_list += [{"e" : row["e"], "l" : l, "a" : a}]

        MySession.execute(
            Myclass.__table__.insert(),
            dict_list
        )

这是我连接数据库的方法。

    params = urllib.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=servername;DATABASE=dbname;UID=user;PWD=pass")
    engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params )
    MySession.configure(bind=engine, autoflush=False, expire_on_commit=False)

我的设置有问题吗，导致性能下降如此严重？我尝试了不同的数据库驱动程序——pyodbc和pymssql。无论我尝试什么，都无法接近文章中所声称的数字：

SQLAlchemy ORM: Total time for 100000 records 2.192882061 secs
SQLAlchemy ORM pk given: Total time for 100000 records 1.41679310799 secs
SQLAlchemy ORM bulk_save_objects(): Total time for 100000 records 0.494568824768 secs
SQLAlchemy ORM bulk_insert_mappings(): Total time for 100000 records 0.325763940811 secs
SQLAlchemy Core: Total time for 100000 records 0.239127874374 secs
sqlite3: Total time for 100000 records 0.124729156494 sec

我正在连接到 MS SQL Server 2008。如果我漏掉了其他细节，请告诉我。

使用原始 SQL 方法存在 SQL 注入的安全问题。因此，如果您有解决此问题的建议，那么也会非常有帮助：）。

- Anton Belev

我建议你阅读：http://seldo.com/weblog/2011/08/11/orm_is_an_antipattern，只是提一下 ;) - Cédric Julien

我更倾向于建议阅读 https://github.com/mkleehammer/pyodbc/issues/120，这是一个关于pyodbc和大型插入操作极其缓慢的“长期存在的”问题。ORM有其适用之处，但不包括大量数据处理。 - Ilja Everilä

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- univerio · Accepted Answer

你正在做什么。

MySession.execute(
    Myclass.__table__.insert(),
    dict_list
)

这个使用executemany()。它与INSERT INTO ... VALUES ...不同。要使用VALUES，请执行以下操作：

使用executemany()不同于使用INSERT INTO ... VALUES ...。如果您想使用VALUES，请执行以下操作：

MySession.execute(
    Myclass.__table__.insert().values(dict_list)
)

顺便提一下，使用参数可以解决SQL注入问题：

MySession.execute('''
insert into MyTable (e, l, a)
values (?, ?, ?), (?, ?, ?), ...
''', params)

这里的要点是你没有比较等效的结构。在SQLAlchemy生成的查询中，你没有使用VALUES，但在文本SQL中使用了它，并且在文本SQL中没有使用参数化，但在SQLAlchemy生成的查询中使用了参数化。如果打开执行的SQL语句的日志记录，你就会看到不同之处。