Agfdhyk

I will see you what is the problem...

SELECT 

    SUM(A.POSTEDQTY),

    SUM(A.POSTEDVALUE),

    SUM(A.PHYSICALVALUE),

    SUM(A.DEDUCTED),

    SUM(A.RECEIVED),

    SUM(A.RESERVPHYSICAL),

    SUM(A.RESERVORDERED),

    SUM(A.REGISTERED),

    SUM(A.PICKED), 

    SUM(A.ONORDER),

    SUM(A.ORDERED),

    SUM(A.ARRIVED),

    SUM(A.QUOTATIONRECEIPT),

    SUM(A.QUOTATIONISSUE),

    SUM(A.AVAILPHYSICAL),

    SUM(A.AVAILORDERED),

    SUM(A.PHYSICALINVENT) 

FROM 

    INVENTSUM A WITH(NOLOCK) 

WHERE 

    ((A.DATAAREAID = @P1) 

    AND ((A.ITEMID = @P2) 

    AND (A.CLOSED = @P3))) 

    AND EXISTS (SELECT 'x' FROM INVENTDIM B WITH (NOLOCK) 

                WHERE ((B.DATAAREAID = @P4) 

                  AND ((((B.INVENTDIMID = A.INVENTDIMID) AND (B.CONFIGID = @P5)) 

                  AND (B.INVENTLOCATIONID = @P6)) AND (B.INVENTBATCHID = @P7))))

Query slow performance

declare @p2 int

set @p2=180157421

declare @p3 int

set @p3=16

declare @p4 int

set @p4=1

declare @p5 int

set @p5=1

exec sp_cursorexecute 1073741895,@p2 output,@p3 output,@p4 output,@p5 output,N'ens',N'V89710821',0,N'ens',N'PO16628',N'AS',N'OFS028748'

select @p2, @p3, @p4, @p5

Query parameters slow performance

SELECT SUM(A.POSTEDQTY),SUM(A.POSTEDVALUE),SUM(A.PHYSICALVALUE),SUM(A.DEDUCTED),SUM(A.RECEIVED),SUM(A.RESERVPHYSICAL),SUM(A.RESERVORDERED),SUM(A.REGISTERED),SUM(A.PICKED),SUM(A.ONORDER),SUM(A.ORDERED),SUM(A.ARRIVED),SUM(A.QUOTATIONRECEIPT),SUM(A.QUOTATIONISSUE),SUM(A.AVAILPHYSICAL),SUM(A.AVAILORDERED),SUM(A.PHYSICALINVENT) FROM INVENTSUMDELTA A WHERE ((A.DATAAREAID=@P1) AND (((A.ITEMID=@P2) AND (A.ISAGGREGATED=@P3)) AND (A.TTSID=@P4))) AND EXISTS (SELECT 'x' FROM INVENTDIM B WHERE ((B.DATAAREAID=@P5) AND ((((B.INVENTDIMID=A.INVENTDIMID) AND (B.CONFIGID=@P6)) AND (B.INVENTLOCATIONID=@P7)) AND (B.INVENTBATCHID=@P8))))

Same query good performance

declare @p2 int

set @p2=180157423

declare @p3 int

set @p3=16

declare @p4 int

set @p4=1

declare @p5 int

set @p5=1

exec sp_cursorexecute 1073741904,@p2 output,@p3 output,@p4 output,@p5 output,N'ens',N'V89710821',0,5666550353,N'ens',N'PO16628',N'AS',N'OFS028748'

select @p2, @p3, @p4, @p5

Parameters same query good performance

In SQLServer Profiler you can see that the same query executes in 0ms or over 10000ms

And if I try to execute the same query (10000ms) with the same parameters directly it takes 0ms

What is happening?

Thanks!! :)

The first time you execute the query, the db engine have to prepare the statement send to db search the data and return the result. The second time the statement is already prepared and the data is on the cache. That is why the second run is always faster

https://littlekendra.com/2016/11/25/why-is-my-query-faster-the-second-time-it-runs-dear-sql-dba-episode-23/