I am trying to read a small table from SQL and I'm looking into switching over to SQLAlchemy from pyodbc to be able to use pd.to_sql()
When I compare the two, the sql alchemy is much slower.
s_py = """\
import pandas as pd
import pyodbc
cxn = pyodbc.connect('DRIVER={SQL SERVER};SERVER=.\;DATABASE=PPIS;UID=sa;PWD=pwd')
"""
s_alch = """\
import pandas as pd
import sqlalchemy
cxn = sqlalchemy.create_engine("mssql+pyodbc://sa:pwd@./PPIS?driver=SQL+Server")
"""
timeit.timeit('pd.read_sql("SELECT * FROM Operators", cxn)',setup=s_py, number=100)
Out[21]: 0.18496091418973037
timeit.timeit('pd.read_sql("SELECT * FROM Operators", cxn)',setup=s_alch, number=100)
Out[23]: 4.407356934717654
I've seen this that talks about inserting rows. But why would the read be so much slower?
