locked
CRM Lookup Query - Performance RRS feed

  • Question

  • Hi,

    We have 2 lookups that retrieve data from a custom entity on the serviceappointment form. Users heavily use them and search for the items using the * wildcard character. The problem is that it takes about 15 to 30 seconds to show the results. It is worth pointing out that users are very specific in the search and results usually return approx 10 records from a total of 18000 records.

    Upon further investigating we discovered the query that is being generated by CRM is something like the below. Is there any way this CRM-built query can be optimized/changed?

    WITH __QuickFind__ as (

    select top 10001 [new_entityId]

    from (

    SELECT "new_entity0".[new_entityId] as [new_entityId]

    from [new_entityExtensionBase] as "new_entity0"

    where ("new_entity0".new_name like '%tex%')

    ) as [__QuickFindInternal__]

    )

    select top 251 "new_entity0".new_name as "new_name",

    "new_entity0".new_entityId as "new_entityid",

    case when (select COUNT(*) from [__QuickFind__]) = 10001 then 1 else 0 end as [__QuickFindLimitValue__]

    from

    inew_entity as "new_entity0" (NOLOCK)

    where

    [new_entity0].[new_entityId] in

    (

    select [new_entityId]

    from [__QuickFind__]

    )

    and

    (

    ("new_entity0".new_LocId = '64df8b95-8a18-e411-beb6-0050568c445e'

    and (("new_entity0".statecode = 0))

    )

    )

    order by "new_entity0".new_name asc,

    "new_entity0".new_entityId asc

    Thanks


    Darren Mercieca

    Wednesday, May 31, 2017 8:18 AM

All replies

  • Hi,

    We have 2 lookups that retrieve data from a custom entity on the serviceappointment form. Users heavily use them and search for the items using the * wildcard character. The problem is that it takes about 15 to 30 seconds to show the results. It is worth pointing out that users are very specific in the search and results usually return approx 10 records from a total of 18000 records.


    Upon further investigating we discovered the query that is being generated by CRM is something like the below. Is there any way this CRM-built query can be optimized/changed?

    WITH __QuickFind__ as (

    select top 10001 [new_entityId]

    from (

    SELECT "new_entity0".[new_entityId] as [new_entityId]

    from [new_entityExtensionBase] as "new_entity0"

    where ("new_entity0".new_name like '%tex%')

    ) as [__QuickFindInternal__]

    )

    select top 251 "new_entity0".new_name as "new_name",

    "new_entity0".new_entityId as "new_entityid",

    case when (select COUNT(*) from [__QuickFind__]) = 10001 then 1 else 0 end as [__QuickFindLimitValue__]

    from

    inew_entity as "new_entity0" (NOLOCK)

    where

    [new_entity0].[new_entityId] in

    (

    select [new_entityId]

    from [__QuickFind__]

    )

    and

    (

    ("new_entity0".new_LocId = '64df8b95-8a18-e411-beb6-0050568c445e'

    and (("new_entity0".statecode = 0))

    )

    )

    order by "new_entity0".new_name asc,

    "new_entity0".new_entityId asc

    Thanks


    Darren Mercieca

    Wednesday, May 31, 2017 7:56 AM
  • You can't change the query that is generated by CRM. Your main option (assuming you have CRM OnPremise) is to add SQL indexes to improve the query. As users do a wildcard search on a text field, there aren't many useful indexing option (in this case the only useful one is probably on new_name including the new_entityid column), but it's worth using SQL Management Studio to see if it recommends any indexes for this.

    If that doesn't help, can you add more useful search fields to your custom entity ? If users are doing a wildcard text search, if the test they search for has some meaning, then maybe you could extract that into a separate field


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

    Wednesday, May 31, 2017 9:10 AM
    Moderator