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

Question

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

Answer 1

If you have differences in execution time, then compare the execution plans.
See Display an Actual Execution Plan

Answer 2

Hi @Hiteshkumar Patel ,

Can you check the execution plan when you execute the query? Then compare it to see where the difference is.
Clear the cache execution plan before querying.
DBCC FREEPROCCACHE()
GO

Finally, if possible, can you post a screenshot of your execution plan?

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

This problem is always related to "parameter sniffing". I suggest you read this:
https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Answer 4

In your first batch, the optimizer does not know the value of the variable, since the query before the compilation starts, and therefore only makes an assumption from the density of the column, that is how many distinct values there are.

In the second case, the optimizer can work from the value and use it as constant. Since it knows more, your chances for a better plan increases. But, yes, chances. Sometimes optimization goes the other way.

Since we don't know the tables or that table-valued function you are using, we cannot say much more.

Answer 5

Hi @Hiteshkumar Patel ,

Agree with Erland.

But, yes, chances. Sometimes optimization goes the other way.

Also, is your purpose in asking this question to ask why the two times are different? If so, I believe Erland's answer is clear enough. If not, please feel free to let us know.

Best regards,
Seeya