• It appears you are running the same query in 2 different where clauses. If I were to tackle this I'd consider restructuring the code so this result set:

    select pcm_child.contract_id

    from table_a pcm inner join

    table_b pra on pcm.pcm_role_id = pra.pra_role_id INNER JOIN

    table_c y ON pra.p_phn_id = y.p_phn_id inner join

    table_d pris on pcm.pcm_role_id = pris.parent_role_instance_id inner join

    table_a pcm_child on pris.role_instance_id = pcm_child.pcm_role_id

    where (y.lst_updt_dtm >= @lst_updt_tm

    OR pra.lst_updt_dtm >= @lst_updt_tm))

    so it is in either a CTE or an indexed temporary table. The other think to look at is the Execution Plan. Make sure your tables being joined are indexed and have updated statistics.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman