Issue adding meta fields to meta classes grammatically

Todd Jasper
Member since: 2013
 

We’ve created a cms scheduled job that processes a queue of operations on meta classes and meta fields.  In this job we Create and Delete meta classes and meta fields.  We also relate meta fields to meta classes.  During the job execution we have an issue where the sql server cpu is getting slammed due to thousands of calls to the following stored procedure per second:  mdpsp_sys_LoadMetaFieldListByMetaClassId.  The stored proc alone is pretty simple and fast, but what is making this SP execute so often?

We're using the MetaClass and MetaField classes in the Mediachase.MetaDataPlus.Configurator namespace.

Through some debugging we've narrowed it to the following method in the MetaClass class:

private void AddFields(IEnumerable<MetaField> metaFields, int weight)

In this method the following line is responsible for the call to the stored proc mentioned above, because the MetaFields property calls LoadMetaFields().

this.MetaFields.Add(metaField);

Here is our code that is called maybe 300 times during the life of the job

public string CreateMetaFieldRelation(MetaClass metaClass, MetaField metaField)
        {
            if (metaClass == null)
                return "ERROR: Meta class does not exist";

            metaClass.AddField(metaField);
            return _successMessage;
        }
#200446 Jan 11, 2019 22:42
  • Quan Mai
    Member since: 2011
     

    Would it be possible to post the code of your scheduled job here ?

    #200450 Jan 12, 2019 16:48
  • Quan Mai
    Member since: 2011
     

    Even better, try to use some profiler (like dotTrace) to look into the execution of your job, that would reveal a lot of thing you might have overlooked 

    #200452 Jan 12, 2019 17:08
  • Scott Reed
    Member since: 2010
     

    I'd also recommend Stackify Prefix, it's free and will show you all the SQL executions being made and errors even if they aren't logged on local development.

    #200492 Jan 14, 2019 12:52
  • Quan Mai
    Member since: 2011
     
    #200494 Jan 14, 2019 13:26
  •  

    Quan:

    Our scheduled job code is calling the following 3 scenarios using the methods below from EPi.  It's rather simple what we are doing from our end with very few new classes/fields involved.  Yet when tracing via SQL Profiler, the procedure mdpsp_sys_LoadMetaFieldListByMetaClassId is being called 30,000+ times.  Any idea?

    Create MetaClass -- only 3 new classes created

    MetaClass.Load(MetaDataContext.Instance, name);

    MetaClass.Create(MetaDataContext.Instance, metaClassNamespace, name, friendlyName, tableName, parentMetaClass, MetaClassType.User, description);

    Create MetaField -- only 2 new fields created

    MetaField.Load(MetaDataContext.Instance, name);

    MetaField.Create(MetaDataContext.Instance, metaFieldNamespace, name, friendlyName, description, dataType, 255, true, false, false, false);

    Create Relation -- associating 40 fields per new class

    MetaClass.Load(MetaDataContext.Instance, name);

    MetaField.Load(MetaDataContext.Instance, name);

    metaClass.AddField(metaField);

    #200513 Jan 14, 2019 17:27
  • Quan Mai
    Member since: 2011
     

    Care to share your profiling results (probably with code/pdb) to me? Preferably directly via mail, 

    #200515 Jan 14, 2019 17:58
  •  

    Quan:

    I've got a hunch that this might have something to do with event replication...

    We ran a test in our QA environment which is load balanced between two servers: QA1 and QA2

    The job to create the class and add field relations was exectued on QA1 and in SQL profiler we saw QA1 call mdpsp_sys_LoadMetaFieldListByMetaClassId once. On QA2 that same SP was called 11781 times in the same timespan.

    We have two applications, our CMS site and our Commerce site. Both are setup for event replication on the same soap udp address/port.

    Can you think of anything with event replication that might be causing this activity that we're seeing? Should we be running the same endpoints between our CMS and Commerce sites? Or do you think this event replication thing is unrelated to the issue we're seeing?

    Thanks,

    Dave

    #200526 Jan 14, 2019 23:46
  • Quan Mai
    Member since: 2011
     

    Shouldn't that be clear on dotTrace profile result? So you don't even have to guess 

    #200531 Jan 15, 2019 7:23
  •  

    Quan:

    Just to follow up, we sent the trace file to you via email and we saw your response.  To add to this, we pulled one server out of EPi's event replication and ran the job without any issues.  The stored procedure, mdpsp_sys_LoadMetaFieldListByMetaClassId, was called one time each while profiling (just as it should).  So this really points to the event replication as being the issue.  Let us know what you find!

    #200563 Jan 15, 2019 21:41
  • Quan Mai
    Member since: 2011
     

    I decided to file a bug and "fix" it - the fix will be in the later version of Commerce 

    #200613 Jan 17, 2019 13:52
  •  

    Thanks Quan!  What was the issue and what is the fix?  Can you give us a ticket number so we can keep an eye out for this update?

    #200616 Jan 17, 2019 14:00
  • Quan Mai
    Member since: 2011
     

    Your "hunch" is correct, and yes this has something to do with event. When looking into it I realize we can reduce the cost of reacting to events, so we did. The fix is under review now. 

    The bug is COM-8757 but it will be some time before it's made public.

    #200619 Jan 17, 2019 14:07
  •  

    Quan, do you have any update for us on when this fix will be available?  Also, can you tell me if this issue appears with both TCP/UDP event replication?  How about in a meshed vs non-meshed replication environment?  We are just trying to get some additional details so we can make any adjustments in our environment as well.  Thanks in advance!

    #200865 Jan 29, 2019 16:11
  • Quan Mai
    Member since: 2011
     

    The bug is under review. The best we can do is 12.17, but that is still under discussion (we might skip that and go straight to 13.0)

    #200866 Jan 29, 2019 16:24