I does a mistake in my last, that is the results (error in parameters of
mysqldb connection)
Local database
Update 1000 MySQLdb 0.0319998264313
Update 1000 SqlAlchemy 0.265999794006
France-Tunisia
Update 1000 MySQLdb 10.3910000324
Update 1000 SqlAlchemy 42.1579999924
Sorry
2009/10/2 Christian Démolis <christiandemo...@gmail.com>
> *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
-~----------~----~----~----~------~----~------~--~---
