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

Transact-SQL
1

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

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.

2

hi

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

3

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.

4

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

5

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

6

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
7

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]

8

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.