If I set expire_on_commit=False only the modified object will be written?
I don't know if there is a better way to do this (short version):
mail_items = mboxgroup_db.mail_item.filter(msg_filter).all() <---
this returns 636000 objects
for mail_item in mail_items:
do_something(mail_item)
mail_item.volume_id = 3
mboxgroup_db.commit()
Is it possible to get an iterator querying with Sqlsoup?
Regards,
Diego
On Tue, Dec 21, 2010 at 3:29 AM, Michael Bayer <[email protected]>wrote:
> You've got 636000 objects in your session which is quite large. The commit
> would like to expire them when the transaction ends and in this case it's
> the sheer number of objects present taking up the time spent. You should
> try to keep present in a session only those objects which you are working
> with currently, and for large jobs to batch the current set of objects into
> groups of no more than a few thousand. Failing that, you can set
> expire_on_commit=False for the session which will prevent the expire step
> from occurring. Note that clean objects leave the session as soon as you
> dereference them everywhere else.
>
> Sent from my iPhone
>
> On Dec 20, 2010, at 10:37 PM, Diego Woitasen <[email protected]> wrote:
>
>
>
> On Tue, Dec 21, 2010 at 12:35 AM, Diego Woitasen < <[email protected]>
> [email protected]> wrote:
>
>> Hi,
>> I have a python script that uses Sqlsoup to iterate over some rows of
>> table to modify them. The script is running really slow and I ran cProfile
>> to see where is the bottleneck. I've got this:
>>
>> 20549870 function calls (20523056 primitive calls) in 34.800 CPU
>> seconds
>>
>> Ordered by: cumulative time
>>
>> ncalls tottime percall cumtime percall filename:lineno(function)
>> 1 0.000 0.000 34.810 34.810 <string>:1(<module>)
>> 1 0.002 0.002 34.810 34.810 {execfile}
>> 1 0.095 0.095 34.808 34.808
>> zimbra-archive.py:34(<module>)
>> 20 0.000 0.000 25.618 1.281 sqlsoup.py:504(commit)
>> 20 0.000 0.000 25.618 1.281 scoping.py:128(do)
>> 20 0.000 0.000 25.618 1.281 session.py:631(commit)
>> 40/20 0.000 0.000 25.617 1.281 session.py:361(commit)
>> 20 1.262 0.063 25.565 1.278
>> session.py:290(_remove_snapshot)
>> 636600 20.002 0.000 23.254 0.000
>> state.py:220(expire_attributes)
>> 100 0.021 0.000 5.035 0.050 query.py:1447(all)
>> 32048 0.042 0.000 3.180 0.000 query.py:1619(instances)
>> 504 0.001 0.000 3.139 0.006 sqlsoup.py:549(__getattr__)
>> 504 0.003 0.000 3.138 0.006 sqlsoup.py:535(entity)
>>
>> The problem is commit, almost all the time used by the script is on
>> commit.
>>
>> Is there a faster way to modify every row? Or Should i use raw sql?
>>
>> Regards,
>> Diego
>>
>> --
>> Diego Woitasen
>> XTECH
>>
>
> I forgot the important lines of the code.
>
> mail_item.volume_id = archive_vol.id
> mboxgroup_db.commit()
>
> That's the only modification that I'm doing. A simple query with that
> change.
>
>
> Regards,
> Diego
>
> --
> Diego Woitasen
> XTECH
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected]<sqlalchemy%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
Diego Woitasen
XTECH
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
