locked
Suggestion on how to copy an Entity RRS feed

  • Question

  • I have an entity called 'Application.'  Originally it contained about 100 attributes/fields.

    My company hired a vendor to come in and create some enhancements to our existing system.  In doing so, approximately 200+ attributes were added to the 'Application' entity.  The addition of all of these fields caused our system performance to plummet!  I have written several SQL queries that are based on our 'Application' data.  Most of these SQL queries are very simple - 10 fields or less are pulled from the 'Application' entity in order to create a report.  Some of these queries are run quite frequently by our users.  It has been found that these queries are running very slowly - sometimes it takes over a minute to retrieve 6 fields from the 'Application' entity!  However, what needs to be remembered is that each record in that entity now contains 300+ attributes.

    What I am thinking as a possible solution to our performance problem is to "split" the existing 'Application' entity into several other entities, each identical to the other except for the name, GUID, and whatever else needs to be different in order to uniquely identify each entity.  I would then eliminate from each entity all attributes except those that pertain to a specific "area," thereby reducing the number of attributes on each “new” entity.

    Before the addition of the 200+ attributes, our CRM system ran rather efficiently.  Now, not so much.  And, the poor system performance is negatively affecting our company's work performance.  We are a service organization, processing hundreds of clients each day, and we need to be able to process applications as quickly and correctly as possible.

    Any suggestions/advice would be GREATLY appreciated!

    Thanks,

    Michelle

    Thursday, January 28, 2016 8:02 PM

All replies

  • 1. Do indexing for this table.

    2. create more index for the tables.

    For creating new entity to stored rest data need to create lookup on main application entity.


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Friday, January 29, 2016 11:46 AM
  • Assuming you have CRM OnPremise, I think you are best looking at improving the query performance before you change the entity structure. In general, the number of fields in an entity, or in a query, do not make an appreciable difference to how long the queries take - what is more important is the filtering criteria, and whether there are suitable indexes.

    I'm not convinced from your description that the increase in the number of attributes on the entity is the main cause of your performance issues - do you know if anything else was changed ?

    I suggest you use the SQL tools to analyse your queries, and make suggestions as to which indexes may help performance. However, bear in mind that adding indexes can have adverse effects on data update performance


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Friday, January 29, 2016 2:04 PM
    Moderator
  • David

    Yes, I have a CRM 2015 On-Premise system.  The server which houses the DB has a Windows Server 2012 Op Sys and uses SQL Server 2012.<o:p></o:p>

    I am sure that the queries that I had originally written to pull fields for reporting purposes are not the best-structured queries, so I will agree with you that all of them need to be re-examined to find ways to increase performance.  However, pre-Enhancement, these queries ran fairly quickly (10-20 secs) and now, post-Enhancement, those same queries may take a minute or more to run, fairly often bringing the entire system to a stand-still till the cache can be refreshed. 

    I have no problem looking at those SQL queries.  However, I honestly do not think that they are causing the bulk of my problems.  I really believe that there are just too many attributes contained in each record of my Application entity.  I have no other way to explain the sudden and drastic negative change in performance that occurred within hours of adding our new Application entity, with all of the additional attributes, to the system.

    Due to the poor system performance directly linked to the addition of so many attributes, I had to create multiple forms - each containing a smaller number of attributes - to replace the single form that was used that contained many attributes - this modification was due to the fact that the single form took an unacceptably long time (over a minute) to load. 

    Because of my experience with the single form, I am led to believe that having all of those attributes on one entity is not a good thing.

    Yes, I will continue to analyze my system, however time always being short (as we are a service organization), I will most likely lean towards determining how to "divide up" my existing Application entity into similar entities that will each contain a smaller number of attributes.

    Thanks for your suggestions!

    Friday, January 29, 2016 5:40 PM