selecting from a view is taking longer than 30+ minutes

Question

I am working on making this view fast enough to fetch the result set in reasonable time which is at the moment taking more than 30+ minutes, going parallel and causing all sorts of pain with increased cpu time. I have identified the problem query but I can't figure out a way to cut the execution time by either re-writing the query or adding appropriate index if needed. We already have clustered index on client_id and non clustered index on the hash_key column in both the tables. Also these respective join tables have close to around 238 million records from work_orders and a total of 287011570 records from s_inspections table.

select
    wo.client_id, 
    wo.work_orders_hash_key,
    wo.work_order_number, 
    wo.work_order_id, 
    si.inspection_id, 
    si.inspection_name,
    si.inspection_detail, 
    si.master_inspection_id, 
    si.master_inspection_detail, 
    si.status_id, 
    si.exception, 
    si.inspection_order, 
    si.comment,
    si.[procedure_id],
    si.[flag_id],
    si.[asset_id],
    si.[asset_name],
    si.[inspection_status],
    si.[is_removed],
    si.[response],
    row_number() over(partition by si.work_orders_hash_key, si.inspection_id order by si.dss_version desc) rnk
from
    datavault.dbo.h_work_orders wo with (readuncommitted) 
    join datavault.dbo.s_inspections si with (readuncommitted) on wo.client_id = si.client_id and wo.work_orders_hash_key = si.work_orders_hash_key 
where
    wo.client_id in (7700876368663, 8800387996408)

Below is the estimated execution plan as it was taking quite sometime so I couldn't provide the actual execution plan.

https://www.brentozar.com/pastetheplan/?id=ryLzvNwUN

Any help would be greatly appreciated.

Answer 1

maybe this will work to keep you in business since the row_number() was the issue. try:

;with x as (
select
    wo.client_id, 
    wo.work_orders_hash_key,
    wo.work_order_number, 
    wo.work_order_id, 
    si.inspection_id, 
    si.inspection_name,
    si.inspection_detail, 
    si.master_inspection_id, 
    si.master_inspection_detail, 
    si.status_id, 
    si.exception, 
    si.inspection_order, 
    si.comment,
    si.[procedure_id],
    si.[flag_id],
    si.[asset_id],
    si.[asset_name],
    si.[inspection_status],
    si.[is_removed],
    si.[response],
    si.dss_version
from
    datavault.dbo.h_work_orders wo with (readuncommitted) 
    join datavault.dbo.s_inspections si with (readuncommitted) on wo.client_id = si.client_id and wo.work_orders_hash_key = si.work_orders_hash_key 
where
    wo.client_id in (7700876368663, 8800387996408)
    )
select 
    x.client_id, 
    x.work_orders_hash_key,
    x.work_order_number, 
    x.work_order_id, 
    x.inspection_id, 
    x.inspection_name,
    x.inspection_detail, 
    x.master_inspection_id, 
    x.master_inspection_detail, 
    x.status_id, 
    x.exception, 
    x.inspection_order, 
    x.comment,
    x.[procedure_id],
    x.[flag_id],
    x.[asset_id],
    x.[asset_name],
    x.[inspection_status],
    x.[is_removed],
    x.[response],
    row_number() over(partition by x.work_orders_hash_key, x.inspection_id order by x.dss_version desc) rnk
from x;

Answer 2

Your compute scalar is 59% of your query cost. I would guess it's this line: row_number() over(partition by si.work_orders_hash_key, si.inspection_id order by si.dss_version desc) rnk It's estimating 159014000000000 rows! Whack this line (lot of work to return a row number) and run it again.