SPCascadedDropdowns performance

Apr 23, 2011 at 9:33 PM
Edited Apr 23, 2011 at 11:17 PM

On the SPCascadedDropdowns demo page, if you look to the page source code, you can notice, that all the city names are already loaded into the page.

http://www.sympraxisconsulting.com/Demos/Demo%20Pages/CascadingDropdowns.aspx

Yes, SPCascadedDropdowns is a client technology, so it can not alter data, which are loaded from server. But this means, that for huge amounts of data, a user will have the performance cut!

For example, imagine, if you deal with search engine keywords. There can be millions and millions of keywords, and you could have, for example, 5-10 megs of data loaded into your browser - even before SPServices start working!

So, in my opinion, while SPCascadedDropdowns is a great and very simple-to-use feature for small lists, but do not use it, if you deal with large amounts of data.

 

At the other hand, a very interesting idea hides here. SPServices is always trying to load some data using web services. It always takes some time, isn't it? But in fact, ALL THE DATA is already on the page. And you can probably grab it from the javascript DOM model.

So, what do you think, am I right?

Coordinator
Apr 23, 2011 at 10:56 PM
Edited Apr 24, 2011 at 12:59 AM


Sorry, sometimes when I reply from my iPhone, it goes all Base64 on me. You're the first person who's ever tried to convert it, I must say!

omlin:

Thanks for expanding on what you said in your tweet. Let me reply piece by piece, as well as to the issue you pointed out in the ensuing tweet.

First, while SPServices operates entirely client side with script (jQuery/JavaScript), it certainly is possible to alter data, just as you can with the Client Object Model. You can update most of the primary SharePoint objects with operations such as UpdateListItems, UpdateList, etc.

Search engine keywords is a rather spurious example, I think. If you were to expose 'millions of keywords', you'd certainly want a better UI than the lowly dropdown generated by a Lookup column. (In fact, SPAutoComplete might work far better, though millions of values is probably still far too many to choose from.) As with all programming challenges, you'd certainly need to think about the specific use case to be sure that SPCascadeDropdowns (or any other approach) was going to be both useful and performant.

I also carefully piggyback upon the markup SharePoint emits natively so that if the script doesn't run or bombs out for sone reason, the user can still get their work done.

Yes, all the data is in the DOM on page load, but the *relationships* between those values are not; they are contained in the relationshipList. Without the ability to query the relationshipList upon a value change, there's no cascading magic.

The issue you pointed out in your second tweet (http://bit.ly/dTHtwv) is a valid issue which I need to fix, but one where the performance hit is extremely small. I think if you investigated, you'd see that the extra call to GetList requires a few hundredths or tenths of a second, which is hardly noticeable. Still, I need to fix it.

There is a performance cost to using SPCascadeDropdowns, for sure, but a performance cost which ought to beat alternatives in most cases. There are no postbacks using this Web Services approach, no matter how many times the user changes the value(s) and the Web Services are extremely fast.

I'm always interested in any further suggestions you might have for improving the performance of SPCascadeDropdowns or any of the other SPServices functions.

M.

Apr 23, 2011 at 11:43 PM
Edited Apr 23, 2011 at 11:54 PM

Base64 decoding quest? Ok, I passed ;)

> Yes, all the data is in the DOM on page load, but the *relationships* between those values are not

Yes, you're right about the relations, didn't think about this :(

> possible to alter data

Yep, but I meant that you can't prevent lookup fields from loading all of its possible values into your browser. Because SharePoint renders html server-side.

> Search engine keywords is a rather spurious example, I think.

Ok, if you don't like search engine keywords example, let's try to extend your own demo? :) There are 2 millions of cities in the whole world, say, 10 symbols in length avg each... 20 megs of data! And plus countries, plus regions...

>I think if you investigated, you'd see that the extra call to GetList requires a few hundredths or tenths of a second

I think, this may be true for intranet or loopback, but not for extranet sites. At least, I have about 0.5-1 second "freeze", when selecting Region, at your demo page.

Anyway, big thanks for your great contribution! For most purposes, it is a very good solution.

Coordinator
Apr 24, 2011 at 1:53 PM

My $0.02 cents.

> Yep, but I meant that you can't prevent lookup fields from loading all of its possible values into your browser. Because SharePoint renders html server-side.

You certainly can prevent all fields from being returned by specifying a CAML query with the request. You can filter, sort, etc. just as you would with any other query, which would offload that filtering to the server. Obviously once you select that first lookup, the query to repopulate the second lookup is filtered this way, not all of the choices are returned to the user. Unless I'm missing something here, sure the population of the first dropdown is going to return the gamut of options for that dropdown, but any future web service calls filter at the server level and only return valid choices; it doesn't return every choice and use client side logic to decide if it should render or not. Or were you talking about the initial SharePoint page load that builds every possible option before SPServices is invoked?

> I think, this may be true for intranet or loopback, but not for extranet sites. At least, I have about 0.5-1 second "freeze", when selecting Region, at your demo page.

I think this is one of those statements that can't be taken too seriously. Any time someone says "but it's slow for me" you have to consider that individual mileage may vary on performance. Many factors apply here: where are you located, home network, corporate network, firewalls, proxys, caching, etc. My testing has reflected more of Marc's suggestion that it's relatively short; though there have been times where I've gotten some less than stellar performance from Marc's demo site (the demo site as a whole, not just the Cascade Dropdown demo). There's also dozens of other areas that could potentially be the bottleneck, and I always recommend using some timeline and network tools (such as those baked into Google Chrome) to identify specifically where your holdup is. I wouldn't use the blanket statement that it applies for all extranet sites. I've personally deployed this type of solution to a handful of extranets and have not seen the issue you're describing.

Coordinator
Apr 24, 2011 at 8:39 PM
And one more comment from me...

Trying to use SPCascadeDropdowns with every city on the planet obviously would be silly. Though if you did do so, the biggest problem would be *SharePoint's* page load, not anything to do with SPServices. Assuming you managed to stuff all 2 million into a list and had a Lookup column using the values, SharePoint would load all 2 million values in the dropdown (at least it would try - I think). SPCascadeDropdowns would clear all of those values until the state was chosen and then only request the values for that particular state. For my home state, Massachusetts, that's 251 values. No big deal.

M.
>
Apr 25, 2011 at 6:55 AM

> My $0.02 cents.

Hello, webdes03! :)

> Or were you talking about the initial SharePoint page load that builds every possible option before SPServices is invoked?

Absolutely.

> think this is one of those statements that can't be taken too seriously.

Say this to your customers? :)

"Why we don't have any troubles with Google, which has tons of js and data, and your dropdowns are too slow?" - this will be their reply, isn't it?

I think, unless you don't have some kind of caching, SPServices is targeted to intranet portals, not internet sites. And not for big dropdowns.

Just examine any Javascript project. For example, jquery ajax tabs or smth like this. All of them do have caching.

> the biggest problem would be *SharePoint's* page load

> SharePoint would load all 2 million values in the dropdown

That is what I'm talking about from the very beginning. Here is the main problem!

Do you have any ideas how to fight this?

Coordinator
Apr 25, 2011 at 12:57 PM

Call me crazy but there has to come a time where common sense prevails, no? There are guidelines for list sizes in SharePoint, and if your information architecture is dictating a lookup list with 2 million entries, then you need to do some work on your own to fix your information architecture. SPServices is a great library with loads of flexibility, but it's not the end all solution to everything. If you need to handle a 2 million item lookup, then you probably need to do some development, build some custom forms, and offload that work to the server vs. the client. "Fighting" the way SharePoint works OOTB (as you put it) isn't going to buy you anything; if this is a legit requirement that you have to meet, then you need to pass some of the work to the server side.

Comparing SharePoint to Google is like comparing apples to oranges. Sure, your end users and project stakeholders may not "get it", but it's an entirely different scenario. There's many factors to look at including your SQL server performance, horsepower on the servers, indexing, etc. before you can say that it's Javascript causing your bottleneck. You're not providing any real information that leads me to believe you've done any of that research.

In his previous reply himself, Marc said that a lookup list that big is silly, and I'd add to the point saying it's flat out bad information architecture. It can be done, but as with everything else the question shouldn't be "can you" but rather "should you". You take a huge performance hit on large lists, which is why SharePoint 2010 has large list throttling, but those web service requests take ages to be processed by the server. I'm working with a client to rebuild a line of business tool because it has lists with 40-50,000 items. This is nowhere near your 2 million concept, but the performance is terrible and this is OOTB SharePoint with no SPServices; we frequently see 2-3 seconds for the form to load (and that's just SharePoint building lookups of 50,000 items). If you look at any debug tool that uses a timeline it explicitly shows the web service call as the bottleneck. My point- you need to look at your environment and your information architecture before you come back and say Google has fast Javascript why isn't this fast.

Apr 25, 2011 at 2:16 PM
Edited Apr 25, 2011 at 2:53 PM

@webdes03, you're not crazy, of course, but in the real life, each man has it's own 'common sense' :(

Actually, SharePoint lists can operate with huge amounts of data, at least, store it and query it with CAML. And this is true for both lists and document libraries.

And another point, "selecting a city" - is a very common task, isn't it?

> Comparing SharePoint to Google is like comparing apples to oranges

Comparing SharePoint to Google is very reasonable. I always do it at my talks. At least, because SharePoint Enterprise Search is great. ;)

> Sure, your end users and project stakeholders may not "get it"

And they will not :) 100%

> You're not providing any real information that leads me to believe you've done any of that research.

I've worked with huge amounts of data, but my solutions are not only mine and are not OSS, so I can't share them :(

Ok, really, I cannot blame you about this, but I just want to suggest (if you want to improve your awesome project) to think about the performance, and maybe it is possible to find some workarounds, which can prevent SharePoint from loading all the lookup values in the dropdown?

> performance is terrible and this is OOTB SharePoint with no SPServices

About the performance: my opinion is that you could always "suddenly" get bad performance on your production servers, if you will not care about it from the very beginning.

SharePoint lookup fields are not for more than 100-300 items, at least because they do not provide any search capabilities. Here your customers will probably need custom field types (which have some disadvantages, of course, but they could solve all the performance issues).

Coordinator
Apr 25, 2011 at 3:33 PM

> Ok, really, I cannot blame you about this, but I just want to suggest (if you want to improve your awesome project) to think about the performance, and maybe it is possible to find some workarounds, which can prevent SharePoint from loading all the lookup values in the dropdown?

The only way you're doing to do that is to develop your own form. This isn't something you're going to change client-side; the fields are already rendered by SharePoint when SPServices, or any jQuery for that matter, is invoked.

> SharePoint lookup fields are not for more than 100-300 items

That has kind of been the point all along; a lookup containing every city in the world is impractical. I think we just went full circle back to the original point Marc made.

Apr 27, 2011 at 10:05 PM
Edited Apr 27, 2011 at 10:07 PM

Before I post anything, I must agree with @sympmarc and @webdes03...  All of their comments have very valid points.

If you are bound to keeping that many items in the list and wanting the capability to load them into the form, you could create your own ASP.NET drop down with a data source.  Then use CAML to trim the result set.  Kinda a hack, but if you are pigeon-holed, it may help you out.