On Tue, Jan 17, 2012 at 1:34 PM, Tamas Hegedus <[email protected]> wrote:
> #-----------------------------**-----------
> #This version is very slow (>10sec)
>
> @expose('myprj.templates.**browse')
> def browse(self):
>
> q = PfamClan.query
> q.enable_eagerloads(True)
> pfamclans = q.filter(PfamClan.van==1).**
> filter(PfamClan.families.any()**).order_by(PfamClan.id).all()
> return dict(page='browse', clans=pfamclans)
>
If you're running via paster, you'll get debug output that will include the
SQL queries that are being run. Judging by the differences between this and
your fast query, I'd guess you are missing some indexes. For instance, your
query above has an "order by" clause, and adds in a "where" clause.
I would use the tools for the database to find out which indexes are not
present, get those built, and that should produce major improvements for
you.
> <!-- The corresponding template -->
>
> <py:for each="clan in clans">
> ${clan.longname}<br/>
> <ul>
> <py:for each="fam in clan.families">
> ${fam.longname}<br/>
> <ul>
> <py:for each="prt in fam.protein">
> ${prt.entry_name}<br/>
> </py:for>
> </ul>
> </py:for>
> </ul>
> </py:for>
>
> *************************************************
> #-----------------------------**-----------
> # This version is fast (<1sec)
> # .filter(PfamFamily.protein.**any()) is an implicit
> # join in Elixir (most likely also in SQLAlchemy)
>
> @expose('rbcdb.templates.**browse')
> def browse(self):
>
> pfamfamilies = PfamFamily.query.filter(**
> PfamFamily.protein.any()).all(**)
> return dict(page='browse', families=pfamfamilies)
>
> <!-- The corresponding template -->
>
> <py:for each="fam in families">
> ${fam.longname}<br/>
> <ul>
> <py:for each="prt in fam.protein">
> ${prt.entry_name}<br/>
> </py:for>
> </ul>
> </py:for>
>
> Michael Pedersen wrote:
>
>> I'm not terribly familiar with Elixir, I admit. I'm willing to try to
>> help, though. Can you post the model you're actually using (not just
>> pseudocode), and (ideally) the template code that works and that doesn't
>> work, and I'll try to help sort it out?
>>
>> On Mon, Jan 16, 2012 at 4:12 PM, Tamas Hegedus <[email protected]<mailto:
>> [email protected]>> wrote:
>>
>> Hi,
>>
>> I have problems even with simple joins...
>> I am using TG with MySQL and SQLAlchemy/Elixir.
>>
>> I have object_TOP with one2many relationship to object_MID with
>> many2many relationship to object_BOT.
>>
>> I am doing in the template (pseudo code):
>> for oTOP in object_TOP:
>> print oTOP
>> for oMID in object_MID:
>> print oMID
>> for oBOT in object_BOT:
>> print oBOT
>>
>> The rendering in the browser is extremely slow. I mean the fetching
>> of the objects from the database.
>>
>> ---------------
>> There are
>> 500 rows in the table of object_TOP
>> 13,000 rows of object_MID
>> 171 rows of object_BOT
>>
>> There are only 230 rows in the mapping table of object_MID and
>> object_BOT (I do not want to delete the unused rows from the table
>> of object_MID - only if I can not solve this performance issue).
>>
>> --------------
>> To avoid issuing a lot of select statements, I tried eager loading.
>> q = object_TOP.query()
>> q.enable_egarloads(True)
>> It did not help.
>>
>> If I select only the object_MID and object_BOT in the following way
>> (performing an implicit join), it works well:
>> objectMID.query.filter(object_**__MID.oject_BOT.any()).all()
>>
>>
>> --------------
>> Could you tell me, or at least give me some suggestions, how to do
>> these things in the proper way? How to do this three level load with
>> good efficacy?
>>
>> Thanks a lot for your help in advance,
>> Tamas
>>
>> -- You received this message because you are subscribed to the
>> Google
>> Groups "TurboGears" group.
>> To post to this group, send email to [email protected]
>> <mailto:turbogears@**googlegroups.com <[email protected]>>.
>>
>> To unsubscribe from this group, send email to
>> turbogears+unsubscribe@__googl**egroups.com <http://googlegroups.com>
>>
>> <mailto:turbogears%**[email protected]<turbogears%[email protected]>
>> **>.
>>
>> For more options, visit this group at
>>
>> http://groups.google.com/__**group/turbogears?hl=en<http://groups.google.com/__group/turbogears?hl=en>
>>
>> <http://groups.google.com/**group/turbogears?hl=en<http://groups.google.com/group/turbogears?hl=en>
>> >.
>>
>>
>>
>>
>>
>> --
>> Michael J. Pedersen
>> My Online Resume: http://www.icelus.org/ -- Google+
>> http://plus.ly/pedersen
>> Google Talk: [email protected] <mailto:[email protected]> --
>> Twitter: pedersentg
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "TurboGears" group.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to turbogears+unsubscribe@**
>> googlegroups.com <turbogears%[email protected]>.
>> For more options, visit this group at http://groups.google.com/**
>> group/turbogears?hl=en <http://groups.google.com/group/turbogears?hl=en>.
>>
>
>
> --
> Tamas Hegedus, PhD
> Membrane Research Group | phone: (36) 1-459 1500/60233
> Hungarian Academy of Sciences | fax: (36) 1-266 6656
> Tuzolto utca 37-47 | mailto:[email protected]
> Budapest, 1094, Hungary | http://www.hegelab.org
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "TurboGears" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to turbogears+unsubscribe@**
> googlegroups.com <turbogears%[email protected]>.
> For more options, visit this group at http://groups.google.com/**
> group/turbogears?hl=en <http://groups.google.com/group/turbogears?hl=en>.
>
>
--
Michael J. Pedersen
My Online Resume: http://www.icelus.org/ -- Google+ http://plus.ly/pedersen
Google Talk: [email protected] -- Twitter: pedersentg
--
You received this message because you are subscribed to the Google Groups
"TurboGears" 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/turbogears?hl=en.
