Again about SharePoint list performance…

We have been refactoring some elements of our SharePoint HR solution in terms of improving the performance of reading data from SharePoint lists (thousands of items).

I’d like to share few “bottlenecks” we faced and their solutions.

1. ViewFields property of SPQuery
There are a lot of recommendations to use SPQuery.ViewFields property to limit the set of fields returned by your query.

However, sometimes it is ignored when you need to read all the fields you have created.

We recommend defining this property anyway as far as SPQuery would read a number of internal fields, which you probably do not need.

So in our case defining all our custom fields in combination with ViewFieldsOnly = True gave the significant performance improvement.

2. SPUser field
User field is quite useful and popular when designing custom lists. However creating an SPUser object based on a field value takes very long time.

The good news that in a lot of cases (like our) you do not really need a full SPUser object, but UserID or User Name only.

User field contains a value similar to a Lookup (like, “1;#John Smith”).

So instead of creating SPUser object, we just parsed that string and took ID and Name we needed.

Again significant performance improvements…

3. Lookups with additional fields.

The great new feature of SharePoint 2010 is the ability to “map” additional fields when creating a Lookup field. However that brings additional overheads when you read such data.

We had a list with 4 lookups and 2 of them had additional “mapped” fields.

Reading only 2000 items have been taking a lot of time

That’s a mystery how SharePoint treats that internally, however deleting of only one lookup with an additional field decreased the reading time about 5 times.

Recommendation that is difficult to follow J but try to minimize lookup fields and especially mapping additional fields.



There are not any world-shaking discoveries, however still the practical recommendations, which would probably help you.

Export to excel/word saves blank document or problems with the ReportViewer 2010 in the SharePoint 2010. Again.

Initial data
The disposition is the same like in the previous post: SharePoint 2010 -> Visual Web Part -> UpdatePanel -> Microsoft Report Viewer 2010.


Situation
MRV 2010 generates correct report in the  own report section. BUT when we click "Export to Excel/Word" and try to open generated  document -  we have a following warning:   "The file you are trying to open '<FileName.xls>', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do  you want to open the file now?". If we click yes - we have blank excel/word document.
 
Investigation
If we login as  web-server's admin user and   click "Export to Excel/Word" - we have correct documents without any warnings. If we login as" not admin" user  - we have problem. So the reason is somewhere in the permissions. By "Procees Monitor" I found that "Network Service" had write error in one directory when it was trying to create excel document. It was a signal.


Solution
This directory was "C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp".  So to solve this problem - I just added "Authenticated Users" to this directory with "Full Control " permissions.

BUT! This path is not fully correct answer. This path  just a special case... The correct answer is following - to add  "Authenticated Users" with "Full Control " permissions to the temporary directory of  your Web Application pool  account.  I had my pool that worked as "Network Service". So I used  path as above. But ,for example, if your pool has identity = "Administrator.Office", your path could be following: C:\Users\Administrator.OFFICE\AppData\Local\Temp. And don't forget to do it on the server, not  on your local PC.



It  solved this problem for me.  Hope it will help you.

UpdatePanel Progress Emulation or How to show "Progress" information when we need to generate full postback (by PostBackTrigger) in the UpdatePanel

Initial data
We have an UpdatePanel. In this UpdatePanel we have two buttons. By clicking each button application does something. Also we have an UpdateProgress for the UpdatePanel. BUT one button generate full PostBack.

<%@ Page Title="Home Page" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="UpdatePanelProgressEmulation._Default" %>

<%-- It is registration for a web user control that include presentation layout of our progress information. --%>

<%@ Register src="Preloader.ascx"  tagname="Preloader" tagprefix="uc1" %>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

    <asp:ScriptManager ID="ScriptManagerMain" unat="server"></asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanelMain" runat="server">

        <Triggers>

            <%--Register our full postback action for button--%>

            <asp:PostBackTrigger ControlID="btnWithPostBackTrigger" />

        </Triggers>

        <ContentTemplate>

            <%--UpdateProgress - uses ucPreloader. As in the above code.--%>

            <asp:UpdateProgress ID="UpdateProgressMain" EnableViewState="false"    AssociatedUpdatePanelID="UpdatePanelMain" runat="server">

                <ProgressTemplate>

                    <uc1:Preloader    ID="ucPreloader" ClientIDMode="Static"   runat="server" />

                </ProgressTemplate>

            </asp:UpdateProgress>

       

            <asp:Button runat="server" ID="btnWithoutPostBackTrigger" Text="Button without PostBackTrigger " BackColor="#006600" BorderStyle="Solid" BorderColor="#003300" ForeColor="White"/>

            <asp:Button runat="server" ID="btnWithPostBackTrigger" Text="Button with PostBackTrigger" BackColor="#CC0000" BorderStyle="Solid"  BorderColor="Maroon" ForeColor="White" />

          

        </ContentTemplate>

       

    </asp:UpdatePanel>

</asp:Content>

The code of the Preloader.ascx used to show progress info:

<%--A simple Div that shows us "Loading" message.--%>

<div style="top:150px; width:100%; left:0px; display:block;position:fixed; z-index:2000;" align="center" >

    <table style=" width:600px; height:200px; z-index:2002; background-color:#f9f9f9; border-style:solid; border-color:Grey; ">

        <tr>

            <td valign="middle" align="center">

                <h1 style=" color:Navy">Loading: 5000 milliseconds...</h1>

            </td>

        </tr>     

    </table>

</div>

So, if we click this button with a full PostBack action - we don't see any Progress. If we click the button without the full PostBack action - we see this progress information. But it is not friendly for user, because in one time he sees progress info, in another time he doesn't see it. It would be much better if user has ability to see progress information for each situation.

Investigation

There are many ways to solve this situation. But I select simplest way, as for me. And it is emulation of the showing of the progress info for Update Panel.

Solution 

The idea – to run JavaScript code by clicking our FullPostBack button. In any cases this code will be runned and will be executed before a moment when request of our page will be send to server and will be back.  We click button – and by JavaScript shows ProgressInfo. And until  request don’t return from the server – we see our ProgressInfo. Also we don’t need to think how to hide our ProgressInfo, because when our request returns from server – we will have reRander of our page and it will hide our ProgressInfo automatically.

Code in aspx file…

<div id="divPreloader" style="visibility:hidden;">

      <uc1:Preloader    ID="ucPre"  runat="server" />

</div>

Add this code above somewhere outside of the UpdatePanel.  The purpose of this code – to show  our progressinfo for our  FullPostBack. I placed it  above UpdatePanel. We will find this div by JavaScript and will show it. Yes, if you want you can find Preloader that is used by UpdatePanel inside. But I  prefer to have separate div for this.

And add OnClientClick="ShowProgress('divPreloader')" to our btnWithPostBackTrigger. ShowProgress – it is our JavaScript function that will show what we need.

Code in .vb file

Private Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        Dim PageLoadedHandlerScript As String = " function getObject (sId) { if(!sId) return null; else if (document.all && document.all[sId])  return document.all[sId];  else if (document.getElementById)  return document.getElementById(sId); else return null;}" _

         & " function ShowProgress(sId) { var dP = getObject(sId); if (dP != null) { dP.style.visibility = 'visible'; } }"

        Page.ClientScript.RegisterClientScriptBlock(Me.GetType, "getObjectScript", PageLoadedHandlerScript, True)

        btnWithPostBackTrigger.OnClientClick = "ShowProgress('divPreloader')"

End Sub

In this code we register our JavaScript on the page. There are two functions – the first function finds DOM element by id or name. The second function is our ShowProgress function that is used by btnWithPostBackTrigger. You can use your own JavaScript and jQuery if you need. 

That’s all. Hope it will help you.

to sources >>

The Safari’s and Chrome’s problems with Report Viewer 2010 + UpdatePanel + SharePoint 2010 or Uncaught Sys.ScriptLoadFailedException: Sys.ScriptLoadFailedException: The script '.....' could not be loaded. Uncaught TypeError: Cannot read property '_notified' of null.

Initial data
We have one WebPart for the  SharePoint 2010. In this WebPart we have one UpdatePanel. In this UpdatePanel  -  Microsoft Report Viewer 2010 is placed.

Situation
MS Report Viewer  works correct in the IE9.0 and in the Firefox. Report’s data being displayed correctly… But in the Chrome and in the Safari we have nothing.  Well, we see NOT active Report Viewer, without any content.  But if we remove UpadtePanel – all works  good in all browsers. I was trying to reproduce this issue by base website created in the VS2010 (UpdatePanel + Report Viewer) – and again all was good. There was not any problems.  But in the SharePoint2010 – no data in the ReportViewer.

Investigation
By the Chrome Dev Tool (F12) I found that the page had two JavaScript’s errors :

1. Uncaught Sys.ScriptLoadFailedException: Sys.ScriptLoadFailedException: The script '....' could not be loaded. 
2. Uncaught TypeError: Cannot read property '_notified' of null

And it was in the MicrosoftAjaxWebForms.js...

Solution
After internet’s revision I found that the same errors often occur in these browsers ( Safary and Chrome) if you use UpdatePanel/AjaxToolkit – all that uses  ScriptManager. MicrosoftAjax in one word… it looks like the problem is in the following - MicrosoftAjaxWebForms.js don't define WebKit browsers correctly
If you add following code on the page - all works correctly :

Sys.Browser.WebKit = {};
if (navigator.userAgent.indexOf('WebKit/') > -1) {
    Sys.Browser.agent = Sys.Browser.WebKit;
    Sys.Browser.version = parseFloat(navigator.userAgent.match(/WebKit\/(\d+(\.\d+)?)/)[1]);
    Sys.Browser.name = 'WebKit';
}

It looks like that correct JavaScript is present for these browsers, but definer is not present.  In any case these rows were solving my problems. But one question still present - why it was in the SharePoint2010 and was not present in the simple website created by VS2010… only one suggestion - different versions of the scripts for these products.

Hope it will help you.