x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 2 replies
  • Subscribers 74 subscribers
  • Views 172 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
nal von minden GmbH

vPersonPhoneSummary optimisation

Posted By

Hi, we had a problem with the PersonPhoneSummary. When a customer call us, CRM display this Information after 1,40 minutes! We have 1,6 Million Entries in the Phone table.

We had a look in that view. We changed the view, now it takes 2 seconds, to find a record!

our SQL-Statement

[code]

SELECT CASE WHEN PLink_EntityID = 13 THEN RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) ELSE NULL END AS Pers_FullName,

RTRIM(REPLACE(REPLACE(RTRIM(ISNULL(Phone.Phon_CountryCode, '')) + ' ' + RTRIM(ISNULL(Phone.Phon_AreaCode, '')) + ' ' + RTRIM(ISNULL(Phone.Phon_Number, '')), '-', ''), ' ', '')) AS Phon_FullNumber,

Pers_PersonId, Pers_PrimaryUserId, Pers_SecTerr, Pers_CreatedBy, Pers_ChannelId, Pers_Deleted, Pers_CompanyId, Pers_AccountId,

COALESCE(c1.Comp_CompanyId, c2.Comp_CompanyId, c3.Comp_CompanyId) AS Comp_CompanyId,

COALESCE(c1.Comp_Name, c2.Comp_Name, c3.Comp_Name) AS Comp_Name,

COALESCE(c1.Comp_PrimaryUserId, c2.Comp_PrimaryUserId, c3.Comp_PrimaryUserId) AS Comp_PrimaryUserId,

COALESCE(c1.Comp_SecTerr, c2.Comp_SecTerr, c3.Comp_SecTerr) AS Comp_SecTerr,

COALESCE(c1.Comp_CreatedBy, c2.Comp_CreatedBy, c3.Comp_CreatedBy) AS Comp_CreatedBy,

COALESCE(c1.Comp_ChannelId, c2.Comp_ChannelId, c3.Comp_ChannelId) AS Comp_ChannelId,

COALESCE(a1.Acc_AccountId, a2.Acc_AccountId) AS Acc_AccountId,

COALESCE(a1.Acc_Name, a2.Acc_Name) AS Acc_Name,

COALESCE(a1.Acc_PrimaryUserId, a2.Acc_PrimaryUserId) AS Acc_PrimaryUserId,

COALESCE(a1.Acc_SecTerr, a2.Acc_SecTerr) AS Acc_SecTerr,

COALESCE(a1.Acc_CreatedBy, a2.Acc_CreatedBy) AS Acc_CreatedBy,

COALESCE(a1.Acc_ChannelId, a2.Acc_ChannelId) AS Acc_ChannelId

FROM PhoneLink

INNER JOIN Phone ON PhoneLink.PLink_PhoneId = Phone.Phon_PhoneId

LEFT JOIN Person p1 ON p1.Pers_PersonId = PLink_RecordId AND p1.Pers_Deleted IS NULL AND PLink_EntityID = 13

LEFT JOIN Account a1 ON a1.Acc_AccountId = p1.Pers_AccountId

LEFT JOIN Company c1 ON c1.Comp_CompanyId = p1.Pers_CompanyId

LEFT JOIN Account a2 ON

  • 0

    Run the following:

    SELECT *

    FROM Custom_views

    WHERE cuvi_viewname = 'vPersonPhoneSummary'

    And take note of the cuvi_entity

    It is in the phone entity.

    If you look at the custom_tables table:

    SELECT bord_hidden,*

    FROM Custom_tables

    WHERE bord_caption = 'Phone'

    You will see that the phone table is hidden, so you cannot see it.

    Back up before you do this, and it is up to you if you do, but you can update the view directly in the custom_views table.

    Update the cuvi_viewscript so it matches your script, ensuring you make sure it includes the:

    CREATE VIEW vPersonPhoneSUmmary AS

  • 0

    Thanks for the info. We did it in this way. Thanks.

*Community Hub is the new name for Sage City