ลงชื่อเข้าใช้
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Problem with the performance in teh lookup view of a customentity in CRM 4

Proposed Answer Problem with the performance in teh lookup view of a customentity in CRM 4

  • 12 พฤษภาคม 2555 18:25
     
     
    ลงชื่อเข้าใช้เพื่อโหวต
    0

    Hi,

    We have a custom entity named Address Detail. Address Detail entity has about 4 million records and other than some varchar column it has 2 lookups to the account entity(which has 1.5 million record), one look up to the contact entity and one lookup to anther entity named Operating name( which has 800,000 records).

    On the other entities I have a lookup view to the Address Detail which contains of the following columns( street, postal code, contact,account, Operating name).

    Find columns on this lookup view contains all of the above fields which I know it is not a good idea to have all of them in the find column but business would like to be able to seach on all of them.

    When ever I have a lookup in another entity to the address detail it it crazy slow and it takes about 35 Sec to open the lookup and 1 minutes and 15 seconds to search on it.

    I checked with DBA they mentioned the indexes are well defined on the Address Detail entity and the SQL profiler does not give any recommanadation for adding new index!

    SQL Profiler shows when ever we try to open and search in the lookup, the quey which is running behind the seen on sql server consist of Address Detail filter view with left outer join 2 times wih account, 1 time contact and 1 time operating name. Considering the number of our records we have I beleive this is the cause of issue but I have no idea how I can improve the performance?!

    We are using CRM4.

    Any advice would be very much apprecaited!

    Thanks,

    M

     

ตอบทั้งหมด

  • 12 พฤษภาคม 2555 18:45
     
     คำตอบที่เสนอ
    ลงชื่อเข้าใช้เพื่อโหวต
    0

    Maryam,

    You could have a dummy varchar fields for each lookups that get populated with the name portion of the lookup record. You can have a plugin that runs on Create and Update of Address Detail record to ensure that the value in these dummy varchar fields are kept up to date.

    And you should include these varchar fields in your View and Find column instead of the lookup ones. By doing this, there shouldn't be the need for CRM to do joins and your lookup query should run a lot faster.

    Hope this helps.

    Dimaz Pramudya - CRM Developer - CSG (Melbourne) www.xrmbits.com http://twitter.com/xrmbits

    • เสนอเป็นคำตอบโดย Devashish Bajpai 12 พฤษภาคม 2555 18:52
    •  
     
  • 12 พฤษภาคม 2555 21:23
     
     
    ลงชื่อเข้าใช้เพื่อโหวต
    0

    Thank you so much for the fast respond...

    This sounds like a good idea but what if the value of the primary field of parent record(account/contact/operation name)  gets updated?Then all the related varchar fields to the lookup on the Address details Become out of date.Then this solution would not work 100%

    Any idea?

     
  • 12 พฤษภาคม 2555 21:44
     
     คำตอบที่เสนอ
    ลงชื่อเข้าใช้เพื่อโหวต
    0

    Hi Maryam,

    You could write 3 additional plugins on Account, Contact and Operation Name Update to automatically update the dummy varchar fields on all related Address Details.

    To validate that this extra work is worth the effort, can you try removing the lookup fields from the View and the Quick find temporarily and test the performance? If the performance is significantly improved, you can go ahead and implement these plugins.

    Hope this helps.

    Dimaz Pramudya - CRM Developer - CSG (Melbourne) www.xrmbits.com http://twitter.com/xrmbits

     
  • 12 พฤษภาคม 2555 22:09
     
     
    ลงชื่อเข้าใช้เพื่อโหวต
    0

    Thanks Dimaz! I will try and see what will happen and post the result....