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.
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.
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()
{
}
}
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.
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:
This is in fact quering all members first, and then doing the filtering based on where clauses, so not really useful.
Option B:
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:
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.
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
Hi,
Could you use the Examine Member index?
Steve
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:
Dee
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!
is working on a reply...