question

HiteshkumarPatel-3921 avatar image
0 Votes"
HiteshkumarPatel-3921 asked SeeyaXi-msft answered

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






sql-server-generalsql-server-transact-sql
· 5
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.


How fast is the next query?

SELECT . . . .
WHERE Orderbydeptid = 37 -- (also try @iDeptID)
ORDER BY Jid
option (recompile)


0 Votes 0 ·

SELECT . . . .
WHERE Orderbydeptid = 37 this one total time 12min and 33sec Total Recore: 2796
ORDER BY Jid

SELECT . . . .
WHERE Orderbydeptid = @iDeptID this one total time 0min and 1sec Total Recore: 2796
ORDER BY Jid

if i try with another store which have more entry then this store its
SELECT . . . .
WHERE Orderbydeptid = 3 this one total time 0min and 2sec Total Recored: 3196
ORDER BY Jid

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 HiteshkumarPatel-3921 ·

Did you also try the next query?

SELECT . . . .
WHERE Orderbydeptid = 37
ORDER BY Jid
OPTION (RECOMPILE)


0 Votes 0 ·
Show more comments
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered HiteshkumarPatel-3921 commented

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


· 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.

still 15 gone query is still running if I enter another store id like 3, 9, 2 etc. its working ok also this stores have more entry. But when I enter 37 its going infinity

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft converted comment to answer

Hi @HiteshkumarPatel-3921 ,

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.

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.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered HiteshkumarPatel-3921 commented

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/

· 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.

if this is true then why its not happen with all stores other stores either I put parameter or direct value its take same time

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

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.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @HiteshkumarPatel-3921 ,

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
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.