May 1, 2009 at 1:19 pm
Lynn Pettis (5/1/2009)
Just for S & G's, try this with a CROSS APPLY:
Sorry, I'm in an all-2000 environment right now. Testing is just a question of copying Gail's code from the link above and adding an extra case, however, if you're interested...
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
May 1, 2009 at 1:21 pm
GilaMonster (5/1/2009)
Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.Long answer - http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/
Do you have a ready blog article for every question ever raised?
I think I'm going to go print it all out and peruse over the weekend.
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
May 1, 2009 at 1:23 pm
Tao,
No; it just seems that way because the top N articles cover the top 80% of commonly-asked questions like this one about UDFs.
Paul
May 1, 2009 at 1:26 pm
Tao Klerks (5/1/2009)
GilaMonster (5/1/2009)
Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.Long answer - http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/
Do you have a ready blog article for every question ever raised?
I was wondering if you'd think that.
Actually it's the other way around. I use questions here as ideas for blogs and I've seen a lot of problems with UDFs over time. Half the reason I write blog entries is so that I have something I can direct people to instead of having to write a couple pages every time common questions come up.
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
May 1, 2009 at 1:38 pm
Okay, simple test, both returned the same actual execution plan.
Functions:
CREATE FUNCTION dbo.ufnDayOnly(@DateValue DateTime)
RETURNS table
AS
RETURN (select dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnly )
GO
CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)
RETURNS DateTime
WITH SCHEMABINDING
AS
BEGIN
RETURN dateadd(dd, datediff(dd,0, @DateValue),0)
END
GO
Test code:
create table dbo.LAPTest (
AccountID int,
Amount money,
Date datetime
);
go
create clustered index IX1_LAPTest on dbo.LAPTest (
AccountID asc,
Date asc
)
;
go
--===== Build the table 100 rows at a time to "mix things up"
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter < 1000000
BEGIN
--===== Add 100 rows to the test table
INSERT INTO dbo.LAPTest(
AccountID,
Amount,
Date)
SELECT TOP 100
AccountID =ABS(CHECKSUM(NEWID()))%50000+1,
Amount =CAST(CHECKSUM(NEWID())%10000 /100.0 AS MONEY),
Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
FROM
master.sys.columns t1
CROSS JOIN master.sys.columns t2
--===== Increment the counter
SET @Counter = @Counter + 100
END
;
go
select *, dbo.fn_DayOnly_DateTime (Date) from dbo.LAPTest;
select * from dbo.LAPTest cross apply dbo.ufnDayOnly(Date);
select *, dateadd(dd, datediff(dd,0, Date),0) as DateOnly from dbo.LAPTest;
go
drop table dbo.LAPTest;
go
Edit: Added a third query to the test code, but I haven't uploaded the third execution plan. Why, because it is identical to the other 2 I already uploaded.
May 1, 2009 at 3:47 pm
I did a little more testing and wanted to post the results of the testing. These are in the same order as the queries in my previous post:
CpuMs LogRds Elapsed
9344 5164 29506
CpuMs LogRds Elapsed
844 5164 29276
CpuM LogRds Elapsed
921 5164 29339
Viewing 6 posts - 106 through 111 (of 111 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy