Microsoft Dynamics CRM (Archived)

Lookup dialog filtered by fetchxml and wildcard search results in Timeout (CRM 2011/UR18)

(1) Share

Report

Posted on by Tanguy TOUZARD

687 Most Valuable Professional

Hi community,

I'm facing a strange problem, here.

When a lookup is filled with a value, I'm filtering another lookup with quite complex query (3 linked entities, 8 conditions).

The lookup works as expected but if I search a record with wildcard character (*) in the lookup dialog, then it ends with a SQL timeout.

The strange thing is if I execute the exact same query on SQL management studio, then it takes only 1 to 5 seconds to execute.

Is there anything I could try to avoid this timeout?

Thank you very much

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

ScottDurow 21 on at

Like (0)

Report

Hi Tanguy,

I've found timeouts like this are usually to do with the joins to the POA table - if you run the query as an Administrator then this join is not done.

Do you have a lot of sharing? When you run the query in SQL Management studio are you running the same query that you've captured from SQL Profiler or just a query that does the same sort of joins?

You might try using SQL Profiler to capture the trace to see what is going on if not.

Scott

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

Have you tried to enable tracing on the crm server and checking the actual sql query that gets executed when you search using CRM UI? May be that will provide some insight to figure out the issue. Based on the actual query that gets executed by crm you can fine tune things in the database side by adding index on the columns that affects the perfomance.

Was this reply helpful? Yes No
Tanguy TOUZARD 687 Most Valuable Professional on at

Like (0)

Report

Thanks guys for helping

The SQL query comes from CRM Server trace, so it should be the real one used and I am CRM system administrator when it times out.

No sharing here, all users have access to all data in one BU

Was this reply helpful? Yes No
ScottDurow 21 on at

Like (2)

Report

I'm sure you've checked already but are there any plugins on RetrieveMultiple registered?

Was this reply helpful? Yes No
Tanguy TOUZARD 687 Most Valuable Professional on at

Like (0)

Report

No plugins on RetrieveMultiple message...

Thank you for trying :)

Was this reply helpful? Yes No
NatrajY 3,040 on at

Like (0)

Report

When you are executing the query extracted from SQL Profiler, check if it is using the plan cache.

If you get a row with CacheHit, when you are running the query from SSMS, then the comparison is not fair, as the query is going to execute faster the second time.

If the query exists in the Plan Cache, you can try purging it before comparing the performance. Have a look at http://blog.sqlauthority.com/2010/08/28/sql-server-plan-cache-retrieve-and-remove-a-simple-script/

Was this reply helpful? Yes No