다음을 통해 공유

LightSwitch Tips & Tricks on Query Performance

  • 아티클

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.

image

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.

image

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.

image

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.  

image

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.

image

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.

image

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)

image

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.

image

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

image

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