June 10, 2008 at 7:03 am
whether inline function execution plan stored in cache like stored procedure
and what is exact difference between SP and inline function(performance wise)
June 10, 2008 at 11:54 am
Inline functions are cached, just like procs. If they are deterministic (same results every time from same inputs), their results are cached and become quite fast.
When comparing them to procs, do you mean inline select (table) functions, or inline scalar functions?
An inline select function has exactly the same performance as a proc with the same select in it. I've tested this quite thoroughly.
An inline scalar function (returns one value instead of a table), doesn't really have the same purpose as a proc, so really can't be compared.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 11, 2008 at 11:01 am
G:
I have used inline scalar functions in DB2 but I was not aware that they were available in MS SQL Server. Is this a Katmai feature or is this available in SQL Server 2005? I tried coding one up in 2005 and it rejected. There is no reference to this in my copy of 2005 BOL. If this is currently available it will certainly solve some performance issues that I have seen.
Kent
June 13, 2008 at 4:43 am
June 13, 2008 at 5:16 am
I am not referring to "Inline table function", I am referring to "Inline Scalar Functions". I understand the concept and yes, I use them in DB2, but I have never seen / used one in MS SQL Server from version 4 all the way to the present.
June 13, 2008 at 5:26 am
They've been there since SQL 2000, along with the table-valued functions.
eg:
CREATE FUNCTION DoSomeThing (@ID int) RETURNS INT
AS
BEGIN
DECLARE @Result INT
SELECT @Result = SomeValue FROM SomeTable Where SomeKey = @ID
RETURN @Result
END
They generally don't solve performance prolems. All too often, they cause them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2008 at 5:31 am
Fair enough. My semantics are wrong. What I am talking about is something in which there is no procedural code. This is what I incorrectly thought G was talking about:
create function dbo.cube(x float)
returns float
return( x * x * x )
go
Which I don't think exists
June 13, 2008 at 5:34 am
You mean like this?
create function dbo.cube(@x float)
returns FLOAT AS
BEGIN
return( @x * @x * @x )
END
GO
SELECT dbo.cube(4)
😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2008 at 5:48 am
No.
I have clearly not communicated well so I will let this fall; sorry. Also, I think this is a large diverge from the original post. I started a thread to discuss in the MSDN forum here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3482768&SiteID=1
June 13, 2008 at 5:57 am
I'm sorry I misunderstood you. If you're willing to explain more what you mean (or link to DB2 documetation) I'll try and help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2008 at 6:03 am
Really, I had more in mind what is discussed on this page:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273443
June 13, 2008 at 7:28 am
Ah. Now I see what you're getting at.
I'd probably call that a macro-type replacement than an inline function, but that's just nomenclature. Probably more of a parser enhancement than an engine change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2008 at 8:05 am
I agree.
June 15, 2008 at 1:08 pm
I agree with the OP, in fact it's one of my biggest wishes for SQL Server: true "inline" scalar functions or more properly "persistent parametrized scalar expression aliasing".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 15, 2008 at 1:11 pm
Actually, I would like "non-persistent scalar expression aliasing" also, in the same way that WITH (CTE's) is Table/Set expression aliasing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply