On Feb 13, 8:20 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Feb 13, 2009, at 10:08 AM, Michael Bayer wrote:
>
>
>
>
>
> > On Feb 13, 2009, at 4:07 AM, koranthala wrote:
>
> >> Hi,
> >> I am working on a Python application which does approximately 1000
> >> updates at a time every 3 seconds in a Sqlite File Database using
> >> SqlAlchemy. On timing it in a 2GHz machine, I found that it is taking
> >> ~1.01 seconds to do the same.
> >> Is it possible to increase the speed of the same? My application
> >> is quite a complex one, and taking 1 second for the updates is
> >> slowing
> >> the overall performance considerably.
> >> I checked Sqlite page - and they mention that 25000 text UPDATEs
> >> with an index (which is what I am doing) takes 2.4 seconds (this is
> >> Sqlite2 data - sqlite3 should be faster). I guess should mean that
> >> 1000 text UPDATEs should take 0.1 seconds.
>
> >> The inserts I do is as follows:
> >> BEGIN:
> >> 2009-02-13 14:34:40,703 INFO sqlalchemy.engine.base.Engine.0x..10
> >> UPDATE data SET status=? WHERE data."index" = ?
> >> 2009-02-13 14:34:40,703 INFO sqlalchemy.engine.base.Engine.0x..10
> >> ['True', 68762]
>
> >> ...
> >> (1000)
> >> ...
>
> >> COMMIT
>
> oh sorry, you said "inserts" but your SQL says "UPDATE". for a
> single UPDATE statement of any style the Query has an update() method
> - read the docstrings for it carefully. For issuing 1000's distinct
> UPDATE statements each with different parameters very quickly, the
> same advice as before applies - use connection.execute(table.update(),
> [<dictionaries of parameters>]).
Thank you very much, all for all the help.
I was in a meeting due to which I was not able to follow it up till
now.
I split it into 20 sets of 50 elements each - as below:
qdata = []
for i, o in enumerate(self.data):
if not (i % 50):
qdata.append([])
qdata[i/50].append(o.index)
for q in qdata:
self.session.query(Data).filter(Data.index.in_(q)).update
({'status':txt}, synchronize_session=False)
self.session.commit()
There is a marked improvement in the timings. Earlier it used to take
~1.1 seconds. Now it takes 0.26 seconds which is very close to what I
expected.
Again, thank you all very much.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---