Posted by dado on June 7, 2006 at 7:23pm
In this post
http://drupal.org/node/67502
I describe my troubles with slow views. I learned that as I add fields to my views (esp. fields from different tables), queries slow down a lot. My solution was remove all fields from my views, then load them inside my theme_views_view_... function, using node_load(). This cut my page execution time from 6 seconds to 1 second.
Now I find that I am doing all my views this way, for performance optimization. I find it too expensive to add fields to my views (for CCK nodes anyway, which is ~all I am using).
So I wonder if CCK's views implementation could effectively do this in the background. But I don't believe Views API would support this?
Comments
odd
this is certainly not intended behavior. i don't know if the problem is in cck or views, but it should be tracked down instead of worked around.
thanks for replies
My page took 6 seconds to load and 1 views_build_view query (which loads CCK fields from 4 tables & location from 1 table) accounted for 5 of those seconds, so no mystery where the slowdown is I think. I decided to try using CCK's caching to improve performace and definitely worked. Off the top of my head, I would think best solution would be if views permitted API for declaring that node's fields should be loaded w/ node_load instead of standard SQL joins.
All cck views fields are run
All cck views fields are run through the field handler in the content module which then determines if there is a field handler in the field module, and executes it. That is a bit of extra work that doesn't happen if you don't add the fields and instead load the node in your template, but it also means nothing the field views handlers do gets done if you leave the fields out of the view. I'm not sure how much of the slowdown is due to running things through the handler. If you have a lot of fields, each one is going to generate a pass through the handler.
Maybe there's something that could be done to cache the handler results, if that is not already happening.
cck filters and arguments also run through handlers in the content module, for whatever it's worth.
I'm not sure what, if anything that means about the cck processing of views fields, just kind of thinking out loud about how the processing works to see what is different about the normal views processing and your round-around method...
CCK's data storage method
This is almostcertainly due to CCK's data storage method, where all the fields are in different tables, which causes a lot of JOINs.
However, when you node_load the view, the entire node is cached. Thus, the only joins are going to be what you sort and filter on.
I think the only workable solution is that when using a CCK table that has a lot of fields, if you have performance issues you probably need to theme it out and use a template. I should look at contemplate and see if it can easily be used to plug in there. It may turn out to be very simple.
I just ran into problems
I just ran into problems with slow queries and did some experimenting of my own and am finding that it is the location fields that really clobber the load time. I did a series of experiments using fields from various tables, including cck, location, and event, and load times were all reasonable until I added a location field, when performance plummeted. I can see no reason why location fields are such a problem, but maybe there is something in the way the table or handlers are set up in location views. I see that you mention a location field in your list so I wonder if it is actually that field that is causing your problems. What happens to performance if you remove the location field? Does anyone have any idea what in the location joins could be causing such a big problem?? I'm ready to make any necessary changes to location views, but I really have no idea what to try.
UPDATE: This is even stranger than I first thought. I have no problems on other installations with location fields in views that I am testing, just one. I will have to keep digging to find what about this particular installation is creating such a problem.