differance timing between direct int value and passing by parameter in fuction

Hiteshkumar Patel 21 Reputation points
2021-08-25T05:57:00.713+00:00

Hello Everyone,
today i found sum timing difference when i run my function with reference it take different time and when i pass direct value its take different time
with reference for 2784 record its take 1sec

same function with direct value its take more then 3min

With reference
DECLARE @iDeptID INT
SET @iDeptID = 37
SELECT JobCardId AS [Job Card No], JobCardId [JobCardID], CONVERT(VARCHAR(10),JobCardDate,105) [JobCardDate], CustPONumber [LPONumber], CONVERT(VARCHAR(10),CustPODate,105) [LPODate], LedgerName [Customer], ISNULL(dESCRIPTIONS,'-') [Product], ISNULL(PrintDetail1,'-') [PrintingDetail], ISNULL(PrintDetail2,'-') [OtherDetails], ISNULL(DispatchMeans,'-') AS [DispatchType], ISNULL(CONVERT(VARCHAR(18), AccptDate, 105),'-') [Exp.Date], InsertedBy [CreatedBy], ISNULL(UpDatedBy,'-') [UpdatedBy], CONVERT(VARCHAR(18),ORDERSIZE) [Order], ExOrderSize [Added], SalesReturn [SalesReturn], CONVERT(VARCHAR(18),Colplited) [Completed], CONVERT(VARCHAR(18),PENDING) [Pending], CONVERT(VARCHAR(18),PCS) +'/'+ CurrencyName [Rate], VAT [VAT], UnitsName [Type], JOBTYPE [JobType], CASE WHEN JobCardApproved = 0 THEN 'PENDING' WHEN JobCardApproved = 2 THEN 'REJECTED BY: ' + JobCardApprovedBy ELSE 'APPROVED BY: ' + JobCardApprovedBy END [Status], JFlag, ISNULL(JobCardApproved,0) JobCardApprovedType, OrderByDeptId
FROM JobCardMasterDEPARTMENT1('20210101','20210825')
WHERE Orderbydeptid = @iDeptID

ORDER BY Jid

Without Reference
SELECT JobCardId AS [Job Card No], JobCardId [JobCardID], CONVERT(VARCHAR(10),JobCardDate,105) [JobCardDate], CustPONumber [LPONumber], CONVERT(VARCHAR(10),CustPODate,105) [LPODate], LedgerName [Customer], ISNULL(dESCRIPTIONS,'-') [Product], ISNULL(PrintDetail1,'-') [PrintingDetail], ISNULL(PrintDetail2,'-') [OtherDetails], ISNULL(DispatchMeans,'-') AS [DispatchType], ISNULL(CONVERT(VARCHAR(18), AccptDate, 105),'-') [Exp.Date], InsertedBy [CreatedBy], ISNULL(UpDatedBy,'-') [UpdatedBy], CONVERT(VARCHAR(18),ORDERSIZE) [Order], ExOrderSize [Added], SalesReturn [SalesReturn], CONVERT(VARCHAR(18),Colplited) [Completed], CONVERT(VARCHAR(18),PENDING) [Pending], CONVERT(VARCHAR(18),PCS) +'/'+ CurrencyName [Rate], VAT [VAT], UnitsName [Type], JOBTYPE [JobType], CASE WHEN JobCardApproved = 0 THEN 'PENDING' WHEN JobCardApproved = 2 THEN 'REJECTED BY: ' + JobCardApprovedBy ELSE 'APPROVED BY: ' + JobCardApprovedBy END [Status], JFlag, ISNULL(JobCardApproved,0) JobCardApprovedType, OrderByDeptId
FROM JobCardMasterDEPARTMENT1('20210101','20210825')
WHERE Orderbydeptid = 37
ORDER BY Jid

Azure SQL Database
{count} votes