LightSwitch Tips & Tricks on Query Performance

LightSwitch Tips & Tricks on Query Performance

Rate This
  • Comments 7

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 http://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 http://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!

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • Great article, as always, Beth, thanks. Just out of curiosity, where did the 45 originate from as a default? Why not 50, or 25?

  • 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

  • If you disable “Auto Execute Query”, you may disable “Support Sorting” as well, with a safe conscience, because it obviously relies on that feature.

    visualstudio.uservoice.com/.../5952775-always-auto-execute-query-on-column-sorting

  • If you disable “Auto Execute Query”, you may also wish to disable “Support paging” and “Support search” as they become useless, as well as "Support Sorting".

  • 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

  • Hello,

    1.) If you disable "Auto Execute Query" paging doesn't work any more. That makes it worth. If you have 20.000 rows in a table and paging is disabled all 20.000 rows get loaded and displayed which will most likely cause a lock of the Screen. So that doesn't work

    2.) The key issue is the way the string search is being coded in SQL. Here a full like (contains) will be used ('%'+<field>+'%' = <serachpattern>). That will include all resulte where the search pattern appears somewhere in the field, which is the default desired behavioure. Unfortenately this will prevent SQL databases to use an index on the field to speed up the search. Hence a full table scan (or a index scan) will be peformed being VERY slow. It would be really great if the search behaviour could be configured:

    - Equals: use <field>=<search pattern>

    - Ends with: use <field>+'%' = <search patthern>

    - Contains: '%'+<field>+'%' = <serachpattern>

    For many cases contains does not add value. E.g. a table with users where you want to search for the personal ID number - any pattern machting is useless.

    But as Lightswitch is yet another dieying Technology by MS I guess we won't see this any soon in the product.

  • @some thoughts -- If you uncheck Auto Execute Query then you need to specify your own custom search. See blogs.msdn.com/.../creating-a-custom-search-screen-in-visual-studio-lightswitch.aspx This allows you to create the query exactly how you want using the query designer or even via code. The paging not working sounds like a bug. Can you please submit that to connect.microsoft.com/visualstudio and let us know what version you are using.

Page 1 of 1 (7 items)