I am asking wondering if any of you can help me with ideas. You see, I am tuning a store procedure, call it proc1, and it calls internally a table function. The procedure have a table input parameter (idk why, but it was build like that) and then it executes the table function:
Call to the procedure as it is being done by front end:
declare @p3 dbo.tblType
insert into @p3
values( 11 , 1 , 2 , 5 , 0 , 0 , 0 , 0 , 0 , 'true', 'test')
exec sp_executesql N'proc1 @tblSearch',N'@tblSearch [dbo].[tblType] READONLY',@tblSearch=@p3
Call to the table function within the procedure.
INSERT INTO #TempTable
SELECT *
FROM Func1(@Param1, @Param2,@Param3)
The spooky thing is this, I tuned up the table function to reduce the timing to execute, however, the procedure it is taking about the same. Then I try to figured out the problem and simplify the problem. Finally I put the raw execution of the table function with values.. and it works. So if the procedure got this, it executes in 1 second, but if I change it with variables, it takes ages (8 minutes). And I review and verify several times that the values that I am binding are the same that those I am putting raw.
lightspeed execution:
SELECT *
FROM [dbo].[Func1] (11,1,2,5,0,0,0,0,0,0)
ORDER BY 1
Snail slow execution:
SELECT *
FROM [dbo].[Func1] (@IDa, @IDb, @OtherActionId, @Idc, @IDd, @IDe, @IDf, @IDg, @IDh, @IDi)
ORDER BY 1
This issue is driving me crazy, it is nuts. How come the very same call have such different timings?? just in one I put the values and I swear that I verify the input parameters, I put the below statement just before the execution so I know them:
SELECT @IDa '@IDa',@IDb '@IDb',@OtherActionId '@OtherActionId',@Idc '@Idc',@IDd '@IDd',@IDe '@IDe',@IDf '@IDf',@IDg '@IDg',@IDh '@IDh',@IDi '@IDi'
Any idea? Perhaps related to sp_executesql? Has anyone had similar issues?
Thanks in advance and kind regards!
