I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query.
It is taking 4 sec to 4 min in execution. Please suggest the possibilities.
There is no scope of query optimization. Indexes are also proper.
SELECT ALL X.*
,sd1.string_value "sd1_string_value"
,sd2.string_value "sd2_string_value"
,sd3.string_value "sd3_string_value"
,sd4.string_value "sd4_string_value"
,sd5.string_value "sd5_string_value"
,sd6.string_value "sd6_string_value"
,sd7.string_value "sd7_string_value"
,wi.r_object_id "wi_r_object_id"
,wi.r_act_def_id "wi_r_act_def_id"
,wi.r_workflow_id "wi_r_workflow_id"
FROM (
SELECT *
FROM (
SELECT ALL q.content_type q_content_type
,q.item_type q_item_type
,q.dependency_type q_dependency_type
,q.router_id q_router_id
,q.event q_event
,q.item_name q_item_name
,q.task_subject q_task_subject
,q.task_name q_task_name
,q.NAME q_name
,q.sent_by q_sent_by
,q.r_object_id q_r_object_id
,q.priority q_priority
,q.item_id q_item_id
,q.date_sent q_date_sent
,q.task_state q_task_state
,q.due_date q_due_date
,q.task_number q_task_number
,q.source_docbase q_source_docbase
,q.source_event q_source_event
,wf.process_id wf_process_id
,wf.r_object_id wf_r_object_id
,CAST(ROW_NUMBER() OVER (
ORDER BY q.date_sent DESC
,q.r_object_id ASC
) AS INT) AS dm_rnum
FROM dm_workflow_sp wf
,dmi_queue_item_sp q
WHERE (
((q.router_id = wf.r_object_id))
AND (
q.NAME IN (
N'Hemavathy Aneesh. Nair'
,N'al_nko1000abs_group'
,N'al_nmp1000abs_group'
,N'al_nmu1000abs_group'
,N'al_nub1000abs_group'
,N'al_nue1000abs_group'
,N'edms_user'
)
AND (wf.process_id = N'4b027d9a800077e8')
AND (
(q.delete_flag = 0)
AND q.dependency_type IN (
N'0'
,N'1'
,N'2'
,N'3'
,N'4'
,N'5'
,N'6'
)
)
)
)
) dm_inline_view_1
WHERE dm_rnum >= 1
AND dm_rnum <= 101
) X
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'dm_taskName'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd1 ON (sd1.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'circel'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd2 ON (sd2.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'VendorCode'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd3 ON (sd3.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'VendorName'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd4 ON (sd4.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'InvoiceNumber'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd5 ON (sd5.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'InvoiceAmountCommaFormat'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd6 ON (sd6.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'Parking_Doc_Id'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd7 ON (sd7.workflow_id = X.q_router_id)
LEFT JOIN dmi_workitem_sp wi ON (wi.r_queue_item_id = X.q_r_object_id)
ORDER BY X.q_date_sent DESC
,X.q_r_object_id ASC
