Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have one master list from which I wish to query and create a subset of this in a new custom list. The code I have so far works, but it is extremely slow and occasionally hits the hard limit of the 30 second request time of a sandboxed solution.

The master list contains about 700 items and I will need to copy up to 500 to the subset list.

Is there a more efficient way of running this query? What is the best practice for this sort of task?

                string filterQuery = "<Where><Eq><FieldRef Name='milestone' /><Value Type='Text'>4</Value></Eq></Where>";

                SPQuery query = new Query(); 
                query.Query = filterQuery;
                SPList list = web.Lists["Projects"];
                SPListItemCollection collListItemSrc = list.GetItems(query);
                SPListItemCollection collListItemDest = web.Lists[documentTitle].Items;

                string projectId = "Project name";

                foreach (SPListItem oListItemSrc in collListItemSrc)
                {

                    SPListItem oListItemDest = collListItemDest.Add();


                    oListItemDest["ProjectID"] = projectId;
                    oListItemDest["ProjectName"] = oListItemSrc["ProjectName"];
                    oListItemDest["Owner"] = oListItemSrc["Owner"];
                    oListItemDest["Manager"] = oListItemSrc["Manager"];
                    oListItemDest["Objective"] = oListItemSrc["Objective"];
                    oListItemDest["Duration"] = oListItemSrc["Duration"];
                    oListItemDest["Milestone"] = oListItemSrc["Milestone"];
                    oListItemDest.Update(); 
                 } 
share|improve this question
    
Is it one time operation, or you will use it again? –  Arsalan Adam Khatri Dec 6 '12 at 13:45
    
What do you do in GetProjectName()? and btw query.ViewFields and query.ViewFieldsOnly could help on your performance too. –  Anders Aune Dec 6 '12 at 14:41
    
It will be used many times programmatically based on any search criteria specified by the end user. –  James Harland Dec 6 '12 at 15:33

2 Answers 2

If it is one time operation i.e. Static, you can:

1- Make a list view of the source list with your filters
2- Export the view to Excel
3- Make a datasheet view of the destination List with matching columns to Excel Spreadsheet
4- Copy data from Excel sheet to Datasheet view of List!

OR

Use Excel Add-in to synchronize data from Source Excel to destination List!

If it is more than one time operation i.e. Dynamic:

Have a look at List Item Performance - Updating Large Lists and SharePoint: Using Batch-Updates to speed up performance? ... I don't really know if RPC calls/Batch Adding items work in Sandbox environment or not, but you can give it a try!

Have a look at How to: Update List Items, they say update but of course can be used for Adding items!

EVEN THAT DOESN'T SOLVES THE PROBLEM

You can then schedule this task for Timer Job, keep a list with the user specified criteria and use it as a scheduled task for Timer Job... You can also notify the user about completion of task from within the Timer Job! I usually prefer this approach if the task is time consuming, and keep user posted about this! Let me know if you want more information on this!

I hope this helps!

share|improve this answer
1  
The batch update is a little tricky to get right but works extremely well –  Dave Wise Dec 6 '12 at 16:11
    
Great reply, thanks for the information. I shall give it a go and let you know if it works! –  James Harland Dec 6 '12 at 16:20

James,

What about just creating a new View for the current list, which filters to show only what you need?

Or if you don't need all the columns, perhaps creating a second list with specific Lookup columns referencing the first?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.