-- You could try breaking up the query. Here's one way, there are a few possibilities:

SELECT

[wck.wck_id] = wck.wck_id,

[pd.mode_id] = pd.mode_id,

[p.person_id] = p.person_id,

[wck.worker_id] = wck.worker_id,

[wck.worker_order_id] = wck.worker_order_id,

[p.access_site] = p.access_site,

[p.access_bu] = p.access_bu

INTO #person_wck_item_dashboard

FROM dbo.person p

INNER JOIN dbo.wck_item wck

ON wck.buyer_id = p.company_id

AND wck.worker_id <> ''

INNER JOIN offline_DB.dbo.dashboard pd

ON pd.[object_id] = wck.wck_id

AND pd.mode_id IN (870, 1350)

AND pd.activity_id = 10179

WHERE p.role_id <> '5'

AND NOT (p.access_site = 'ALL' AND p.access_bu = 'ALL' AND p.access_cc = 'ALL')

AND p.flag_access = 1

-- You may need an index or two on the temp table #person_wck_item_dashboard

-- try a clustered index on [p.person_id]

SELECT

[object_id] = [wck.wck_id],

mode_id = [pd.mode_id],

person_id = [p.person_id]

FROM #person_wck_item_dashboard pwd

LEFT JOIN dbo.worker w

on w.worker_id = [wck.worker_id]

LEFT JOIN dbo.worker_order wo

on wo.worker_order_id = [wck.worker_order_id]

LEFT JOIN dbo.job_enquiry jp

on jp.job_enquiry_id = wo.job_enquiry_id

WHERE

NOT EXISTS (

SELECT1

FROMoffline_DB.dbo.dashboard_visible pdvs

WHEREpdvs.object_id = [wck.wck_id] -- outer reference wck

AND pdvs.mode_id = [pd.mode_id] -- outer reference pd

AND pdvs.person_id = [p.person_id]) -- outer reference p

--AND (1 = 1

AND ((

([p.access_site] = 'ALL' OR EXISTS (

SELECT 'x'

FROM dbo.person_site ps

WHERE ps.site_id = wo.site_id -- outer reference wo

AND ps.person_id = [p.person_id])) -- outer reference p

AND ([p.access_bu] = 'ALL' OR EXISTS (

SELECT 'x'

FROM dbo.person_bu pb

WHERE pb.bu_id = wo.bu_id -- outer reference wo

AND pb.person_id = [p.person_id])) -- outer reference p

AND EXISTS (

SELECT 'x'

FROM dbo.worker_cc wcc

INNER JOIN dbo.person_cc pcc

ON wcc.cost_center_id = pcc.cost_center_id

WHERE wcc.worker_id = w.worker_id -- outer reference w

AND pcc.person_id = [p.person_id]) -- outer reference p

))

--)