LightSwitch Tips & Tricks on Query Performance

Articolo
05/29/2012

LightSwitch allows you to connect to multiple data sources, relate them together, and create screens to edit the data. These data sources can be a variety of databases like SQL Server and SQL Azure, SharePoint, and custom RIA services. With the release of Visual Studio 11 Beta, the next version of LightSwitch also allows you to connect data services via OData which are often hosted remotely. When the size of the data in these data sources get too large, or the distance between them increases (i.e. Internet based data services), performance decreases. Contributing factors are the size of the data set coming down the wire is too big, the number of fields to search on in the table is high, or the size of the data in the tables has grown very large.

As the size of data grows and/or the length of the “wire” increases, it’s important to understand the affects these have on query performance. In this post I’d like to discuss some important considerations to make when designing your entities and queries in LightSwitch against large or remote data sets.

Default Searching Behavior

When you design your entities in LightSwitch, by default all string properties are searchable. This means that when displaying data in grids or lists, the search box will execute a query that compares all the string fields in the table for a possible match. For example, say I have a table of customers coming from the AdventureWorksLT database. If I search for “Adam” then all records with any mention of the word “adam” in any of its string fields are displayed.

There are 10 string fields in this table so the WHERE clause on the database query is:

WHERE ([LastName] LIKE N'%Adam%') OR ([FirstName] LIKE N'%Adam%') OR ([MiddleName] LIKE N'%Adam%') OR ([Title] LIKE N'%Adam%') OR ([Suffix] LIKE N'%Adam%') OR ([CompanyName] LIKE N'%Adam%') OR ([SalesPerson] LIKE N'%Adam%') OR ([EmailAddress] LIKE N'%Adam%') OR ([Phone] LIKE N'%Adam%') OR ([PasswordHash] LIKE '%Adam%') OR ([PasswordSalt] LIKE '%Adam%')

Similarly, when connecting to OData sources with LightSwitch in Visual Studio 11, you can see the query over HTTP to the remote data service. For instance, in the following example I have a search screen for movie & music titles coming from the Netflix OData service, sorted by name. Notice if I search for “Star Wars” that all records with any mention of the phrase “Star Wars” in any of its string fields are displayed.

Here’s the request sent to the OData service. (For more information on the query syntax see the URI conventions supported in OData queries.)

GET https://odata.netflix.com/v2/Catalog/Titles()?$orderby=Name&$filter=substringof('Star%20Wars',Id)%20or%20substringof('Star%20Wars',Name)%20or%20(substringof('Star%20Wars',ShortName)%20or%20substringof('Star%20Wars',Synopsis))%20or%20(substringof('Star%20Wars',ShortSynopsis)%20or%20substringof('Star%20Wars',Url)%20or%20(substringof('Star%20Wars',Rating)%20or%20substringof('Star%20Wars',Type)))%20or%20(substringof('Star%20Wars',WebsiteUrl)%20or%20substringof('Star%20Wars',NetflixApiId)%20or%20substringof('Star%20Wars',TinyUrl))&$skip=0&$top=45&$expand=Movie,Series,Season,Disc&$select=*,Movie/*,Series/*,Season/*,Disc/*

In this case it can take over 15 seconds to display the results depending on my connection speed to this public OData service. We can speed up both of these queries considerably by limiting the number of properties that must be searched. You can do that by opening up the entity in the Data Designer and marking fields “Is Searchable” in the properties window.

You can also uncheck this for an entire entity. If you do, then when you add the entity to screens, the screen query will have “Support search” unchecked, which removes the search box from the UI.

Keep in mind that the “Is Searchable” property on the entity controls the behavior regardless of what you do in the UI. So if you turn “Support search” on in the screen designer, but it is unchecked in the data designer, then the entity will not be searchable and a message will appear to the user that attempts to perform a search in the UI.

In the case of customer above we don’t need to waste our time searching through properties we’re never going to display to the user like password information. And in the case of the OData service data source, if I decide to only allow searching on the Title’s Name & Synopsis then the query will now be much quicker.

GET https://odata.netflix.com/v2/Catalog/Titles()?$orderby=Name&$filter=substringof('Star%20Wars',Name)%20or%20substringof('Star%20Wars',Synopsis)&$skip=0&$top=45&$expand=Movie,Series,Season,Disc&$select=*,Movie/*,Series/*,Season/*,Disc/*

NOTE: In Visual Studio 11 Beta, the default behavior for OData sources is the same as database data sources. However we are changing this at final release where not all properties will have “Is Searchable” checked by default, so that querying OData sources will be much quicker.

Data Set / Page Size

On the screen designer you can also set the page size to control the size of the result sets that are returned. Here you can fine-tune how many rows of data will come down per page by selecting the screen query and then setting the number of items to display per page in the properties window. By default 45 rows per page are brought down. This helps control the bandwidth you are using when bringing results down from the server to the client.

Auto Executing Queries

By default LightSwitch also auto-executes queries for you. This means you don’t need to do anything to have data load into screens. Sometimes, however, you can make considerable performance gains by controlling this yourself. For instance, if you are creating a custom search screen with many optional parameters you may want to uncheck “Auto Execute Query” to allow users to first specify search criteria and then issue the search at once.

For instance, say we want to create our own search screen for titles and only allow searching on the Name and the Synopsis. We first create a query that makes both of these parameters optional. (For more information see: Creating a Custom Search Screen in Visual Studio LightSwitch)

When we use the query on a screen, uncheck the “Auto execute query” and then add a command button that the user can click to execute the search once they enter all of the optional parameters they want to search. Then we simply write this code to load the results:

 Private Sub Search_Execute()
    Me.TitleSearch.Load()
End Sub

Static Spans

Static spans have been around since LightSwitch V1. These are used if you want to make sure to include (or exclude) related entities in the query that loads your data. You typically only need to use this if you are using the related data in your screen code because LightSwitch can see if you are using related data on the screen itself within the content tree and will automatically include this data for you. To control this, first click the “Edit Query” link on the screen query.

Then in the properties window, select “Manage Included Data” to select what data to include.

More Tips & Tricks

These are just some of the query-related things you can do within LightSwitch to help performance. Of course there are a lot of factors outside LightSwitch that can affect performance, like the speed of your data sources and services you are using. For more tips and tricks see:

Enjoy!

Comments

Anonymous
June 01, 2012
Great article, as always, Beth, thanks. Just out of curiosity, where did the 45 originate from as a default? Why not 50, or 25?
Anonymous
January 26, 2014
Hi, I'm having trouble getting this to work, and I'm starting to pull my hair out over it. I've posted on SO and MSDN about it (social.msdn.microsoft.com/.../how-to-load-related-entity-of-external-data-source-in-lightswitch-visual-studio-2013). I'm using exactly the technique here (static spans) to included related data on a screen, however when accessing it in code I get that the related entity is 'undefined'. The only caveats that I think may be causing the issue are that the data (the on-screen query and the related table) is linked in externally from SQL Azure, and that I am using the HTML client. Any ideas? Thanks
Anonymous
May 20, 2014
The comment has been removed
Anonymous
May 20, 2014
The comment has been removed
Anonymous
August 13, 2014
Hi Beth please see my stack exchange questions and see if you can confirm these behaviors/limitations and suggest possible workarounds? stackoverflow.com/.../expand-odata-query-one-to-many stackoverflow.com/.../odata-expand-across-multiple-datasource
Anonymous
April 08, 2015
The comment has been removed
Anonymous
April 09, 2015
The comment has been removed
Anonymous
April 27, 2016
I'm curious to find out what blog platform you happen to be using?I'm having some minor security issues with my latest site and I'd like to find something more risk-free. Do you have any solutions?
Anonymous
October 10, 2016
The comment has been removed
- Anonymous
  October 13, 2016
  clic 'edit query' and find the checkbox in the properties window

Condividi tramite