kuroの覚え書き

96の個人的覚え書き

UPDATEの速度

SQLAlchemyでUPDATEを行うときの書式は

for target in session.query(tableA).filter(tableA.gene.in_(session.query(tableB.gene))).all():
     target.Gene_comments = target.Gene_comments + 'commentXXX'
     session.commit()

まあこんな感じ。色々関係ない要素も入っているqueryだが、このような結構面倒くさい処理で項目を選んでアップデートをかけようとすると、このforループではあまりにもおそすぎる。
SQLAlchemyのありがたみである統一書式を捨てて、SQLごとの方言をそのまま使うほうが断然高速なのだ。

updt = text("""UPDATE tableA SET Gene_comments = Gene_comments || ' commentXXX' WHERE gene IN(SELECT gene FROM tableB)""")
conn = engine.connect()
conn.execute(updt)

スマートさには欠けるがこっちのほうが断然速いのだ。


ただ、どうもこの方式はSQLiteでは速いのだがMySQLだとイマイチっぽい。何が問題なんだろうか。
やはりこれはMySQLの設定に問題があってエラーで停止してしまっているためであるようだ。
MySQLWorkbenchで同じクエリを投げてみたところ

ERROR 2013 (HY000) at line : Lost connection to MySQL server during query

というエラーで停止していた。
これを回避する措置をサーバ側、クライアント側双方に施さねばならないらしい。

とりあえずクライアント側のtime out関係の設定値を3600秒と十分に大きく取ってみたところ、今度は

ERROR 2015  (HY000): Lock wait timeout exceeded; try restarting transaction

こういうエラーが出て止まる。
これはどうやら前に停止したときにtransactionの途中だったためにロックされたままになっていたためのようで、手動でthreadをkillしてやらねばならないようだ。