Lessons Learned: Improving Business Connectivity Services Performance

by John Bonfardeci

Are you developing a client-side JavaScript application on the SharePoint platform? Is your data in an external database such as Azure or SQL Server? Are you using views and/or stored procedures to join two or more tables? If you’ve answered “yes” to these questions, this lessons learned article is for you.

It’s relatively simple to create external content types in SharePoint Designer via Business Connectivity Services (BCS) and many articles have been published covering the steps. Furthermore, you can filter data with the REST $filter parameter. However, SQL server will return all the data in a view or table and only then will SharePoint filter the results. For example, if a view returns 10,000 rows and you only need 10, this can be an obvious performance issue. To compound the problem, SharePoint will throw an error if your external list returns more than 2,000 rows.

You can overcome the 2,000-row limit by setting a default Limit filter on your external content type in SharePoint Designer and include a CAML query in your AJAX POST request as outlined in this Microsoft blog post. Now in simple cases, filtering a view in a database can be just as fast as filtering with parameters in a stored procedure, but not always. Using a CAML query to filter results from a view isn’t the same and it can be much slower depending on the query and how much data a view returns.

With a recent project, I had a payroll report view with 14 joins, complex aggregation, and calculations requiring 3 common table expressions (CTE). The first CTE joined and grouped the tables the report required, and the other 2 CTEs calculated the total payroll, overtime, and average OT rates from multiple timesheets for one client with multiple care attendants among different job codes.  Filtering rows from the view took ~45 minutes (Figure A) as opposed to 7 seconds (Figure B) with the same code in a stored procedure with filter parameters. The dramatic speed increase was directly due to passing filter parameters to the first CTE in the stored procedure, something that isn’t possible with views – except for using the SPID approach, but this is complicated. Time comparisons between the view and the stored procedure are shown below.

Filtered view in SMSS

Figure A. Filtered view results in SSMS. Time to complete ~45 minutes.

Stored Procedure results in SSMS

Figure B. Stored procedure results in SSMS. Time to complete in 7 seconds.

For such scenarios, you can send parameters to a SharePoint external list within a CAML query that includes your parameters for a stored procedure. SQL Server will only return the results you need, potentially improving response time significantly. Utilizing this powerful feature also helps to enable developers to write full-blown CRUD applications without a custom, backend REST API service to handle database operations. There are still situations where you may need a custom backend service to handle more complex logic, but for CRUD operations you can develop rich data-driven, single-page applications with the framework of your choice (React, Angular, Knockout JS, etc.) with only JavaScript, SharePoint REST services, and well-designed stored procedures!

The remainder of this article will detail how to connect SharePoint to a stored procedure, step-by-step.

Prerequisites

Before you can set up an external content type to a database, your SharePoint site must have Business Connectivity Services (BCS) and Secure Store Service (SSS) enabled. Additionally, you’ll need either an Active Directory account or username and password for access permissions to the database. This account will be stored in SSS and used to access your database. If you don’t have access to your Central Administration site, work with your SharePoint administrator to set up these features and credentials.

There are numerous online resources for learning how to set up BCS and SSS, but this article is focused on the specific steps for executing stored procedures, and therefore these steps are outside the scope of this article.

To learn how to setup BCS, please refer to the article, SharePoint Online to Azure SQL Database: Part 1 

Going forward, it’s assumed you have BCS and SSS set up in your SharePoint environment already.

1. Sample Dataset

For this example, we’ll use the dataset from the 2018 World Happiness Survey which can be downloaded here. After downloading, save the first tab of this Excel workbook as a tab delimited file titled, “WorldHappiness2018” somewhere on your local drive. You can use the Import Flat File wizard in SQL Server Management Studio (SSMS) to import the dataset. Right-click on your database and select Tasks > Import Flat File… (Figure 1).

Screenshot of SSMS task

Figure 1.

 

1a) In the Specify Input File section of the wizard, click Browse… to locate the file to import. Click the Next button at the bottom of the wizard (Figure 1a).

Screenshot showing specify input file in SSMS

Figure 1a.

 

1b) In the Preview Data section of the wizard, you should now see a preview of the dataset (Figure 1b). Click Next again.

Screenshot for Preview Data

Figure 1b.

 

1c. In the Modify Columns section of the wizard, be sure to check Allow Nulls on all columns. The import wizard only looks at the first 10 rows to determine the datatypes and if they’re nullable. If you don’t check Allow Nulls, you’ll run into import errors (Figure 1c). Click Next.

Screenshot for Modify Columns

Figure 1c.

 

1d) After the import operation is complete, press the Close button (Figure 1d i).

Screenshot of Operation Complete

Figure 1d i.

You should be able to see the new table within SSMS (Figure 1d ii).

Screenshot of WorldHappiness2018 table in SMSS

Figure 1d ii.

2. Write the Stored Procedure

2a) We’ll write the following stored procedure to filter the table by @country and an optional @year parameter.

Take note of the @dummy parameter. We’ll see how this is used to circumvent an obstacle when setting up the Read Item operation of the external content type in SharePoint Designer.

3. Create the External Content Type

Before proceeding, it’s worth mentioning Multi Factor Authentication (MFA) in SharePoint Designer. Referencing an excerpt from the aforementioned article,  SharePoint Online to Azure SQL Database

“Here is another gotcha that might get you.  If you attempt to open your site and get a message telling you that there is a problem with your account, then you probably need an App Password.  If your admin has set up multi-factor authentication (MFA) for your organization, then you will need an App Password.  How do you know if your admin has set up MFA? If you log in with a username and password and then get a text message sent to your phone to verify your login, MFA has been established.”

3a) In SharePoint Designer, open your SharePoint site’s URL and click on the External Content Type button to create a new external content type (Figure 3a).

Screenshot of External Content Type (Figure 3a)

Figure 3a.

3b) Name the external content type, “spWorldHappiness2018” then select Click here to discover external data sources and define operations (Figure 3b).

Screenshot of SharePoint Designer (Figure 3b)

Figure 3b.

 

3c) Expand the Routines folder. You should see the stored procedure we created in the previous step (Figure 3c).

Screenshot of SharePoint Source Explorer (Figure 3c)

Figure 3c.

 

3d) Right-click on the stored procedure and select New Read List Operation (Figure 3d).

Screenshot of SharePoint Designer New Read List Operaitons (Figure 3d)

Figure 3d.

 

3e) Enter the operation name. To keep it simple, I typically name the operation the same as the stored procedure. This will be what you’ll set the Name attribute to within the Method element of the CAML query (Figure 3e).

Screenshot of SharePoint Designer to setup properties for read list operation (Figure 3e)

Figure 3e.

 

3f) Within the Input Parameters section, select @country under Data Source Elements. On the right under Properties, click (Click to Add) next to Filter (Figure 3f).

Screenshot of Read List properties (Figure 3f)

Figure 3f.

 

3g) In the Filter Configuration window, click the New Filter radio button and enter, “Country” as the new filter name. You don’t need to change anything else. The filter will correspond to the @country parameter of the stored procedure. Click OK (Figure 3g).

Screenshot of Filter Configuration (Figure 3g)

Figure 3g.

 

3h) In the Input Parameters section, select @year under Data Source Elements. On the right under Properties, click (Click to Add) next to Filter (Figure 3h).

Screenshot of Input Parameters Configuration (Figure 3h)

Figure 3h.

 

3i) Within Filter Configuration, click the New Filter radio button and enter, “Year” as the new filter name (Figure 3i). Click OK.

Screenshot of Filter Configuration (Figure 3i)

Figure 3i.

 

3j) Finally, select @dummy and click Click to Add next to Filter. Select New Filter, enter “RowLimit” as the name then change the Filter Type to “Limit” (Figure 3j). Press OK.

Screenshot for setting @dummy parameter (Figure 3j)

Figure 3j.

3k) Set the Default Value to 2000. This is the maximum number of rows SharePoint will return without throwing an error (Figure 3k). Press Next.

Screenshot of setting default value (Figure 3k)

Figure 3k.

 

3l) Set RowNum as the identifier by selecting it and check Map to Identifier in the Properties section on the right (Figure 3l). Press Finish.

Screenshot for mapping to identifier (Figure 3l)

Figure 3l.

 

3m) For the second and last operation necessary to create an external list, right-click the procedure and select New Read Item Operation (Figure 3m).

Screenshot for New Read Item Operation (Figure 3m)

Figure 3m.

 

3n) Set the Operation name to “ReadItem” and the Display Name to “Read Item.” It doesn’t matter what you name it but remember that any spaces in the operation name will show up as ”%20” in the URL of the operation page. You’ll never need to access it and its existence is extraneous, but you need at least one Read List and one Read Item operation to save and external content type in SharePoint (Figure 3n). Click Next.

Read Item properties (Figure 3n)

Figure 3n.

 

3o) Select @dummy and check Map to Identifier. This is the primary need for the dummy variable. If your procedure doesn’t expect a parameter that has the same type as your primary key, you’ll need this workaround to just get past this step. Notice the two red warning messages at the bottom (Figure 3o).

Mapping @dummy parameter

Figure 3o.

 

3p) After selecting Map to Identifier, these warnings disappear (Figure 3p). Click Next.

Screenshot of selecting next button (Figure 3p)

Figure 3p.

 

3q) Select RowNum and check Map to Identifier on the right (Figure 3q). Press Finish.

Screenshot of RowNum within Read Item property window (Figure 3q)

Figure 3q.

IMPORTANT: Click the Save button in the upper-left corner of the SharePoint Designer window.

 

4. Create the External List

4a) In SharePoint designer, select External Content Types of the Site Objects section (Figure 4a).

Screenshot of External Content Types in navigation (Figure 4a)

Figure 4a.

 

4b) Locate “spWorldHappiness2018” and right-click it. Select External List to create a new external list (Figure 4b).

Screenshot of creating external list (Figure 4b)

Figure 4b.

 

4c) Name it, “WorldHappiness2018” and click OK (Figure 4c).

Screenshot of Create External List (Figure 4c)

Figure 4c.

 

5. Create the Test Form

5a) Somewhere in your SharePoint site (I chose Site Pages), create a new .aspx page. Right-click the file and select Edit File in Advanced Mode (Figure 5a).

Screenshot of creating new aspx file and editing in advanced mode (Figure 5a)

Figure 5a.

 

5b) Copy and paste the markup and JavaScript code below into the new .aspx file, overwriting all the default markup created by SharePoint Designer.

5c. Save the file and open in your browser. I recommend Google Chrome.

In the form, enter the name of a country in the Country field and optionally a year in the Year field. Press Get Data. After the asynchronous POST request completes, you should see a preview of the JSON returned below the form (Figure 5c).

Screenshot of rendered page (Figure 5c)

Figure 5c.

 

There it is! I was elated the first time I saw JSON returned from a stored procedure call from SharePoint. Unlike a regular REST response, notice all the numeric types have been returned as strings. You’ll need to convert the values to the correct types including Boolean, date, integer, and float.

While this is a very basic example using only one table, you can call procedures that execute complex business logic or perform complex joins among many tables/views. For example, you can utilize the OFFSET, ROWS, and FETCH NEXT commands in T-SQL to facilitate paging and dynamic sorting with very large datasets. In my next post I’ll show you how to implement this approach with Datatables.Net. Using this approach for a recent project, our team developed a complete application in SharePoint with only JavaScript, SharePoint REST services, the SharePoint JSOM library, and Azure SQL.