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