USP VS TVF - why is one so much slower than the other?

[SergioM]

I'm working on a complicated query but I'm at a loss as to why it is so slow.

• As a table value function * (https://pastebin.com/CjN2sEkd) it takes ~1 minute to run. It uses CTEs to group the data.

• As a stored procedure (https://pastebin.com/2UzJu6Hg) it takes ~4 seconds to run. It uses temporary tables to group the data.

The logic & the date range does not change whatsoever between the two.

I suppose I could just go the dumb route that works & switch this over to a USP. My life would not be any different & I would have a working solution. But I was hoping someone could point me to some mistake that I've made so that I can have it as a TVF (which I prefer). Or if there is a known limitation that I should also become aware of.

[harishgg1]

hi

please see below link .. hope it has the explanation you are looking for ..

Table Valued Function Killing My Query Performance

[SergioM]

I appreciate the effort. Though this explains why TVF which returns a table as a variable would decrease in performance VS an inline TVF. Mine is an inline TVF.

[yosiasz]

How are you using the TVF? Show us the code that uses the tvf

[SergioM]

I've included both variations of the code in the pastebin link above.

• As a table value function * (https://pastebin.com/CjN2sEkd ) it takes ~1 minute to run. It uses CTEs to group the data.
• As a stored procedure (https://pastebin.com/2UzJu6Hg ) it takes ~4 seconds to run. It uses temporary tables to group the data.

[yosiasz]

Yes I have seen those, I am asking about the code that shows how you use the TVF

select tvf(b.crumbs), *
  from dbo.bread b

[SergioM]

I didn't understand the question. Though even now do not understand the relevance. Either way, here is how I would call either one.

SELECT * FROM [database].[dbo].[tvfADS_company_Shipping] (GETDATE()-1, GETDATE())

EXEC [usp_name]

[jeffw8713]

Is there any way you could post the code here - instead of using a link? I am not able to access that site from work and it would be much easier if the code was posted here.

When posting - use 3 back-ticks before the code and after the code and it will be contained in a code block.

Since I cannot see the code...is the function written as an inline-table valued function (iTVF) or a multi-statement table valued function? If written as an inline-table valued function - then most likely the problem is due to how those temp tables are being utilized in the procedure.