Thanks Mike - will the full profile ASAP. On Monday, March 7, 2016, Mike Bayer <clas...@zzzcomputing.com> wrote:
> What will make inserts slow is network as well as indexes. If you aren't > comparing your script to some other backend then there's probably a whole > lot of other things that can get in the way like unicode encoding, large > columns or other surprises in the model. If you run the full profile > version from the FAQ (please refer to the 1.0 documentation... Also you're > on sqla 1.0 right?) I can identify where the time is spent with a full > Python profile. > > On Mar 7, 2016, at 7:12 PM, jbrownst...@clearcosthealth.com > <javascript:_e(%7B%7D,'cvml','jbrownst...@clearcosthealth.com');> wrote: > > 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 sqlalchemy+unsubscr...@googlegroups.com > <javascript:_e(%7B%7D,'cvml','sqlalchemy%2bunsubscr...@googlegroups.com');> > . > To post to this group, send email to sqlalchemy@googlegroups.com > <javascript:_e(%7B%7D,'cvml','sqlalchemy@googlegroups.com');>. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/2Safb9uhgjg/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com > <javascript:_e(%7B%7D,'cvml','sqlalchemy%2bunsubscr...@googlegroups.com');> > . > To post to this group, send email to sqlalchemy@googlegroups.com > <javascript:_e(%7B%7D,'cvml','sqlalchemy@googlegroups.com');>. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
