August 3, 2011 at 4:45 am
It is true that the optimizer and engine CANNOT use parallelism when one of those nasty old scalar UDFs is in play - it is just a limitation of the engine. One of MANY reasons why you should NEVER EVER use scalar UDFs. I am writing a chapter for the second SQL Server MVP Deep Dives book entitled "Death by UDF".
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 3, 2011 at 5:42 am
TheSQLGuru (8/3/2011)
It is true that the optimizer and engine CANNOT use parallelism when one of those nasty old scalar UDFs is in play - it is just a limitation of the engine. One of MANY reasons why you should NEVER EVER use scalar UDFs. I am writing a chapter for the second SQL Server MVP Deep Dives book entitled "Death by UDF".
When is it coming out?
August 3, 2011 at 9:52 am
Leo.Miller (8/2/2011)
Jeff Moden (8/2/2011)
Leo.Miller (8/1/2011)
I can't see any real benefit of using an iTVF, since as far as I can see, [font="Arial Black"]you can still only pass it a single paramater[/font], even though it returns a table.I'm not sure where you came up with such a misunderstanding but that's patently not true, Leo. iTVF can accept as many input variables as any other UDF.
Where are you getting this information from? I ask because I'd like to look it up and possibly have them make some corrections.
Sorry that was badly worded. I'd didn't mean one parameter, what I meant was one set of parameters, or another way of saying it was they can't accept a table, but they return a table. The whole tread is really about using a function to process many rows from a table, and why calling a function for each row becomes in efficent. What I was trying to say was that you can't call a function with a table of values as a parameter and get a table of values back.
Cheers
Leo
This was changed in 2008 (R2?). You CAN create a function taking in a table parameter.
See the BOL entry here for the specifics. It's definitely not as simple as just plopping a table together on the fly and throwing it at a procedure, but still - once you do, it's actually fairly efficient. We've been progressively replacing our CRUD procedures from some of our UI's with table-based CRUD procedures rather than single-row CRUD called in an RBAR fashion.
Have not tried a function this way, so I don't know how well they operate.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 3, 2011 at 11:30 am
Has anyone bench marked iTVF's both with and without SCHEMABINDING. I seem to recall an article by Paul White about iTVF's recommending that option so I use it with iTVF's and I usually get screamingly good performance out of them. However, being under constant pressure and production demands I never did do an actual bench mark between the 2 options.
Todd Fifield
August 3, 2011 at 12:18 pm
e.g. one of the first optimisations i did by altering a svf to an itfv resulted in:
before: duration +/-4000ms
after: duration +/- 328ms
This was for a single query that was run practically 400times a minute (multiple users)
Probably the query itself can be optimized more, but I wasn't alowed digging any further because they were happy with this gain.:ermm:
Seems worth the try to me.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 3, 2011 at 12:20 pm
😉
ALZDBA (8/3/2011)
e.g. one of the first optimisations i did by altering a svf to an itfv resulted in:before: duration +/-4000ms
after: duration +/- 328ms
This was for a single query that was run practically 400times a minute (multiple users)
Probably the query itself can be optimized more, but I wasn't alowed digging any further because they were happy with this gain.:ermm:
Seems worth the try to me.
That's why I usually do more test B4 telling them the good news. With a 90% speed gain it's easy to justify that you had to work a little more on it than usual ;-)... and that gets you that extra 5%
August 3, 2011 at 12:43 pm
Oh, I bet there's more than 5% still to be gained with that query. But that's not my call.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 3, 2011 at 12:54 pm
ALZDBA (8/3/2011)
But that's not my call.
Exactly my point :w00t:.
August 3, 2011 at 10:16 pm
Matt Miller (#4) (8/3/2011)
This was changed in 2008 (R2?). You CAN create a function taking in a table parameter.See the BOL entry here for the specifics. It's definitely not as simple as just plopping a table together on the fly and throwing it at a procedure, but still - once you do, it's actually fairly efficient. We've been progressively replacing our CRUD procedures from some of our UI's with table-based CRUD procedures rather than single-row CRUD called in an RBAR fashion.
Have not tried a function this way, so I don't know how well they operate.
This has proved to be a really interesting thread. I went over and tested the iTVF with a table of data as an input parameter, and is fairly efficient, although I still need to do some full testing.
The only drawbacks are it's quite a mission to set up, since yo need to create a user type first, then create your function. In your code you need to declare a variable of the type you previously created, select data into this and then use this as the parameter to the function:
create type LTT as table
(LTT_Col_ID int, LTT_SomeNumber int)
create function LTT_NegTab (@Input LTT
readonly )
returns table
as
return (select LTT_Col_ID ,
case
when LTT_SomeNumber >= 0 then 'Pos'
else 'Neg' end
as 'Signs'
from @Input)
declare @Local_LTT as LTT
insert into @Local_LTT
select top 1000 ID_Col, Somenumber from Test1
select * from dbo.LTT_NegTab (@Local_Ltt)
Also you are touching the data twice, once to initialise the variable with data (@Local_LTT above) and the second time when it goes through the iTVF. You may need to think in terms of filtering the data at the insert into @Local_LTT point where if you had the same code inline with a CASE you would touch the data once, applying the CASE statement and filtering rows at the same time.
My personal opinion... Unless you are going to use the iTVF in a significant number of places and want reusable code & single point for changes and less risk of coding differences, the complexity of design and the double touch of the data may not make it worth your while. I image a few junior developers may struggle to get their heads around this.
It does however give you a single call to the function for multiple input rows.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 4, 2011 at 7:40 am
Ninja's_RGR'us (8/3/2011)
TheSQLGuru (8/3/2011)
It is true that the optimizer and engine CANNOT use parallelism when one of those nasty old scalar UDFs is in play - it is just a limitation of the engine. One of MANY reasons why you should NEVER EVER use scalar UDFs. I am writing a chapter for the second SQL Server MVP Deep Dives book entitled "Death by UDF".When is it coming out?
Suppose to be released for PASS Summit this year, just like the last one. So much good stuff in these books!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 4, 2011 at 2:53 pm
I've run some tests ranging from a few hundred rows to a few million rows, and in most cases using the inline case is more efficient than passing the table to the function. At 2 million rows, 24 seconds vs 36 seconds total time. This is 50% more expensive.
This site http://msdn.microsoft.com/en-us/library/bb510489.aspx says the tipping point is around 1000 rows, depending on what you are doing and the nature of the data.
I'll be sticking with inline code for a while yet.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 5, 2011 at 9:19 am
Leo.Miller (8/4/2011)
I've run some tests ranging from a few hundred rows to a few million rows, and in most cases using the inline case is more efficient than passing the table to the function. At 2 million rows, 24 seconds vs 36 seconds total time. This is 50% more expensive.This site http://msdn.microsoft.com/en-us/library/bb510489.aspx says the tipping point is around 1000 rows, depending on what you are doing and the nature of the data.
I'll be sticking with inline code for a while yet.
Leo
1) Try using a temporary table instead of either table var, TVP or inline function.
2) I can show you an example of a SINGLE ROW in a table variable that gets you a BAD plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2011 at 9:23 am
TheSQLGuru (8/5/2011)
Leo.Miller (8/4/2011)
I've run some tests ranging from a few hundred rows to a few million rows, and in most cases using the inline case is more efficient than passing the table to the function. At 2 million rows, 24 seconds vs 36 seconds total time. This is 50% more expensive.This site http://msdn.microsoft.com/en-us/library/bb510489.aspx says the tipping point is around 1000 rows, depending on what you are doing and the nature of the data.
I'll be sticking with inline code for a while yet.
Leo
1) Try using a temporary table instead of either table var, TVP or inline function.
2) I can show you an example of a SINGLE ROW in a table variable that gets you a BAD plan.
Only 1? How many do you want me to show you :w00t:?
Table variables are pretty banned from my tools list (yes they have a place, but in OLAP??? it's a small one ;-))
August 5, 2011 at 7:07 pm
Leo.Miller (8/2/2011)
Jeff Moden (8/2/2011)
Leo.Miller (8/1/2011)
I can't see any real benefit of using an iTVF, since as far as I can see, [font="Arial Black"]you can still only pass it a single paramater[/font], even though it returns a table.I'm not sure where you came up with such a misunderstanding but that's patently not true, Leo. iTVF can accept as many input variables as any other UDF.
Where are you getting this information from? I ask because I'd like to look it up and possibly have them make some corrections.
Sorry that was badly worded. I'd didn't mean one parameter, what I meant was one set of parameters, or another way of saying it was they can't accept a table, but they return a table. The whole tread is really about using a function to process many rows from a table, and why calling a function for each row becomes in efficent. What I was trying to say was that you can't call a function with a table of values as a parameter and get a table of values back.
Cheers
Leo
That's the beauty of iTVF's, Leo. If they're written correctly and if you use them in a Cross Apply, they effective become part of the main query and the whole table is processed at once.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2011 at 9:30 am
Jeff Moden (8/5/2011)
Leo.Miller (8/2/2011)
Jeff Moden (8/2/2011)
Leo.Miller (8/1/2011)
I can't see any real benefit of using an iTVF, since as far as I can see, [font="Arial Black"]you can still only pass it a single paramater[/font], even though it returns a table.I'm not sure where you came up with such a misunderstanding but that's patently not true, Leo. iTVF can accept as many input variables as any other UDF.
Where are you getting this information from? I ask because I'd like to look it up and possibly have them make some corrections.
Sorry that was badly worded. I'd didn't mean one parameter, what I meant was one set of parameters, or another way of saying it was they can't accept a table, but they return a table. The whole tread is really about using a function to process many rows from a table, and why calling a function for each row becomes in efficent. What I was trying to say was that you can't call a function with a table of values as a parameter and get a table of values back.
Cheers
Leo
That's the beauty of iTVF's, Leo. If they're written correctly and if you use them in a Cross Apply, they effective become part of the main query and the whole table is processed at once.
That's the magic behind iTVF's.
And SQL is very good in handling SETs !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply