File Import vs Server Timeout.. how to win this fight?

[Luciano S.]

Problem
I would say one of the most recurring topics in this forum is the server timeout error in file importing (for contacts / contributions / memberships, etc).
In our experience, CiviCRM users use this feature a lot. They are so used to deal with Excel Spreadsheets most of the time, that CiviCRM Import feature is very useful to add and update information easily and fast.
But as a CiviCRM end-user, the frustration comes when importing a file, the unexpected "Server Timeout Error" appears in any of its variants...

Some Solutions
So, what can you do to avoid the server timeout error when importing "large" files (what "large" means? the size will depend on different hardware/software variables of your CiviCRM installation):

- Tweak server parameters, extend php/mysql execution time... (security risks or user simply doesn't have permissions to do so)
- Split your import file in smaller pieces. (time consuming, human error involved... and how could you estimate how many pieces??)
- Use the API, do your own migration script (I am an end-user, what API means?? )


Ideas
I was thinking how this feature can be improved to avoid this limitation.
This is an open discussion, so any 2 cents invested here are more than welcome!

The Import Wizard is a good UI feature and very user-friendly in my opinion. That shouldn't be changed
What happens when the final "Import Now" button is pressed in the last step ?
CiviCRM will start importing all the records of the csv file, previously inserted in a temporary table. So if this process exceeds the php mas execution time, for example, an error will arise, and the process will be stooped without a successful ending.

Why Import can't use a similar approach as Mailings?
When you create a Mailing, and select the target group, this creates a list of recipients in a table, and when you press "Send Now" button, you are not actually sending all the emails that moment.
A CiviCRM cron job will be in charge of sending them, getting some benefits as, for example, launching the php process from CLI where, usually, the time execution limit is higher than the web site one.
And more precisely the cron job will send not all the emails in the recipients list, but just a max of N emails each execution, as it's defined in CiviMail Settings, so another security measure to avoid CLI execution timeout.

So my idea is to process the import records in a CiviCRM Job Task, and as well, having the chance to break into groups of N records each time if necessary.

I don't know if anybody have thought something like this, or if looks like a right approach or not.
I will investigate more deeper this approach and start developing an extension to add this behavior.

cheers!

[Donald Lobo]

an older thread but quite relevant:

http://forum.civicrm.org/index.php/topic,15662.msg67412.html#msg67412

lobo

[Luciano S.]

Thanks for the reference Lobo.
Most of the solutions proposed to avoid timeout issue involves coding custom scripts,  using drupal modules,  attacking the db,  etc.
None if this is focused in the end user,  so that's why I started to think an improvement in the current import wizard

[Eileen]

This seems like a good idea - but is there a risk that simply loading the files into the temp table could overload the memory.

One pattern I have been discussing recently is where contributions are taken in an external system and queued into CiviCRM. We had tentatively thought about using the queue as used in the upgrade - with the thinking being that each data set would be a row. I haven't worked through that too much but a generic storage of 'items to be processed through the queue' could look something like....

{entity:contribution
action:create
mapping:x
raw_data{}}

Of course the current import classes have a lot of code that is hard-coded into the classes so making those 'thises & thats' available 'off-line' (to delayed queue processing) vs statndardising them into the api which is already available off-line might be an issue