locked
FilteredView Query Running Slow RRS feed

  • Question

  • I am making a report using the FilteredViews in CRM 2011. The query "works" when it isn't timing out. The problem seems to be that the query using FilteredViews runs REALLY REALLY slow. The query without the filteredviews seems to run quick, but I need to use the FIlteredViews. This is my query:

    SELECT CRMAF_Projects.new_projectsid AS ProjectId
    	 , CRMAF_Projects.new_name AS ProjectName
    	 , CRMAF_Projects.new_description AS ProjectDescription
    	 , CRMAF_Projects.new_status AS ProjectStatus
    	 , CRMAF_Projects.new_estimatedhours AS ProjectEstimatedHours
    	 , CRMAF_Projects.new_hours AS ProjectActualHours
    	 , CRMAF_Projects.new_startdate AS ProjecStartDate
    	 , CRMAF_Projects.new_enddate AS ProjecEndDate
    	 , CRMAF_ProjectSteps.new_parentstepname AS ParentStepName
    	 , CRMAF_ProjectSteps.new_projectstepsid AS ProjectStepId
    	 , CRMAF_ProjectSteps.new_name AS ProjectStepName
    	 , CRMAF_ProjectSteps.new_description AS ProjectStepDescription
    	 , CRMAF_ProjectSteps.statecode AS ProjectStepStatus
    	 , CRMAF_ProjectSteps.new_order
    	 , CRMAF_ProjectSteps.new_estimatedhours AS ProjectStepEstimatedHours
    	 , CRMAF_ProjectSteps.new_hours AS ProjectStepActualHours
    	 , CRMAF_ProjectSteps.new_startdate AS ProjectStepStartDate
    	 , CRMAF_ProjectSteps.new_enddate AS ProjectStepEndDate
    	 , CRMAF_Task.subject AS ProjectStepTaskName
    	 , CRMAF_Task.statecode AS ProjectStepTaskStatus
    	 , CRMAF_Task.description AS ProjectStepTaskDescription
    	 , CRMAF_Task.new_estimatedduration AS ProjectStepTaskEstimatedDuration
    	 , CRMAF_Task.actualdurationminutes AS ProjectStepTaskActualDuration
    	 , CRMAF_Task.actualstart AS ProjectStepTaskStartDate
    	 , CRMAF_Task.actualend AS ProjectStepTaskEndDate
    FROM
    	Filterednew_projects AS CRMAF_Projects
    	LEFT OUTER JOIN Filterednew_projectsteps AS CRMAF_ProjectSteps
    		ON CRMAF_Projects.new_projectsid = CRMAF_ProjectSteps.new_projectsteps2id
    	LEFT OUTER JOIN FilteredTask AS CRMAF_Task
    		ON CRMAF_ProjectSteps.new_projectstepsid = CRMAF_Task.regardingobjectid

    Is there anything wrong with my query? Is this a known problem? Is there a workaround? Any help would be fantastic, I've been stuck on this for weeks.

    Thanks!

    Friday, July 6, 2012 4:03 PM

Answers

  • Depending on your business scenario. You need to ask the question:

    Is there any Project that has no steps, or any steps that has no task?

    Depending your answer to the question, you can perform your query differently. If the answer is no, the you query can be much faster by changing LEFT OUTER JOIN into INNER JOIN.

    If the answer is YES, then you may split your query into:

    1. changing LEFT OUTER JOIN into INNER JOIN to find all the projects that are with steps and tasks.

    2. Use 

     

    SELECT CRMAF_Projects.new_projectsid AS ProjectId
    , CRMAF_Projects.new_name AS ProjectName
    , CRMAF_Projects.new_description AS ProjectDescription
    , CRMAF_Projects.new_status AS ProjectStatus
    , CRMAF_Projects.new_estimatedhours AS ProjectEstimatedHours
    , CRMAF_Projects.new_hours AS ProjectActualHours
    , CRMAF_Projects.new_startdate AS ProjecStartDate
    , CRMAF_Projects.new_enddate AS ProjecEndDate

    FROM
    Filterednew_projects AS CRMAF_Projects
    Where NOT EXISTS ( SELECT 1 from Filterednew_projectsteps AS CRMAF_ProjectSteps
                      where CRMAF_Projects.new_projectsid = CRMAF_ProjectSteps.new_projectsteps2id)

    to find all projects without steps 

    and

    3. using  

     

    SELECT CRMAF_Projects.new_projectsid AS ProjectId
    , CRMAF_Projects.new_name AS ProjectName
    , CRMAF_Projects.new_description AS ProjectDescription
    , CRMAF_Projects.new_status AS ProjectStatus
    , CRMAF_Projects.new_estimatedhours AS ProjectEstimatedHours
    , CRMAF_Projects.new_hours AS ProjectActualHours
    , CRMAF_Projects.new_startdate AS ProjecStartDate
    , CRMAF_Projects.new_enddate AS ProjecEndDate
    , CRMAF_ProjectSteps.new_parentstepname AS ParentStepName
    , CRMAF_ProjectSteps.new_projectstepsid AS ProjectStepId
    , CRMAF_ProjectSteps.new_name AS ProjectStepName
    , CRMAF_ProjectSteps.new_description AS ProjectStepDescription
    , CRMAF_ProjectSteps.statecode AS ProjectStepStatus
    , CRMAF_ProjectSteps.new_order
    , CRMAF_ProjectSteps.new_estimatedhours AS ProjectStepEstimatedHours
    , CRMAF_ProjectSteps.new_hours AS ProjectStepActualHours
    , CRMAF_ProjectSteps.new_startdate AS ProjectStepStartDate
    , CRMAF_ProjectSteps.new_enddate AS ProjectStepEndDate

    FROM
    Filterednew_projects AS CRMAF_Projects
    INNER JOIN Filterednew_projectsteps AS CRMAF_ProjectSteps
    ON CRMAF_Projects.new_projectsid = CRMAF_ProjectSteps.new_projectsteps2id
    WHERE NOT EXISTS ( SELECT 1 from FilteredTask AS CRMAF_Task
    WHERE CRMAF_ProjectSteps.new_projectstepsid = CRMAF_Task.regardingobjectid)

    To find all the steps without tasks.

    By splitting your queries, you can have 3 fairly fast(compare to your original) queries and  have a better chance of not hitting timeout. 

    Hope this helps.

    • Marked as answer by CrazyeD1583 Monday, July 9, 2012 2:10 PM
    Friday, July 6, 2012 7:18 PM

All replies

  • I can't see anything immediately wrong with the query. Some things you could try to tease out the issue are:

    • Ensure you have an index on the ProjectSteps.new_projectsteps2id column
    • Compare the performanace against using inner joins. Clearly this may give different results, but it would be interesting to know if the joins are the issue

    When I've had performance problems, they are often due to the query optimiser either spending a long time building a query plan, or selecting an inefficient one. Filtered views contribute to this, as they add a lot of joins into the query. How many option sets or lookups do you have on the 2 custom entities - the more you have, then more joins there are


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

    Friday, July 6, 2012 4:13 PM
    Moderator
  • can you try to delete the report from crm and create a new one with the same .rpt file. it sounds strange, but worked for us while we imported a report from crm 4.0 to crm 2011 where all other things failed.

    regards

    joon

    Friday, July 6, 2012 6:08 PM
  • Depending on your business scenario. You need to ask the question:

    Is there any Project that has no steps, or any steps that has no task?

    Depending your answer to the question, you can perform your query differently. If the answer is no, the you query can be much faster by changing LEFT OUTER JOIN into INNER JOIN.

    If the answer is YES, then you may split your query into:

    1. changing LEFT OUTER JOIN into INNER JOIN to find all the projects that are with steps and tasks.

    2. Use 

     

    SELECT CRMAF_Projects.new_projectsid AS ProjectId
    , CRMAF_Projects.new_name AS ProjectName
    , CRMAF_Projects.new_description AS ProjectDescription
    , CRMAF_Projects.new_status AS ProjectStatus
    , CRMAF_Projects.new_estimatedhours AS ProjectEstimatedHours
    , CRMAF_Projects.new_hours AS ProjectActualHours
    , CRMAF_Projects.new_startdate AS ProjecStartDate
    , CRMAF_Projects.new_enddate AS ProjecEndDate

    FROM
    Filterednew_projects AS CRMAF_Projects
    Where NOT EXISTS ( SELECT 1 from Filterednew_projectsteps AS CRMAF_ProjectSteps
                      where CRMAF_Projects.new_projectsid = CRMAF_ProjectSteps.new_projectsteps2id)

    to find all projects without steps 

    and

    3. using  

     

    SELECT CRMAF_Projects.new_projectsid AS ProjectId
    , CRMAF_Projects.new_name AS ProjectName
    , CRMAF_Projects.new_description AS ProjectDescription
    , CRMAF_Projects.new_status AS ProjectStatus
    , CRMAF_Projects.new_estimatedhours AS ProjectEstimatedHours
    , CRMAF_Projects.new_hours AS ProjectActualHours
    , CRMAF_Projects.new_startdate AS ProjecStartDate
    , CRMAF_Projects.new_enddate AS ProjecEndDate
    , CRMAF_ProjectSteps.new_parentstepname AS ParentStepName
    , CRMAF_ProjectSteps.new_projectstepsid AS ProjectStepId
    , CRMAF_ProjectSteps.new_name AS ProjectStepName
    , CRMAF_ProjectSteps.new_description AS ProjectStepDescription
    , CRMAF_ProjectSteps.statecode AS ProjectStepStatus
    , CRMAF_ProjectSteps.new_order
    , CRMAF_ProjectSteps.new_estimatedhours AS ProjectStepEstimatedHours
    , CRMAF_ProjectSteps.new_hours AS ProjectStepActualHours
    , CRMAF_ProjectSteps.new_startdate AS ProjectStepStartDate
    , CRMAF_ProjectSteps.new_enddate AS ProjectStepEndDate

    FROM
    Filterednew_projects AS CRMAF_Projects
    INNER JOIN Filterednew_projectsteps AS CRMAF_ProjectSteps
    ON CRMAF_Projects.new_projectsid = CRMAF_ProjectSteps.new_projectsteps2id
    WHERE NOT EXISTS ( SELECT 1 from FilteredTask AS CRMAF_Task
    WHERE CRMAF_ProjectSteps.new_projectstepsid = CRMAF_Task.regardingobjectid)

    To find all the steps without tasks.

    By splitting your queries, you can have 3 fairly fast(compare to your original) queries and  have a better chance of not hitting timeout. 

    Hope this helps.

    • Marked as answer by CrazyeD1583 Monday, July 9, 2012 2:10 PM
    Friday, July 6, 2012 7:18 PM
  • If the DB is transaction heavy, using the WITH (NOLOCK) table hint might help improve performance. Read and understand it well before implementing. Though using (potentially) dirty reads for reporting should not be an issue.

    http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements

    I'm not sure if the clause would play well with the view alias used for pre-filtering. Please let me know if you got it to work and if it helped at all.

    Regards,

    Mayank

    Saturday, July 7, 2012 9:21 AM