U4-8754 - Single content queries like Get(int id) for content, media and members needs to execute a TOP 1 (or LIMIT 1)

Created by Shannon Deminick 20 Jul 2016, 14:18:46 Updated by Shannon Deminick 04 Aug 2016, 13:07:02

This will speed up queries a lot, currently we do something like:

        protected override IMedia PerformGet(int id)
            var sql = GetBaseQuery(false);
            sql.Where(GetBaseWhereClause(), new { Id = id });
            sql.OrderByDescending<ContentVersionDto>(x => x.VersionDate);

            var dto = Database.Fetch<ContentVersionDto, ContentDto, NodeDto>(sql).FirstOrDefault();

            if (dto == null)
                return null;

            var content = CreateMediaFromDto(dto, dto.VersionId, sql);

            return content;

which will fetch all versions and put into memory and then just pick the first, we can make this much better.


Claus Jensen 02 Aug 2016, 10:18:38

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

I've updated a bunch of the repositories to use this.

Repositories using custom maps/relators fails with this change so those are so far left unchanged (User, Macro & Dictionary). Also left repositories using GetAll caches unchanged as that would of course conflict with this.

All unit tests related to this passed. Tested various backoffice tasks and it seems to work as intended on SQL/SQLCE.

Testing would be to test normal backoffice work creating/modifying content, media, members and all that.

Shannon Deminick 04 Aug 2016, 13:06:29

Great!!! :)

Priority: Normal

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 7.4.3

Due in version: 7.5.0

Sprint: Sprint 39

Story Points: