Issue adding meta fields to meta classes grammatically

Vote:
 

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
Vote:
 

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

#200450
Jan 12, 2019 16:48
Vote:
 

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
Vote:
 

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
Vote:
 
#200494
Jan 14, 2019 13:26
Vote:
 

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
Vote:
 

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

#200515
Jan 14, 2019 17:58
Vote:
 

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
Vote:
 

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

#200531
Jan 15, 2019 7:23
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.