When i execute below query on production server it take max 2 seconds but if i use this query in procedure i am not getting any response . i don't understand this behaviour so kindly help me.
/*== Not getting response of query without store procedure ==*/
alter PROCEDURE [dbo].[USP_MonthlySales_PrimarySecondaryTarget_HQwiseProd_HP_PM] @dtMonth Date
AS
BEGIN
DECLARE @dtCurDate DATETIME
SET @dtCurDate = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT COUNT(1)
FROM (SELECT CG.fk_GMGlCode AS fk_HQGlCode,
PD2.fk_ProductGlCode AS fk_ProdGlCode,
PD.dtDate AS dtMonth,
PD2.fk_DivisionGlCode,
SUM(PD.decQuantity) AS decSecondaryQty,
SUM(PD.decAmt) AS decSecondaryValue,
SUM(PD.decClosing) AS decSecondaryClosingQty,
SUM(ISNULL(PD.decClosing, 0) * ISNULL(PD.decPTS, 0)) AS decSecondaryClosingValue
FROM dbo.Secondary_Data AS PD WITH (NOLOCK)
INNER JOIN dbo.Channel_Geo AS CG WITH (NOLOCK) ON CG.fk_ChannelGlCode = PD.fk_ChannelGlCode_Stk
AND (CG.dtFromDate <= @dtCurDate)
AND (CG.dtToDate IS NULL OR CG.dtToDate >= @dtCurDate)
INNER JOIN dbo.Geo_Mst AS GM ON GM.intGLCode = CG.fk_GMGlCode
INNER JOIN dbo.Product_Details AS PD2 ON PD2.fk_ProductGlCode = PD.fk_ProductGlCode
AND (PD2.dtFromDate <= @dtCurDate)
AND (PD2.dtToDate IS NULL OR PD2.dtToDate >= @dtCurDate)
AND GM.fk_DivisionGlCode = PD2.fk_DivisionGlCode
WHERE PD.dtDate = @dtMonth
GROUP BY CG.fk_GMGlCode,
PD2.fk_ProductGlCode,
PD.dtDate,
PD2.fk_DivisionGlCode) AS TT
LEFT JOIN dbo.Monthly_Sales_Data AS MSD ON MSD.fk_GMGlCode = TT.fk_HQGlCode
AND MSD.fk_ProductGlCode = TT.fk_ProdGlCode
AND MSD.dtDate = TT.dtMonth
AND MSD.varDataType = 'HP'
WHERE MSD.intGLCode IS NULL
END
/*== getting response of query without store procedure ==*/
DECLARE @dtCurDate DATETIME
SET @dtCurDate = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
DECLARE @dtMonth DATE = '2022-10-01'
SELECT COUNT(1)
FROM (SELECT CG.fk_GMGlCode AS fk_HQGlCode,
PD2.fk_ProductGlCode AS fk_ProdGlCode,
PD.dtDate AS dtMonth,
PD2.fk_DivisionGlCode,
SUM(PD.decQuantity) AS decSecondaryQty,
SUM(PD.decAmt) AS decSecondaryValue,
SUM(PD.decClosing) AS decSecondaryClosingQty,
SUM(ISNULL(PD.decClosing, 0) * ISNULL(PD.decPTS, 0)) AS decSecondaryClosingValue
FROM dbo.Secondary_Data AS PD WITH (NOLOCK)
INNER JOIN dbo.Channel_Geo AS CG WITH (NOLOCK) ON CG.fk_ChannelGlCode = PD.fk_ChannelGlCode_Stk
AND (CG.dtFromDate <= @dtCurDate)
AND (CG.dtToDate IS NULL OR CG.dtToDate >= @dtCurDate)
INNER JOIN dbo.Geo_Mst AS GM ON GM.intGLCode = CG.fk_GMGlCode
INNER JOIN dbo.Product_Details AS PD2 ON PD2.fk_ProductGlCode = PD.fk_ProductGlCode
AND (PD2.dtFromDate <= @dtCurDate)
AND (PD2.dtToDate IS NULL OR PD2.dtToDate >= @dtCurDate)
AND GM.fk_DivisionGlCode = PD2.fk_DivisionGlCode
WHERE PD.dtDate = @dtMonth
GROUP BY CG.fk_GMGlCode,
PD2.fk_ProductGlCode,
PD.dtDate,
PD2.fk_DivisionGlCode) AS TT
LEFT JOIN dbo.Monthly_Sales_Data AS MSD ON MSD.fk_GMGlCode = TT.fk_HQGlCode
AND MSD.fk_ProductGlCode = TT.fk_ProdGlCode
AND MSD.dtDate = TT.dtMonth
AND MSD.varDataType = 'HP'
WHERE MSD.intGLCode IS NULL
