U4-10374 - EntityService, ContentService and MediaService should use a prefix query for GetDescendants instead of a Contains query

Created by Shannon Deminick 31 Aug 2017, 14:52:38 Updated by Claus Jensen 06 Sep 2017, 07:12:19

Tags: Backport

Relates to: U4-10385

Subtask of: UAASSCRUM-1073

These 3 services when querying descendants use a Contains query: "LIKE %Something%" on the Path column which will basically never use the DB index on that column. This can easily be changed to a prefix query: "LIKE Something%" on the Path column which will use the index. This just means that we need to make one simple path query for the current item.

Then need to benchmark this with a site with a huge db and running the Examine rebuild process because this is what Examine uses to populate it's indexes.

1 Attachments

Comments

Shannon Deminick 31 Aug 2017, 14:57:00

PR: https://github.com/umbraco/Umbraco-CMS/pull/2164


Shannon Deminick 05 Sep 2017, 05:21:27

Here's the results in the query profiler (when there's lots of data) . As you can see the prefix query is WAY more efficient than the contains query.


Priority: Normal

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 7.7.0

Sprint: Sprint 67

Story Points: 1

Cycle: