*Hello M. Bayer
Ok i understand now what it is strange
My configuration
I m on windows xp pro sp3
Python is 2.5.2
SqlAlchemy is 0.5.6
Dell optiplex 755
Intel core 2 duo e8300
2go ram
**
I dont pass any paremeters in create_engine, is it wrong?*
chaine =
'mysql://'+ParametresDeConnection["Identifiant"]+":"+ParametresDeConnection["MotDePasse"]+'@'+ParametresDeConnection['Ip']+'/'+ParametresDeConnection["BaseDeDonnees"]
engine = create_engine(chaine)
*
This is the final part of my declaration*
class Query(Query):
def __init__(self, *arg, **kw):
self._populate_existing = True
super(Query, self).__init__(*arg, **kw)
Session = scoped_session(sessionmaker(autocommit=True, bind=engine,
query_cls=Query))
session = Session()
import MySQLdb
db = MySQLdb.connection(host="192.168.0.110", user="apm", passwd="apm",
db="azeane")
print "AvecMySQlDB"
xref = time.time()
for x in xrange(100):
db.query("""UPDATE utilisateur SET Dispo="+str(x%2)+" WHERE
IdUtilisateur=1""")
r = db.store_result()
print "Update 1000 MySQLdb", time.time()-xref
xreftotal = time.time()
for x in xrange(100):
session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==1).update({'Dispo':x%2},
False)
print "Update 1000 SqlAlchemy", time.time()-xref
*As u can see, i m not in transactional mode.* *I add a new benchmark and i
execute it with local database and tunisia database
Local results
Update 1000 MySQLdb 0.0160000324249
Update 1000 SqlAlchemy 0.235000133514
Tunisia results
Update 1000 MySQLdb 0.0160000324249
Update 1000 SqlAlchemy 31.0309998989
It s crazy ^^
I don t know why my station is slow? Do u have an idea?*
2009/10/1 Michael Bayer <mike...@zzzcomputing.com>
>
> Christian Démolis wrote:
> > Maybe because the database is in Tunisia and my Computer in France.
> > I don t use sqlite, i use MySQL.
> > I just did a test on internet in Tunisia, 39kbits/sec upload and
> > 417kbits/sec
>
> right but, you had these results:
>
> MySQLdb - .09 seconds
> ORM - .3 seconds
>
> so, network overhead of sending "update " string to tunisia, is at most
> .09 seconds. SQLAlchemy also pulls the cursor.rowcount back, so that
> perhaps adds network overhead as well (and you would see this in the
> profiling results), although that should be tiny. so .21 seconds of
> overhead approximately would appear to be spent locally. whereas on my
> system within .21 seconds I can issue that same amount of work a few
> thousand times. the simple ORM examples you have should not be issuing
> any other SQL statements.
>
>
> >
> > 2009/10/1 Michael Bayer <mike...@zzzcomputing.com>
> >
> >>
> >> Christian Démolis wrote:
> >> > With debug mode it seems to take 0.15 second, my timer print 0.45 s
> >> > i don t know why this difference?
> >> >
> >> > 2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50
> >> > UPDATE
> >> > utilisateur SET `Dispo`=%s WHERE utilisateur.`IdU
> >> > tilisateur` = %s
> >> > 2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50
> >> [0,
> >> > 1L]
> >> > 2009-10-01 17:00:38,743 INFO sqlalchemy.engine.base.Engine.0x...7f50
> >> > COMMIT
> >> > With ORM force update 0.453000068665
> >>
> >> On my workstation which is an intel mac, I can run 1000
> >> Session.execute("update...") against a sqlite memory db statements in
> >> .15
> >> seconds, using the simple time.time() approach to measure. The sqlite
> >> cursor directly, which is against an empty table so is insanely fast,
> >> can
> >> run 1000 in .01 seconds, so that is .14 seconds of overhead within SQLA.
> >>
> >> In your case, you're getting .2-.3 seconds of overhead just for *one*
> >> statement, subtracting what your database call takes natively. Meaning
> >> that while I can execute around 8000 Session.execute() statements per
> >> second, you can execute less than five. That seems very strange.
> >>
> >>
> >>
> >>
> >> >
> >> > 2009/10/1 Michael Bayer <mike...@zzzcomputing.com>
> >> >
> >> >>
> >> >> Michael Bayer wrote:
> >> >> >
> >> >> > Christian Démolis wrote:
> >> >> >> Hello,
> >> >> >>
> >> >> >> I tried all the method to compare the different methods :
> >> >>
> >> >>
> >> >> Here's a decorator I would advise using:
> >> >>
> >> >>
> >> >>
> >>
> http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677
> >> >>
> >> >> Also I would advise testing this as well. Session.execute() creates
> >> a
> >> >> text() construct which does some regular expression matching that you
> >> >> don't need:
> >> >>
> >> >> engine.execute("update table set foo=bar")
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> >>
> >> >> >> *TEST CODE*
> >> >> >>
> >> >> >> xref = time.time()
> >> >> >> self.UtilisateurCourant.Dispo = 1
> >> >> >> session.merge(self.UtilisateurCourant, dont_load=True)
> >> >> >> session.flush()
> >> >> >> print "With ORM dont_load", time.time()-xref
> >> >> >>
> >> >> >> xref = time.time()
> >> >> >>
> >> >> >>
> >> >>
> >>
> session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==self.UtilisateurCourant.IdUtilisateur).update({'Dispo':0},
> >> >> >> False)
> >> >> >> print "With ORM force update", time.time()-xref
> >> >> >>
> >> >> >> xref = time.time()
> >> >> >> if self.UtilisateurCourant.IdUtilisateur:
> >> >> >> session.execute("UPDATE utilisateur SET Dispo=0
> >> WHERE
> >> >> >> IdUtilisateur="+str(self.UtilisateurCourant.IdUtilisateur))
> >> >> >> print "With ORM pure SQL", time.time()-xref
> >> >> >>
> >> >> >> import MySQLdb
> >> >> >> db = MySQLdb.connection(host="192.168.45.28",
> >> user="apm",
> >> >> >> passwd="apm", db="test_christian")
> >> >> >> xref = time.time()
> >> >> >> db.query("""UPDATE utilisateur SET Dispo=1 WHERE
> >> >> >> IdUtilisateur=1""")
> >> >> >> r = db.store_result()
> >> >> >> print "With MySQLdb without ORM", time.time()-xref
> >> >> >>
> >> >> >>
> >> >> >> *TEST RESULTS*
> >> >> >>
> >> >> >> With ORM dont_load 0.453000068665
> >> >> >> With ORM force update 0.296999931335
> >> >> >> With ORM pure SQL 0.31200003624
> >> >> >> With MySQLdb without ORM 0.0939998626709
> >> >> >>
> >> >> >> With ORM dont_load 0.452999830246
> >> >> >> With ORM force update 0.297000169754
> >> >> >> With ORM pure SQL 0.31200003624
> >> >> >> With MySQLdb without ORM 0.0939998626709
> >> >> >>
> >> >> >> With ORM dont_load 0.453000068665
> >> >> >> With ORM force update 0.296999931335
> >> >> >> With ORM pure SQL 0.31200003624
> >> >> >> With MySQLdb without ORM 0.0940001010895
> >> >> >
> >> >> > please use the profile module. The call chain from
> >> >> > Session.execute(somestring) down to connection.execute() is
> >> definitely
> >> >> not
> >> >> > three times slower than connection.execute() itself. On my
> >> >> workstation
> >> >> > here I can execute hundreds of ORM queries per second.
> >> >> >
> >> >> >
> >> >> >>
> >> >> >>
> >> >> >> 2009/9/30 Michael Bayer <mike...@zzzcomputing.com>
> >> >> >>
> >> >> >>>
> >> >> >>> Christian Démolis wrote:
> >> >> >>> > Thx for your answer.
> >> >> >>> > MakeReleased is a method of com object windows agent
> >> (self.agent =
> >> >> >>> > DispatchWithEvents('CosmoAgent.clsCCAgent', Evenement))
> >> >> >>> > It takes 0 second to execute as we can see in the execute print
> >> >> >>>
> >> >> >>> yes I realized later there were two blocks of timer calls. The
> >> >> >>> inclusion
> >> >> >>> of that code made the example harder to read.
> >> >> >>>
> >> >> >>> In particular using session.merge() with an object will issue a
> >> >> SELECT
> >> >> >>> first to locate the current row. this is likely the cause of
> >> the
> >> >> >>> slowness in this specific case since you seem to have a slow
> >> network
> >> >> >>> (profiling would reveal this too). Passing "dont_load=True" to
> >> >> >>> merge()
> >> >> >>> will skip the SELECT step and trust that the state you are
> >> passing
> >> >> it
> >> >> >>> is
> >> >> >>> the state that is within the database.
> >> >> >>>
> >> >> >>> > "Orm does additional steps"
> >> >> >>> > is it possible to force him update only one attribute of the
> >> >> object?
> >> >> >>>
> >> >> >>> you may say:
> >> >> >>>
> >> >> >>>
> >> >> >>>
> >> >>
> >>
> session.query(MyObject).filter(some_criterion).update({'attrname':somenewvalue},
> >> >> >>> False)
> >> >> >>>
> >> >> >>> which will issue an UPDATE statement matching the criterion.
> >> this
> >> >> is
> >> >> >>> the
> >> >> >>> fastest way by far using the ORM only.
> >> >> >>>
> >> >> >>> Changing the "False" to "evaluate" or "expire" will also update
> >> or
> >> >> >>> expire
> >> >> >>> the state of your ORM instance - but that will add some overhead.
> >> >> >>>
> >> >> >>>
> >> >> >>>
> >> >> >>> >
> >> >> >>>
> >> >> >>
> >> >> >> >
> >> >> >>
> >> >> >
> >> >> >
> >> >> > >
> >> >> >
> >> >>
> >> >>
> >> >> >
> >> >>
> >> >
> >> > >
> >> >
> >>
> >>
> >> >
> >>
> >
> > >
> >
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
