I've run sqlalchemy on MySQL dbs and never had a problem with performance, but when I started writing an app that connected to Microsoft SQL Server 2014 via pyodbc I ran into a serious performance problem. I'm running the app on my laptop and the database server IS in a VPN but my internet is fast enough and the vpn connection is reportedly fast.
I decided to run the performance script via the http://docs.sqlalchemy.org/en/rel_0_8/faq.html (copied below for convenience) The FAQ reports being able to insert ***100,000 rows in ~14seconds** *when I run the script below I am inserting ***1000 rows in ~100 seconds*** Is there some additional profiling that I can do? Is there something wrong with my connection set up? *OUTPUT:* before init engine: 0.0750000476837 session remove: 0.0759999752045 session configure: 0.0770001411438 drop all: 1.02800011635 create all: 1.28400015831 init complete 1.28600001335 flush complete 0.192000150681 // total rows added: [0] flush complete 115.700000048 // total rows added: [1000] flush complete 220.65199995 // total rows added: [2000] CODE import time from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, create_engine from sqlalchemy.orm import scoped_session, sessionmaker import urllib Base = declarative_base() DBSession = scoped_session(sessionmaker()) engine = None server = "xx.xxx.xx.xx,xxxxx" database = "test" raw_connection_string = "DRIVER={SQL Server Native Client 11.0};Server=%s;Database=%s;TDS_Version=8.0;Trusted_Connection=yes;" % (server, database) quoted = urllib.quote_plus(raw_connection_string) connection_string = "mssql+pyodbc:///?odbc_connect={}".format(quoted) ECHO = None # "debug" class Customer(Base): __tablename__ = "customer" id = Column(Integer, primary_key=True) name = Column(String(255)) def init_sqlalchemy(dbname='jason'): global engine t0 = time.time() engine = create_engine(connection_string, echo=ECHO) print 'engine: %s' % str(time.time() - t0) DBSession.remove() print 'session remove: %s' % str(time.time() - t0) DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False) print 'session configure: %s' % str(time.time() - t0) Base.metadata.drop_all(engine) print 'drop all: %s' % str(time.time() - t0) Base.metadata.create_all(engine) print 'create all: %s' % str(time.time() - t0) def test_sqlalchemy_orm(n=100000): t0 = time.time() print 'before init' init_sqlalchemy() print 'init complete %s' % str(time.time() - t0) t0 = time.time() for i in range(n): customer = Customer() customer.name = 'NAME ' + str(i) DBSession.add(customer) if i % 1000 == 0: DBSession.flush() print 'flush complete %s // total rows added: [%s]' % (str(time.time() - t0), i) DBSession.commit() print("SQLAlchemy ORM: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs") if __name__ == '__main__': test_sqlalchemy_orm(100000) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
