Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Dee 90 posts 274 karma points
    Aug 28, 2020 @ 09:02
    Dee
    0

    Performance: MemberService vs SQL | How To: SQL Query for Members

    Hey Guys,

    I really hope that someone has an idea to solve this.

    Current Scenario: I have a large amount of Members (30k), and for each category I need to show specific members, page by page, so that in fact for each page I am quering only 20 members and have a performant page load.

    What I so far tried, Option A:

    var members = Current.Services.MemberService.GetAllMembers()
                    .Where(x =>
                        (x.GetValue("published") == null || x.GetValue<bool>("published")) &&
                        (serviceIds.Length == 0 || (x.GetValue("services") != null && serviceIds.Intersect(x.GetValue<string>("services").Split(new string[] { "\r\n" }, StringSplitOptions.None).Select(s => s.Split(':')[0])).Any())) &&
                        (secondaryIds.Length == 0 || (x.GetValue("services") != null && secondaryIds.Intersect(x.GetValue<string>("services").Split(new string[] { "\r\n" }, StringSplitOptions.None).Select(s => s.Split(':')[0])).Any())) &&
                        (focusAreaIds.Length == 0 || (x.GetValue("focusAreas") != null && focusAreaIds.Intersect(x.GetValue<string>("focusAreas").Split(new string[] { "\r\n" }, StringSplitOptions.None).Select(fa => fa.Split(':')[1])).Any())) &&
                        (string.IsNullOrEmpty(model.ProjectSizeFilter) || (x.Properties["minimumProjectSize"].GetValue() != null && Convert.ToInt32(x.Properties["minimumProjectSize"].GetValue()) <= Convert.ToInt32(model.ProjectSizeFilter))) &&
                        (string.IsNullOrEmpty(model.HourRateFilter) || (x.Properties["hourRate"].GetValue() != null && x.Properties["hourRate"].GetValue().ToString().Equals(model.HourRateFilter))) &&
                        (string.IsNullOrEmpty(model.CompanySizeFilter) || !companySizeIsGreaterThanZero || (x.Properties["size"].GetValue() != null && x.Properties["size"].GetValue().ToString().Equals(model.CompanySizeFilter))) &&
                        (string.IsNullOrEmpty(model.ClusterFilter) || (x.GetValue<DateTime>("currentPeriodEnd") > DateTime.Now && x.Properties["clusters"].GetValue() != null && (new string[] { model.ClusterFilter }).Intersect(Regex.Replace(x.Properties["clusters"].GetValue().ToString(), @":(\d+)", "").Split(new string[] { "\r\n" }, StringSplitOptions.None)).Any())) &&
                        (string.IsNullOrEmpty(model.CityFilter) || (x.Properties["city"].GetValue() != null && x.Properties["city"].GetValue().ToString().ToLower().Contains(model.CityFilter.ToLower()))))
                    .Skip((model.PageIndex - 1) * model.PageSize).Take(model.PageSize);
    

    This is in fact quering all members first, and then doing the filtering based on where clauses, so not really useful.

    Option B:

        var members = Current.Services.MemberService.GetAll(model.PageIndex, model.PageSize, out long totalrecords)
            .Where(x =>
                (x.GetValue("published") == null || x.GetValue<bool>("published")) &&
                (serviceIds.Length == 0 || (x.GetValue("services") != null && serviceIds.Intersect(x.GetValue<string>("services").Split(new string[] { "\r\n" }, StringSplitOptions.None).Select(s => s.Split(':')[0])).Any())) &&
                (secondaryIds.Length == 0 || (x.GetValue("services") != null && secondaryIds.Intersect(x.GetValue<string>("services").Split(new string[] { "\r\n" }, StringSplitOptions.None).Select(s => s.Split(':')[0])).Any())) &&
                (focusAreaIds.Length == 0 || (x.GetValue("focusAreas") != null && focusAreaIds.Intersect(x.GetValue<string>("focusAreas").Split(new string[] { "\r\n" }, StringSplitOptions.None).Select(fa => fa.Split(':')[1])).Any())) &&
                (string.IsNullOrEmpty(model.ProjectSizeFilter) || (x.Properties["minimumProjectSize"].GetValue() != null && Convert.ToInt32(x.Properties["minimumProjectSize"].GetValue()) <= Convert.ToInt32(model.ProjectSizeFilter))) &&
                (string.IsNullOrEmpty(model.HourRateFilter) || (x.Properties["hourRate"].GetValue() != null && x.Properties["hourRate"].GetValue().ToString().Equals(model.HourRateFilter))) &&
                (string.IsNullOrEmpty(model.CompanySizeFilter) || !companySizeIsGreaterThanZero || (x.Properties["size"].GetValue() != null && x.Properties["size"].GetValue().ToString().Equals(model.CompanySizeFilter))) &&
                (string.IsNullOrEmpty(model.ClusterFilter) || (x.GetValue<DateTime>("currentPeriodEnd") > DateTime.Now && x.Properties["clusters"].GetValue() != null && (new string[] { model.ClusterFilter }).Intersect(Regex.Replace(x.Properties["clusters"].GetValue().ToString(), @":(\d+)", "").Split(new string[] { "\r\n" }, StringSplitOptions.None)).Any())) &&
                (string.IsNullOrEmpty(model.CityFilter) || (x.Properties["city"].GetValue() != null && x.Properties["city"].GetValue().ToString().ToLower().Contains(model.CityFilter.ToLower()))));
    

    This is really awkward, what Option B does is, quering all members first, although it's a paged query, it is not applying the paged quering on the where clauses, so it gets 20 members of all members first, and then does apply the where clauses, which is crappy. Due to the complex where clauses, i can not just apply the string filtering of the get all method.

    Option C is to directly use SQL: Unfortunately the documentation for the scope provider is heavily missing. So I am struggeling with this a bit, since i need the resultset to be a strongly typed IEnumerable<IMember> which just doesn't work.

    This SQL query is not the query above, since i need to get the right resultset first, before i complete the query:

        using (var scope = Current.ScopeProvider.CreateScope())
        {
            var sql = scope.SqlContext.Sql(@"
            SELECT m.*
            FROM
                (SELECT[Id] FROM dbo.umbracoNode WHERE uniqueid = 'D59BE02F-1DF9-4228-AA1E-01917D806CDA') AS mt
                    LEFT OUTER JOIN(SELECT NodeID, contentTypeId FROM dbo.umbracoContent) AS ml ON ml.contentTypeId = mt.id
                    LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentTypeId
                    LEFT JOIN[dbo].[umbracoDataType] AS dt ON pt.datatypeID = dt.NodeId
                    LEFT JOIN dbo.cmsMember AS m ON m.NodeID = ml.NodeID
                    LEFT JOIN dbo.umbracoContentVersion AS cv ON cv.nodeId = m.nodeId
                    LEFT JOIN dbo.umbracoPropertyData AS d ON d.versionId = cv.id AND d.propertytypeid = pt.id
    
                    --INNER JOIN #Pattern ON d.textValue COLLATE DATABASE_DEFAULT LIKE ServiceIds COLLATE DATABASE_DEFAULT
                    LEFT JOIN dbo.cmsMember2MemberGroup AS xmg ON xmg.Member = m.NodeID
                    LEFT JOIN dbo.umbracoNode AS g ON g.id = xmg.MemberGroup
                    LEFT JOIN dbo.umbracoNode AS n ON n.id = m.nodeId");
    
            var test = scope.Database.Query<IEnumerable<IMember>>(sql);
    
            scope.Complete();
        }
    

    Since the Query Method or ExecuteScalar method needs a type, I don't know how to define the type to be a List of Members here.

    "Cannot create POCO 'System.Collections.Generic.IEnumerable`1[[Umbraco.Core.Models.IMember, Umbraco.Core, Version=8.0.0.0, Culture=neutral, PublicKeyToken=null]]'. It may have no parameterless constructor or be an interface or abstract class without a Mapper factory."

    If anyone has an idea how to get the job done with the member service itself, but in a performant way, please share also.

    Thanks,

    Dee

  • Steve Morgan 1208 posts 3922 karma points c-trib
    Aug 28, 2020 @ 11:36
    Steve Morgan
    0

    Hi,

    Could you use the Examine Member index?

    Steve

  • Dee 90 posts 274 karma points
    Aug 28, 2020 @ 11:43
    Dee
    0

    Hey,

    yes, this is what I am currently working on. For my understanding, I need to add all the needed additional fields to the index.

    Do you have any current examples of doing this and searching the index based on custom fields?

    This peace of work should add one of the property types named "leadsEmail" to the index, but it doesn't work, when I search for a value of this field within the index:

    public class CustomizeIndexComposer : ComponentComposer<CustomizeIndexComponent> { }
        public class CustomizeIndexComponent : IComponent
        {
            private readonly IExamineManager _examineManager;
    
            public CustomizeIndexComponent(IExamineManager examineManager)
            {
                _examineManager = examineManager;
            }
    
            public void Initialize()
            {
                // get the external index
                if (!_examineManager.TryGetIndex(UmbracoIndexes.MembersIndexName, out IIndex index))
                    return;
    
                // add a custom field type
                index.FieldDefinitionCollection.TryAdd(new FieldDefinition("leadsEmail", FieldDefinitionTypes.FullText));
    
                //// modify an existing field type (not recommended)
                //index.FieldDefinitionCollection.AddOrUpdate(new FieldDefinition("parentID", FieldDefinitionTypes.FullText));
            }
    
            public void Terminate()
            {
            }
        }
    

    Dee

  • Steve Morgan 1208 posts 3922 karma points c-trib
    Aug 28, 2020 @ 14:03
    Steve Morgan
    0

    Hi,

    Nothing to hand but I see you've asked the questions in other threads.

    I've added custom stuff in v7 - not really had to do it in v8. Use the backoffice interface though and check your queries and that the fields you've added are there. Saves lots of time.

    https://our.umbraco.com/documentation/reference/searching/examine/examine-management

    Good luck!

Please Sign in or register to post replies

Write your reply to:

Draft