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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
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 110 (of 110 total)
You must be logged in to reply to this topic. Login to reply