question

HiteshkumarPatel-3921 avatar image
0 Votes"
HiteshkumarPatel-3921 asked SaurabhSharma-msft edited

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

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers