Performance impact of Inline Table Value Function in SQL

Question

I have a stored procedure using an inline Table Value Function (ITVF) in a data warehouse system. If I copy the code of ITVF to the procedure itself then it makes a lot of performance improvement (from 50 seconds to 9 seconds). I was under impression that ITVF are executed just like the code inside the procedure itself.

I can see there is a huge difference between Actual Rows and Estimated Rows in the query plan when using ITVF.

Is there any possibility of parameter sniffing in ITVF? What else could the reason? Thanks.

It's hard to say without seeing the code itself... – Zohar Peled Nov 02 '18 at 10:37 — Zohar Peled, Nov 02 '18 at 10:37

score 0 · Answer 1 · answered Nov 02 '18 at 11:04

0

Your function made query non-sargable, so query optimizer cannot create proper execution plan for it. That's why you have huge difference between actual and estimated rows.

answered Nov 02 '18 at 11:04

j.v.

977
6
15

I don't think that you can make that statement in the general case. Do you have supporting documentation for it? – Ben Thul Nov 02 '18 at 16:39

Performance impact of Inline Table Value Function in SQL

1 Answers1