File export causing timeout

I have the same problem as above. I am trying to export about 3000 client from my page, but the problem i have is php memory limit. I suggest implementation of batch processing, i dont know if it is actually possible. But its an idea.

There are several guides in the forums on increasing PHP memory limits using .htaccess or the PHP config file. In Apache, you can do it through the WHM panel as well. But your problem may not be memory. I have PHP memory and timeouts set high and still had problems getting large views files to download as XLS or CSV. (Not necessarily large in terms of the resulting file size, but large in terms of the number of nodes and number of fields in a node.) I solved the problem by NOT setting the view's "items per page" to "unlimited." Instead, I set it to a high number that I was certain would be more than my number of nodes (in my case, 4,000).

oh joy prolly better to refactor the process to make use of batch api only its difficult..

My "solution" of not setting the items per page to unlimited turns out to have its own limits. As the necessary record ceiling grows higher, the "page not found" issue pops back up again. In my case, the problem recurs when trying to retrieve around 15,000 records. After some sleuthing, it turns out that the query is eating up almost two Gigs of memory, and is taking more than 2 of the 4 CPUs in my Intel Core Duo e7400 processor. (I have a dedicated server, so I'm not sharing those resources with anyone else. And I'm on a private T1, so bandwidth isn't the problem.) As the resources reach their limits, the query slows, then stops, then throws the "page not found" error.

This is not the sort of result I am used to seeing from Drupal; certainly not the sort of result I expect from MySQL. I lack the expertise to evaluate whether this is a matter of an inefficient script in views or views_bonus, or whether I simply need a more powerful server. But more powerful than a 2.8 Gig MHz Core Duo with 4 gigs of memory? That's a lot of Web server for a single site.

Inefficient views queries happen. I would debug the query in question. You may even need to right you own module to query things appropriately. You may be joining or filtering on unindexed field which is terribly easy to do with cck.

Views sadly isn't always the answer. In the end you may have to build your own module.

As for the batch api idea. Kinda cool but handling access contol on the files, any filters or dynamic aspects of the query. It seems pretty daugnting. You could /might/ be able to make a display handler to try and render it but I don't know.

Subscribing. Thanks

This is affecting a number of our sites where we provide basic reports with views, and clients can download them to csv/xls. I think batch API is the answer...

Roughly I reckon an implementation might look like:

1. Create a folder in the files directory to store the results, and add a .htaccess to prevent people from just downloading the files using apache.
2. When view is intially rendered, build a batch set to build the full results, manipulating the pager manually for each batch invocation.
3. Execute the batch, passing the results into the file (easy with csv, no idea about xls)
4. At the end of the batch redirect to a download link
5. Download link checks the user against the access permissions on the view, and then transfers the file.

I've been hitting this problem pretty hard - timeouts or memory limits when trying to export thousands of rows. The main problem seems to be that views buffers all of the rows from the query before running them out through the theming system.

I spent most of today hacking around and managed to make a views display that streams the rows from the database to the output, which fixes the memory issues, but it still goes through theming which is slow - I get 7000 rows before hitting the 60 second timeout. Unfortunately I still need the theming output sensible text for the node references and taxonomy fields in this view. But it proved to me that it can be done.

If I get it a bit prettier (I'm just getting started with views hacking) I'll post it. Still wondering how to dereference node references and vocabularies without going through theming though…

subscribing

subscribing

subscribing

Going to work on this. Expect magic.

Going to work on this. Expect magic.

looking forward to it!

If you want to follow development:

https://github.com/computerminds/views_bonus

Subscribing. Waiting eagerly for magic.

@Steven Jones Feel free to develop a patch but looking over your github repo, there are many changes that I will not take. Spurious spacing changes, template changes, unrelated function moving, etc.

Okay, so coding standards aside, all the changes made are required for this to work. The current rendering pipeline only really works if the entire dataset can fit into memory, which isn't really scalable at all. So yes, we've had to refactor the export render pipeline, but it produces identical output to the existing code.
We'll happily take critical feedback once we're happy with what we've got in the git repo.

If you want the export functionality in views bonus to remain useful for only very small exports, then that's fine, but we'll have to fork the project, as there are clearly lots of people who want this functionality of doing large exports.

Template changes are out of the question. Are they required and if so why? Lets find a better solution.

With the current pipeline there's no way to render say, just the rows of data for a XLS file. The current rendering pipeline (Drupal's theme layer) requires everything to be in memory, which just isn't scalable.

The code over at github needs a lot of cleanup, and it is seriously confusing to follow, but it does actually work. We've exported over a hundred thousand rows to CSV with it.

@neclimdul Would you be up for spinning the export functionality out into a separate module? Or do you want to keep it in the Views Bonus package?

Subscribing.

Our work has taken the turn of a fork. Our code is now available at:

http://drupal.org/project/views_data_export

And we've included the ability to 'migrate' views using the views bonus style plugins to the Views data export versions.

Looking forward to getting feedback and really moving forward in this area of data exports.

Fair enough. I'm quite disappointed that a solution couldn't be reached. It just is not possible for template changes because it will break live sites with themed changes. We could have branched but...

Maybe its for the best, the scope of what's needed for a useful implementation has always concerned me and it probably doesn't align with the goals of Views Bonus Pack providing recipe modules for people to build and learn from.

@neclimdul, could you please put this issue on the views_bonus project page? I just spent all day toying around with this to find this issue and a forked project. It would be really helpful for others who come to the views_bonus project page to export large data sets.

subscribing... just in case this is re-thought and we decide to implement a workaround/fix.

@Steve Jones: Brilliant work on "Views data export". I had a list of simplenews (v2.x-dev) subscribers that just grew past 25k rows. Your module helped be circumvent the large-row-limitations of Views Bonus Pack.

Both modules are great and have their place. Thanks to everyone for your hard work on these! :)

keeping track.