Slow Page Load Speeds --- Due to large dataset & SPServices.SPCascadeDropdowns

Sep 30, 2014 at 2:31 AM
Edited Sep 30, 2014 at 12:59 PM
Hello Marc & All-
So I've been working on a project to track order deliveries and delivery truck loads. This is a parent->child (load->order) relationship of two lists, simple right? Well that is before the BizAnal-ist gets involved!! So now it is a series of 22 lists across 4 site (sub-site) tiers. The data is feeding from list to list rather well, I had to create 35-40 site columns to allow look-ups across the site boundaries. Now, because I TRUST my users to misspell or abbreviate names of cities, states, stores, and so forth; I've populated the countries, states, counties, and cities. I now know that there are more than 3100 counties, 29,000 cities, and 42,000 zip codes in the United States. I've set up a active/inactive flag column on all of my lists. Given the very large data set sizes, I'm trying to tweak my scripts to only populate child data, once the parent is selected. My javascripting kung-fu, is very far from mastered, so you advice would be much appreciated, these 45-70 second page loads are kicking my butt!!

Additionally, I have been placing this code on the new and edit forms, inside a script editor webpart (running SharePoint 2013 Standard on-prem). I've been asked if the users can leverage the quick edit display forms, and make use of the scripting cascades.

Relative URLs to each sub-site
/teams - T1
/teams/fldops/nw - T2
/teams/fldops/nw/nw04 - T3
/teams/fldops/nw/nw04/dellogs - T4

Deliveries
  • delvLoads - T4
  • delvOrders - T4
    Locations
  • addrRegions - T1
  • addrAreas - T1
  • addrStores - T1
  • addrCountries - T1
  • addrStates - T1
  • addrCounties - T1
  • addrCities - T1
  • addrZipCodes - T1
    Data
  • locaCustomers - T2
  • locaTrucks - T3
  • locaHelpers - T3
  • locaSalesTeams - T3
  • locaLoadBuilders - T3
  • locaDrivers - T3
  • locaJobSites - T3
    Settings
  • locaTruckTypes - T3
  • locaOrderTypes - T3
  • locaOrderStatus - T3
  • locaLoadStatus - T3
  • locaLoadTypes - T3
This is longest script I have, calling the most data into the page, and as a result has the worst page load times. I want to filter out any values with a "flag" set to no. So as a result, I've used CAMLQuery: "<Eq><FieldRef Name='RoutingEnabled' /><Value Type='Boolean'>1</Value></Eq>", or CAMLQuery: "<Eq><FieldRef Name='Available' /><Value Type='Boolean'>1</Value></Eq>", in several places. I've had problems leading my 3 or 4 level cascades with the cascade function, so I've cheated and started off each with FilterDropdown. I feel like there is a balance between initial page load speed and the waits from the recursive reload queries following a click event. I just don't know how to toggle between the modes, to see which method works best for my case.


My Sample Script
<script language="javascript" src="/_layouts/15/PBCustomJsFiles/jquery.min.js"></script>
<script language="javascript" src="/_layouts/15/PBCustomJsFiles/jquery-ui.min.js"></script>
<script language="javascript" src="/_layouts/15/PBCustomJsFiles/SPServices.js"></script>
<script language="javascript" src="/_layouts/15/PBCustomJsFiles/SPServices.min.js"></script>

<script language="javascript" type="text/javascript">
    $(document).ready(function() {
        $().SPServices.SPFilterDropdown({
            relationshipWebURL: "/Teams",
            relationshipList: "addrRegions",
            relationshipListColumn: "Title",
        //  relationshipListSortAscending: true,
        //  relationshipListSortColumn: "",
            columnName: "Region",
        //  listName: $().SPServices.SPListNameFromUrl(),
            promptText: "Choose Region...",
        //  noneText: "(None)", 
            CAMLQuery: "<Eq><FieldRef Name='RoutingEnabled' /><Value Type='Boolean'>1</Value></Eq>",
        //  CAMLQueryOptions: "<QueryOptions></QueryOptions>",
        //  completefunc: null,
            debug: false
        });
    
        $().SPServices.SPCascadeDropdowns({
            relationshipWebURL: "/Teams",
            relationshipList: "addrAreas",
            relationshipListParentColumn: "Region_x0020_Name",
            relationshipListChildColumn: "Title",
            relationshipListSortColumn: "ID",
            parentColumn: "Region",
            childColumn: "Area",
            CAMLQuery: "<Eq><FieldRef Name='RoutingEnabled' /><Value Type='Boolean'>1</Value></Eq>",
        //  CAMLQueryOptions: "",
        //  listName: $().SPServices.SPListNameFromUrl(), 
            promptText: "Choose Area...",
        //  simpleChild: false,
        //  selectSingleOption: false,
        //  matchOnId: false,
        //  completefunc: null,
            debug: false
        });
    
        $().SPServices.SPCascadeDropdowns({
            relationshipWebURL: "/Teams",
            relationshipList: "addrStores",
            relationshipListParentColumn: "Area_x0020_Name",
            relationshipListChildColumn: "Title",
            relationshipListSortColumn: "ID",
            parentColumn: "Area",
            childColumn: "Store",
            CAMLQuery: "<Eq><FieldRef Name='RoutingEnabled' /><Value Type='Boolean'>1</Value></Eq>",
        //  CAMLQueryOptions: "",
        //  listName: $().SPServices.SPListNameFromUrl(), 
            promptText: "Choose Store...",
        //  simpleChild: false,
        //  selectSingleOption: false,
        //  matchOnId: false,
        //  completefunc: null,
            debug: false
        });
//////////////////////////

        $().SPServices.SPFilterDropdown({
            relationshipWebURL: "/Teams",
            relationshipList: "addrStates",
            relationshipListColumn: "Title",
        //  relationshipListSortAscending: true,
        //  relationshipListSortColumn: "",
            columnName: "State",
        //  listName: $().SPServices.SPListNameFromUrl(),
            promptText: "Choose State...",
        //  noneText: "(None)",
            CAMLQuery: "<Eq><FieldRef Name='RoutingEnabled' /><Value Type='Boolean'>1</Value></Eq>",
        //  CAMLQueryOptions: "<QueryOptions></QueryOptions>",
        //  completefunc: null,
            debug: false
        });
    
        $().SPServices.SPCascadeDropdowns({
            relationshipWebURL: "/Teams",
            relationshipList: "addrCounties",
            relationshipListParentColumn: "State_x0020_Name",
            relationshipListChildColumn: "Title",
            relationshipListSortColumn: "Title",
            parentColumn: "State",
            childColumn: "County",
            CAMLQuery: "<Eq><FieldRef Name='RoutingEnabled' /><Value Type='Boolean'>1</Value></Eq>",
            //CAMLQueryOptions: "",
            //listName: $().SPServices.SPListNameFromUrl(), 
            promptText: "Choose County...",
            //simpleChild: false,
            //selectSingleOption: false,
            //matchOnId: false,
            //completefunc: null,
            debug: false
        });

        $().SPServices.SPCascadeDropdowns({
            relationshipWebURL: "/Teams",
            relationshipList: "addrCities",
            relationshipListParentColumn: "County_x0020_Name",
            relationshipListChildColumn: "Title",
            relationshipListSortColumn: "Title",
            parentColumn: "County",
            childColumn: "City",
            CAMLQuery: "<Eq><FieldRef Name='RoutingEnabled' /><Value Type='Boolean'>1</Value></Eq>",
            //CAMLQueryOptions: "",
            //listName: $().SPServices.SPListNameFromUrl(), 
            promptText: "Choose City...",
            //simpleChild: false,
            //selectSingleOption: false,
            //matchOnId: false,
            //completefunc: null,
            debug: false            
        });
        
        $().SPServices.SPCascadeDropdowns({
            relationshipWebURL: "/Teams",
            relationshipList: "addrZipCodes",
            relationshipListParentColumn: "County_x0020_Name",
            relationshipListChildColumn: "Title",
            relationshipListSortColumn: "Title",
            parentColumn: "County",
            childColumn: "ZipCode",
            CAMLQuery: "<Eq><FieldRef Name='RoutingEnabled' /><Value Type='Boolean'>1</Value></Eq>",
            //CAMLQueryOptions: "",
            //listName: $().SPServices.SPListNameFromUrl(), 
            promptText: "Choose ZipCode...",
            //simpleChild: false,
            //selectSingleOption: false,
            //matchOnId: false,
            //completefunc: null,
            debug: false            
        });
//////////////////////////

        $().SPServices.SPLookupAddNew({ 
            lookupColumn: "City",
            promptText: "Add new City "
        });
        $().SPServices.SPLookupAddNew({ 
            lookupColumn: "ZipCode",
            promptText: "Add new {0} "
        });
    });
</script>
Thank you, for any help you can give!!
Jamie Hill
Coordinator
Oct 6, 2014 at 5:49 PM
Jamie:

With the data volumes you have, you're probably going to see slow performance even without the added scripts. You should time things with no script and use that as your baseline.

You may end up wanting to build your own "form controls" purely out of script and your own markup. I've done this recently where I used a Single line of text column and the select2 jQuery plugin to collect and store multi-select values from an underlying list that has over 12,000 items in it. The SharePoint JavaScript functions that drive things like multi-select lookup columns are darn laggy to start with.

M.
Oct 6, 2014 at 6:20 PM
Thank for the response, Marc..

Is there a way to use the completefunc: or CAMLQuery: to feed the response of one selection (parent) to next selection (child) of a cascade? Further, is there a way to limit/constrain the preload of data to the page, to only push the 1st selection (parent) data to the page initially, and requesting the 2nd selection (child) data in subsequent requests?

JMH
Coordinator
Oct 6, 2014 at 6:40 PM
Not as those functions are written. When you call them, they set up everything in the form for the subsequent user actions during the form lifespan.

M.