Need help resolving a performance issue that I'm running into with a Linq-to-Objects query?

Question

I have a collection of 77 SPListItem objects. These objects can have an implied recursive reference* to other objects. There are currently 4 levels in this hierarchy.

The issue that I'm running into is that when I get items that are deeper in the hierarchy it takes surprisingly long to retrieve them. Time I am seeing at each level:

zeroth: nearly instant
first: 2 seconds
second: 20 seconds
third: goes for about a minute and then times out

This is the structure of the fields in the SPListItem objects:

ID
Title
ParentId //recursive field

And this is the code I am using to get the SPListInformation at each level:

SPList navList = SPContext.Current.Web.Lists["NavStructure"];

//Get items that have no parent
var zero = from n in navList.Items.Cast<SPListItem>()                                                 
           where ((SPFieldLookupValueCollection)n["Parent"]).Count == 0                               
           select new { ID = n.ID, Title = n.Title };

//Get first level items
var first = from n in navList.Items.Cast<SPListItem>()
            from z in zero
               where ((SPFieldLookupValueCollection)n["Parent"]).Select(t => t.LookupId).Contains(z.ID)
            select new { ID = n.ID, Title = n.Title, ParentId = z.ID};
lv_First.DataSource = first.ToList();
lv_First.DataBind();

//Get second level items
var second = from n in navList.Items.Cast<SPListItem>()
             from z in first
               where ((SPFieldLookupValueCollection)n["Parent"]).Select(t => t.LookupId).Contains(z.ID)
             select new { ID = n.ID, Title = n.Title, ParentId = z.ID};
lv_Second.DataSource = second.ToList();
lv_Second.DataBind();

//Get third level items
var third = from n in navList.Items.Cast<SPListItem>()
            from z in second
             where ((SPFieldLookupValueCollection)n["Parent"]).Select(t => t.LookupId).Contains(z.ID)
            select new { ID = n.ID, Title = n.Title, ParentId = z.ID};
lv_Third.DataSource = third.ToList();
lv_Third.DataBind();

Can anyone see something that I am doing here that could cause the long run times that I am seeing?

If anyone would like to see the data just let me know. I left it out because it would be a bit lengthy.

*When I say "implied recursive reference", I mean there is a member in each SPListItem object that can contain an ID and that this ID references another object in the list, but this relationship is not enforced.

Abe Miessler · Accepted Answer · 2011-08-25 18:06:16Z

5

Well, you're executing the first query for each item in navList in second... and each time you execute the first query you're executing the zero query for each item in navList again. third does it all again for every item.

Just adding a call to ToList at the end of each of those queries would probably speed it up significantly.

It's not really clear what you're trying to do, but it feels like you could probably make this rather quicker using a Dictionary or Lookup rather than iterating over the whole collection each time you want to find something.

edited Aug 25, 2011 at 18:06

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

answered Aug 25, 2011 at 18:01

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abe Miessler Over a year ago

In the end I am using this data to create a hierarchical navigation bar. Each of these sets of data are eventually rendered to their corresponding div in the hierarchy. I will look at using a Dictionary or Lookup and see if those are an option.

Abe Miessler Over a year ago

Yep, moving the ToList to the query itself instead of the DataSource made the whole thing nearly instant. I will still check out LookUp, it looks promising.

Dan Bryant · Accepted Answer · 2011-08-25 18:01:46Z

2

You're re-enumerating all prior enumerations on each pass, as you're using the IEnumerable directly. I would recommend storing the list created by ToList() and using that in your subsequent calls.

answered Aug 25, 2011 at 18:01

Dan Bryant

27.5k4 gold badges59 silver badges103 bronze badges

1 Comment

Abe Miessler Over a year ago

Good call, this made it nearly instant. Thanks!

Collectives™ on Stack Overflow

Need help resolving a performance issue that I'm running into with a Linq-to-Objects query?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related