Ok, so yield_per() is useless when subqueryload() is used. Thank you,
Ladislav Lenart On 5.6.2013 16:27, Michael Bayer wrote: > the Query emitted by subqueryload does not use yield_per. so if your total > result set is 1000 rows, and the total rows represented by all the > collections is 10000, the first time that query is emitted, 10000 rows will > be fully loaded and processed into memory at once. This would occur > typically somewhere in the first few rows of your 50 -row yield_per batch. > So the intent of yield_per, which is to conserve memory and upfront loading > overhead, would be defeated entirely by this. > > The subqueryload is emitting once if I recall correctly because I probably at > some point have the query result being memoized in the query context to > prevent it from being emitted many times in a yield_per scenario. > > > On Jun 5, 2013, at 10:20 AM, Ladislav Lenart <lenart...@volny.cz> wrote: > >> Hello. >> >> One more note. I've just tried the below experiment with joinedload() >> instead of >> subqueryload() and that does NOT work just as you expected. One contact is >> returned several times and the first occurrences have incomplete phones. >> >> However my experiments with subqueryload() suggest that it works just fine >> with >> yield_per(). Can you elaborate on that one please? >> >> >> Thank you, >> >> Ladislav Lenart >> >> >> On 5.6.2013 14:03, Ladislav Lenart wrote: >>> Hello. >>> >>> I've tried an experiment to verify that yield_per() with subqueryload() >>> behaves >>> as badly as you described, but according to my practical observation, it >>> issues >>> ONE subqueryload() and everything behaves as I would expect. It emits two >>> SELECTs, one to fetch the objects and the second one to fetch the related >>> data >>> and that's about it, see below. So back to square one, what is wrong with >>> it? >>> >>> >>> Base = declarative_base() >>> >>> class Contact(Base): >>> # Just a sketch, I hope you get the picture. >>> id, >>> name, >>> phones = relationship(Phone) # many >>> >>> class Phone(Base): >>> # Just a sketch, I hope you get the picture. >>> id, >>> number, >>> contact = relationship(Contact) # one >>> >>> # Setup engine with echo set to True. >>> >>> phones = ['123456789', '987654321', '555777999'] >>> for i in range(1, 11): >>> c = Contact(name=u' '.join([u'Contact', unicode(i)])) >>> session.add(c) >>> session.add_all(Phone(contact=c, number=e) for e in phones) >>> session.flush() >>> session.expunge_all() >>> >>> q = session.query(Contact).options(subqueryload(Contact.phones)) >>> for each in q.yield_per(2): >>> print each.last_name >>> for e in each.phones: >>> print e >>> >>> The output is like this: >>> SA info about all the inserts after session.flush(). >>> SA info about select for contacts. >>> SA info about select for their phones. >>> Contact 10 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 9 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 8 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 7 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 6 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 5 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 4 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 3 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 2 >>> 123456789 >>> 987654321 >>> 555777999 >>> Contact 1 >>> 123456789 >>> 987654321 >>> 555777999 >>> >>> >>> Thank you, >>> >>> Ladislav Lenart >>> >>> >>> On 5.6.2013 11:26, Ladislav Lenart wrote: >>>> Hello. >>>> >>>> I have one more question about my approach to WindowedRangeQuery: >>>> >>>>> * Suppose a complex query q with join(...), filter(...) and options(...). >>>>> * I need to create q2 from q such that: >>>>> * It has no options. >>>>> * Can I reset the options with q = q.options(None)? >>>>> * It has select with the over by magic taken from WindowedRangeQuery >>>>> recipe. >>>>> * I know I can use with_entities() for this. >>>>> * I will use q2 to obtain the window ranges. >>>>> * I will iterate over the window ranges and apply each to the original q >>>>> and >>>>> execute it in a loop. >>>> >>>> Suppose I have a complex query (without options() / order_by()) to obtain >>>> the >>>> objects I want. I use this query to calculate window intervals. I don't >>>> have to >>>> use it again to fetch the objects, because I already have their id >>>> intervals. Am >>>> I right? >>>> >>>> >>>> Thank you, >>>> >>>> Ladislav Lenart >>>> >>>> >>>> On 4.6.2013 19:15, Ladislav Lenart wrote: >>>>> On 4.6.2013 18:49, Michael Bayer wrote: >>>>>> >>>>>> On Jun 4, 2013, at 12:28 PM, Ladislav Lenart <lenart...@volny.cz> wrote: >>>>>> >>>>>>> >>>>>>>> Absolutely, you should do whatever you have to in order to get the >>>>>>>> range >>>>>>>> you want, in fact the recipe even says this... >>>>>>> >>>>>>> Ok. What I want to do is basically the following: >>>>>>> * Suppose a complex query q with join(...), filter(...) and >>>>>>> options(...). >>>>>>> * I need to create q2 from q such that: >>>>>>> * It has no options. >>>>>>> * Can I reset the options with q = q.options(None)? >>>>>>> * It has select with the over by magic taken from WindowedRangeQuery >>>>>>> recipe. >>>>>>> * I know I can use with_entities() for this. >>>>>>> * I will use q2 to obtain the window ranges. >>>>>>> * I will iterate over the window ranges and apply each to the original >>>>>>> q and >>>>>>> execute it in a loop. >>>>>>> >>>>>>> Can this strategy work? >>>>>> >>>>>> there's not a publicly supported feature to "reset" the options right >>>>>> now so you'd probably need to apply them after you get your window >>>>>> ranging query. You can probably remove the effect of eager loaders >>>>>> if you were to just assign a blank _attributes dictionary to the new >>>>>> query though (just no long term guarantees). >>>>>> >>>>>> other than that, I use the windowing concept extensively and it works >>>>>> very well. >>>>> >>>>> Ok, I will try it. >>>>> >>>>> Thank you very much for your invaluable insights, >>>>> >>>>> Ladislav Lenart >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. >> For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
