In this post, I will show you how to do some performance tuning and troubleshooting with the data view web part in SharePoint 2010. Please read my previous post called SharePoint Column: Count Related Items, and I’m going to refer to this method as a “reverse lookup”.
This post is based on an issue that I came across on a SharePoint page in a client’s environment. This page had a couple of data view web parts on it, and it started to load very slowly after this environment had gone live and there was a lot of data in it. When I refer to a data view web part, I’m not referring to an XSLT list view web part, and here is my quick video showing the difference:
SharePoint 2010 Data View and XSLT List View.
So, when I became aware that the page was running slowly, I decided to turn on the Developer Dashboard, in order to see where the issue was, exactly. I went to the server, and used PowerShell to turn on the dashboard so that it’s available on demand, using what I learned from Todd Klindt here: How to use Windows PowerShell to configure the Developer Dashboard.
Once the Developer Dashboard is turned on, you’ll see this new icon at the top right in SharePoint, next to your own name:
When you click the icon, it shows you the dashboard for that page, with that page’s performance. See in this screenshot, there’s my data view web part at the top, and the dashboard at the bottom.
Scroll down to look at the full information at the bottom:
Take a look at “Execution Time” in the top right. It shows 420.05 milliseconds. That’s actually pretty slow, especially since it’s just a simple list of a few columns. Look at the “Database Queries” section. The item called @DocParentIdForRF is the one that is taking the longest. Hmm, there’s some kind of big query going on in my web part. I didn’t know what I was looking at, so I elicited the help of one of our developers, Phil Jirsa. He recognized this query item as being related to a lookup field. The only lookup field I had was one of these reverse lookups that’s showing the number of items in another list that are looking up to the items in this list. Ah hah! So each time the page renders, it’s having to do that count of how many items in the other list, and having to display the number next to each of the ones in this list. The more items in the other list, the slower this page loads. In this case, there were thousands!
But wait a minute, as you can see in my above screenshot of my list of doctors, that column called “Count Patients” is not even being displayed! Well guess what… it’s being rendered anyway. I’ll show you what Phil showed me.
Open your web part page in SharePoint Designer. Under “Current Data Source” on the right, click the name of your web part. Mine is DataView 1.
Click Fields on this screen.
There it is! See my “Count Patients” field, which is my reverse lookup field.
I can remove this field since I’m not using it. I can also clean up this list of fields on the right by removing any fields that I’m not using as lookup fields or in any of my filtering, sorting, grouping, or conditional formatting. I recommend leaving ID and title in there no matter what.
NOW my web part has been performance tuned!
Notice that this list of fields is different than the list of columns. In the ribbon, when I click the big Add/Remove Columns button, this only shows the fields that are actually being displayed as columns.
I save my page and go back to the browser and refresh it. Do a hard refresh by clicking CTRL+F5.
Whoa, look at that! My execution time is down to only 104.16 milliseconds!
Now, I hate to tell you, but even though this trick is pretty cool, I know nothing about what most of the information on this screen means. I just know to look at the number of milliseconds next to each item and then try and research what that item is.
Here are a couple of reference links:
Also, if you’d like to see the video of me doing the demonstration of this, you can watch me on our SharePoint Power Hour Episode 3. Unfortunately, the hard drive on my VM happened to fill up in the middle of my demo!