U4-4383 - Improve list view performance with SQL paging and better SQL calls

Created by Shannon Deminick 10 Mar 2014, 22:34:34 Updated by andrew shearer 16 Jul 2015, 20:36:41

Relates to: U4-5365

Relates to: U4-6003

Subtask of: U4-5411

Currently the list view relies on the entity service which is slow due to the way paging and filtering works. We have to do a post filter on the collection due to the way the filter currently works.

Ideally we'd use SQL paging but OOTB that only works on one table so we'd only be able to sort on the data stored in umbracoNode. However there are some ways to do SQL paging with joins but we'd probably have to implement this specific to each db engine that is running and might not be fully supported in SQLCE. I know there'd be some better performing sql that we could use however so it is worth pursuing.

Filtering will be tricky using SQL the way we are currently doing it but is worth investigating to see if it is possible. The alternative is to have the list view backed by Examine but this poses its own issues. Some discussion about that here: http://our.umbraco.org/forum/umbraco-7/using-umbraco-7/46744-enable-list-view-limit

1 Attachments

Download RWC.ListView.Producer.zip

Comments

Matt Muller 11 Mar 2014, 00:00:26

Hey Shannon,

Thought I'd explain how I do it for the v6 package I created. It should be noted that my package (currently) does not support multiple document types in the list. I suspect that could be solved via a "UNION ALL" using my sql approach if the column counts are the same? One thing my package does support (that v7 does not) is using "some" custom properties in the list - I find this extremely powerful - for example you can add the "article date" to the list for a news item instead of the last time a person saved the node. This provides a listing that has a far better UX to the web producer type users as that is the way they think (IMO).

Obviously "linked nodes" (like MNTP) cannot be easily used in the list so I've stuck with stuff that is essentially in cmsPropertyData.data* columns. Supporting "linked stuff" would require a lot more work.

I already attached the core sql I am using and you can see the commented out bits of that statement to see how I am pulling in the custom properties from the cmsPropertyData.data* columns.

MY ARCHITECTURE: The sql I use is simply called from an ashx handler (I built this pre Web API support) by a modified version of the front end js library from www.datatables.net. So it looks after most of the ui stuff for me and all I need to do is spit json back out to it based on what it asks for (next page, filtered contents, etc). It also looks after sorting for me (or at least telling me which column to sort on) and I also implemented a "delay" to the filtering field so it waits 1/3 of a second for you to "finish typing" in the search box so I'm not doing un-neccessary filtering.

ISSUES YOU RAISED: The potential issues you mention with sql are below and how I solved them - I get 1-2 second response times using these approaches on 15,000 nodes.

PAGING: I don't use any of the fancy sql paging stuff. I do the sql call and load the results into a List<List> object (to support any # of columns) via a SqlDataReader. I then just use Skip and Take on that list to grab the "page" that I need to spit back to the client as json. I think this approach should work across all db's you support?

FILTERING: I allow filtering on any of the "built in" umbraco fields that are strings (umbracoNode.[text]) or any of the custom fields that the user specified to be a column in the list. Again I use the List<List> object I populated from the db and do a simple linq statement using where and .Contains on each "string" column that I know of.

At the very least you should be able to use the above approach without custom properties in the list and get far better performance than you currently are.

Let me know if you have any questions.

Regards, Matt


Matt Muller 11 Mar 2014, 00:03:58

Actually UNION ALL is not needed if you are not using the custom property approach - you just need to change the following sql:

cmsContentType.alias = 'resource'

to

cmsContentType.alias in('blogEntry', 'newsletter', 'resource')

to support 3 doc ype children


Shannon Deminick 11 Mar 2014, 00:08:07

Thanks Matt! I did see your other post which inspired me to create this issue :)

When I get time I'll deep dive into this. Currently we are using the entity service to do the querying which "should" be pretty much the same efficiency as the query you posted but I'll have to dive into that in the coming weeks to see why the current list view is 'slow' since in theory it shouldn't really be much slower than your query without sql paging.

I'd like to get sql paging going for this though - there are some limitations there but it would be ultra fast and scaleable to any number of entries.

Stay tuned, will reply with details in the coming couple weeks.


Matt Muller 11 Mar 2014, 00:13:36

Thanks Shannon - what's the likelihood of "Custom Properties" making it into the fix? That decision will drive whether I need to port my v6 package over to v7.


Shannon Deminick 11 Mar 2014, 00:17:33

At a guess I'd assume that might have to wait until 7.2 but I can't say for sure until I get to looking at this first. Custom properties have their own challenges as we have to be able to define which properties to list and where we store this configuration (i.e. at the data type level - but currently List View's aren't exposed as data types).


Shannon Deminick 16 Sep 2014, 01:23:17

Custom properties are in there, but you cannot sort by them - it's really not possible unless we do some horribly performant sql queries. Maybe in the future we can figure that out in a nicer way


Mads Krohn 02 Dec 2014, 14:56:42

Sorting on a custom property would be hugely useful :(

So, the reason why you don't just use Lucene/Examine as data source for the list view, is that the index might become stale when doing bulk operations, correct ? Is that the only issue, or ?

Would it be possible to have the main list view data loaded from sql as it is now and simply use Examine to sort/search on the items loaded by the database ?

Is there a way in Lucene/Examine to know if an index is stale or not ? If so, I guess you could simply display a message if that was the case, so people would know why search/sorting would be wrong for a few moments while the index catches up.

Just some thoughts, would really like that custom sort functionality :P

Also, it would be really useful to somehow be able to provide custom logic for the individual fields when sorting/searching, instead of just working on plain text/dates/ints.


Shannon Deminick 02 Dec 2014, 22:20:15

@sniffdk this has been briefly discussed here : http://issues.umbraco.org/issue/U4-5643#comment=67-16712 but I'll add some details...

  • It's true, I'd rather not rely on Lucene as an integral data source
  • But... SQL paging doesn't support sorting on a property value, however I haven't fully exhausted this option as there might be some logic that can make this possible just not sure yet
  • In that comment I mentioned that Lucene does not support paging which is true. However, Lucene doesn't actually return the entire result set so when i said 'it means it has to load everything up to that point' that isn't actually true. Lucene returns pointer values that you can skip over so it should be ok so long as we use it properly for custom paging.... Just means we can't use normal Linq Take/Skip as that would be horribly inefficient and consume a lot of memory. I'll mention @crumpled_jeavon here as I'm sure he'll be interested in this topic too.
  • If you guys want to create a separate issue about sorting based on custom property values, feel free to do so, I'm just not sure what it's priority will be because it will have some large complexities... and I have a work around for you below:

There's a way you can achieve this now in 7.2 if you want. Create a custom list view database table that holds all of the data that you want to show in your list view, fully equipped with the data type (int, date, etc.. ) that you want for columns. Use events to keep this table in sync. You can the use SQL paging, sorting, etc.. to your hearts desire on this table. Create a custom list view property editor which you can call something cool... you can copy/paste the current one and then override the how it gets/sorts the data using your db table. Then in the list view data type that you are rendering (either the defaults or a custom one), you can change the rendered property editor to be your custom one.


Matt Muller 26 Feb 2015, 07:02:07

@Shandem - thanks for the hint on how to do this now in 7.2 - have it all working as a custom list view with sorting on custom properties!


Mads Krohn 01 Mar 2015, 19:29:05

Oih, never got around to take this further, Matt, any chance you could elaborate on your solution?


Matt Muller 01 Mar 2015, 19:59:52

Hey Mads - I think you're pretty savvy, so hopefully you can work this out from my pretty heavily modified listview - files attached. I basically reverse-engineered the current listview and created a custom property editor with some modifications. Dump them into /app_plugins/rwc.listview.producer.

The namespaces are all hard-coded and this could certainly be made more generic for applying to other listviews. The bonus is that for this listview i also added cookies that remember your last order by state and it allows orderby on any column.

The main part you'll need to change is the custom call out to: /umbraco/backoffice/api/listviewproducer/getchildren/ which is in listview.controller.js on line 76

So once you change the above service call, you'll need to return json - here's a very basic hard coded result - from there you can implement whatever you want on the server side, including grabbing stuff from lucene or wherever you want.

Sorry if code below is not formatted correctly - wikimarkup preview doesnt seem to work.

public JObject GetChildren(int id, int pageNumber = 0, int pageSize = 0, string orderBy = "SortOrder", Direction orderDirection = Direction.Ascending, string filter = "") { JObject json = new JObject(); JArray items = new JArray(); items.Add(new JObject() { new JProperty("id", 1), new JProperty("name", "matt"), new JProperty("published", true), new JProperty("icon", "icon-globe"), new JProperty("contentTypeAlias", "Producer"), new JProperty("updateDate", DateTime.Now.ToString("yyyy-MM-dd")), new JProperty("properties", new JArray( new JObject() { new JProperty("alias","reviewedForWeb"), new JProperty("value",1) } ) ) }); json.Add(new JProperty("items", items)); json.Add(new JProperty("pageNumber", 1)); json.Add(new JProperty("pageSize", 1)); json.Add(new JProperty("totalItems", 1)); json.Add(new JProperty("totalPages", 1));

        return json;
    }


Matt Muller 01 Mar 2015, 20:18:47

I should also note that the json that is returned above assumes you setup the listview with a single column with an alias of reviewedForWeb


Shannon Deminick 01 Mar 2015, 22:36:39

See here: https://github.com/umbraco/Umbraco-CMS/pull/591

However i need to review the performance of this


andrew shearer 16 Jul 2015, 05:02:12

hi - is this ticket to do with being able to sort a List View by a custom field (when that custom field is a primitive umbraco type like Date)? thanks


Shannon Deminick 16 Jul 2015, 07:34:44

No, this ticket is just about performance, but this one : U4-6003: List View - Order By Custom Property is about that


andrew shearer 16 Jul 2015, 20:36:41

ok thanks for confirming Shannon. That's what i thought but then realised there was a small chance U4-6003 was about being able to set a custom field as the default 'order' for the listing rather than the content editor being able to 'sort' the list grid by one of the custom columns.


Priority: Normal

Type: Task

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.0.0, 7.1.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4

Due in version: 7.2.0

Sprint:

Story Points:

Cycle: