November 1, 2016 at 2:38 pm
Hi Below query is taking 47 sec. I want to optimize below query.
I checked without INNER JOIN dbo.T_FieldValue taking 2 sec. How can I re-write below join to improve perfomance.
GO
SET QUOTED_IDENTIFIER ON
GO
SELECT i.Item_ID AS Kit_SID
, i.Item_ID AS Kit_ID
, i.Item_NO AS Kit_NO
--, bsmn.BSM_No AS BSM_No -- RS
, i.ItemType_ID AS Kit_Type_ID
, i.Cluster_ID AS Kit_Label_ID
, i.Lot_NO
--, i.Status_IND AS Current_Status_IND
, CASE WHEN ohl.Lot_No is not null or ohld.Lot_No is Not null THEN
(Select Field_CD From T_FieldValue
where FieldType_ID = 11808 and FieldValue ='On-Hold'
)
Else ISNULL(i.Status_IND,'0')
END As Status_IND
, fv.FieldValue AS Current_Status_Desc
--, DTSUPPORT.dbo.F_DT_GetLocalDSTDateTime(S.Location_ID, i.Assigned_DT)as Assigned_DT
, i.Expiration_DT
, ExtraLot1_NO
, ExtraLot2_NO
--, isnull(CD.DoseValue,0) as Dose_Value
--, isnull(CD.Dose_TXT,'N/A') as Dose_Units
, i.Modif_ID AS Kit_Modif_ID
, fv.Modif_ID AS Field_Modif_ID
, GETDATE() AS Updated_DT
FROM dbo.T_Item AS i
--Lot on hold
LEFT JOIN (Select Lot_No, Site_ID From T_OnHoldLot ohl Where Site_ID is not null)AS ohl on ohl.Lot_No =i.Lot_NO and ohl.Site_ID =i.Site_ID
LEFT JOIN (Select Lot_No, Depot_ID From T_OnHoldLot ohl Where Depot_ID is not null)AS ohld on ohld.Lot_No =i.Lot_NO and ohld.Depot_ID =i.Depot_ID
INNER JOIN dbo.T_FieldValue AS fv ON fv.Field_CD = (CASE WHEN ohl.Lot_No is not null or ohld.Lot_No is Not null THEN
(Select Field_CD From T_FieldValue
where FieldType_ID = 10000 and FieldValue ='On-Hold'
)
Else ISNULL(i.Status_IND,'0')
END
)
GO
November 1, 2016 at 3:03 pm
Since you haven't provided table definitions, index definitions, and the actual execution plan (as a .sqlplan file), there's not much advice that we can give you.
I did notice that you're overcomplicating your joins, but I don't think it will have any affect on the performance. For instance,
-- the following snippet
FROM
dbo.T_Item AS i --Lot on hold
LEFT JOIN (
SELECT
Lot_No
, Site_ID
FROM
T_OnHoldLot ohl
WHERE
Site_ID IS NOT NULL
) AS ohl
ON ohl.Lot_No = i.Lot_NO
AND ohl.Site_ID = i.Site_ID
--can be simplified to
FROM
dbo.T_Item AS i --Lot on hold
LEFT JOIN T_OnHoldLot ohl
ON ohl.Lot_No = i.Lot_NO
AND ohl.Site_ID = i.Site_ID
The join clause ensures that the Site_ID is not null, so you gain nothing by creating a subquery just to filter out records with a NULL Site_ID.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 1, 2016 at 3:18 pm
Sorry, don't have a lot of time, but here's my best guess at what could help:
SELECT i.Item_ID AS Kit_SID
, i.Item_ID AS Kit_ID
, i.Item_NO AS Kit_NO
--, bsmn.BSM_No AS BSM_No -- RS
, i.ItemType_ID AS Kit_Type_ID
, i.Cluster_ID AS Kit_Label_ID
, i.Lot_NO
--, i.Status_IND AS Current_Status_IND
, COALESCE(fv_onhold.FieldCD, i.Status_IND, '0') AS Status_IND
, ISNULL(fv_onhold.FieldValue, fv_not_onhold.FieldValue) AS Current_Status_Desc
--, DTSUPPORT.dbo.F_DT_GetLocalDSTDateTime(S.Location_ID, i.Assigned_DT)as Assigned_DT
, i.Expiration_DT
, ExtraLot1_NO
, ExtraLot2_NO
--, isnull(CD.DoseValue,0) as Dose_Value
--, isnull(CD.Dose_TXT,'N/A') as Dose_Units
, i.Modif_ID AS Kit_Modif_ID
, ISNULL(fv_onhold.Modif_ID, fv_not_onhold.Modif_ID) AS Field_Modif_ID
, GETDATE() AS Updated_DT
FROM dbo.T_Item AS i
--Lot on hold
LEFT JOIN (Select Lot_No, Site_ID From T_OnHoldLot ohl Where Site_ID is not null)AS ohl on ohl.Lot_No =i.Lot_NO and ohl.Site_ID =i.Site_ID
LEFT JOIN (Select Lot_No, Depot_ID From T_OnHoldLot ohl Where Depot_ID is not null)AS ohld on ohld.Lot_No =i.Lot_NO and ohld.Depot_ID =i.Depot_ID
LEFT OUTER JOIN dbo.T_FieldValue AS fv_onhold ON (ohl.Lot_No is not null or ohld.Lot_No is Not null) and FieldType_ID = 10000 and FieldValue ='On-Hold'
LEFT OUTER JOIN dbo.T_FieldValue AS fv_null ON fv_onhold.Field_CD is null and (fv_not_onhold.Field_CD = i.Status_IND or (i.Status_IND is null and fv_not_onhold.Field_CD = '0'))
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply