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
-~----------~----~----~----~------~----~------~--~---
