August 1, 2011 at 1:01 pm
Hi Techies,
I encountered new wierd behaviour (Atleast to me). Please give me your ideas and the reason behing it. Why SQL SERVER not picking Parallelism when there a Row Level Function?
Recently, I found one of the query is taking huge amount of time in production, i got an opportunity to enhance the performance. I have noticed the query is using only 1 CPU and its taking time.. The reason I have noticed is because of Row_Level_Function in the one of the Column. When i looked into the fucntion, it was nothing but "case when" Statement. I removed the function and wrote CASE When Statement directly on the Select Clause.. now the query is executing very fast and gives output of 4 Million rows in less than a min.
--------------------------------------------------------------------------------------------------------------
Existing Code:
Select Column1, Column2, dbo.fnConvertGender(Column3) As Column3.... Column n
FROM Table1
Definition inside the function "dbo.fnConvertGender"
RETURN (Case When @Parameter In ('M', 'F') Then @Parameter Else 'U' END)
--------------------------------------------------------------------------------------------------------------
I have tried adding MAXDOP option but NO USE..
Select Column1, Column2, dbo.fnConvertGender(Column3) As Column3.... Column n
FROM Table1
OPTION (MAXDOP 16) -- Max utilization of 16 CPU'S
Modified Code,
Select Column1, Column2, Case When Column3 In ('M', 'F') Then Column3 Else 'U' END As Column3.... Column n
FROM Table1
OPTION (MAXDOP 16) -- Max utilization of 16 CPU'S
--------------------------------------------------------------------------------------------------------------
Currently the Modified Code is running less than a min and my ETL runs very fast.. But I am not able to understand the reason behind!! can anyone help me understaning this behaviour?
August 1, 2011 at 1:21 pm
alter your function to be an inline table value function. That may do magic !
create itfv_x
returns table
as return (select your_case_statement as Et_Voila)
keep in mind you'll have to modify your query too !
... outer apply itvf_x ( thecol )
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 1, 2011 at 2:26 pm
Thats a good suggestion Thanks..
Can you help why row_Level_Function didnt use the Parallelism?
August 1, 2011 at 3:13 pm
Vijay Vasudevan (8/1/2011)
Thats a good suggestion Thanks..Can you help why row_Level_Function didnt use the Parallelism?
It's not so much that it isn't using parallelism, but that for every row returned, the code must hand over control to the function to get the result. In line functions aren't actually compliled into the code, they are still external functions that need to be called.
If you run a trace, you will see in the order of 4 million calls to the function if you have that many rows. Even if each call is only a mili-second, that is still 66 minutes. The worst case I've seen of this is where the function is used in a join...
You could use a table function as mentioned. There may be other tricks you can pull, but I'd need to test a bit. If any of my ideas work I'll post them.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 1, 2011 at 4:26 pm
Hats of to u! Crystal clear detailed explanation.. Now I got the reason why the query was taking time..
Thanks a lot guys..
August 1, 2011 at 6:56 pm
Leo.Miller (8/1/2011)
In line functions aren't actually compliled into the code, they are still external functions that need to be called.
I agree that's true about Scalar and mTVF's (multi-line Table Valued Functions) but I'm pretty sure that's not correct for iTVF's (inline Table Valued Functions).
Have you got some test code that shows what you mean, Leo?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2011 at 7:13 pm
I've never tested this with iTVFs, only with scaler functions, primaraly because this is where I've been called in to do performance tuning. The big issue is not normally the efficiency of the function, which is often very efficient, but the repeated calls. Since an iTVF is typically only called once it would be much like Vijay's original solution with the case statement, it would just be moved to the iTVF.
I've used a similar solution where I've just used a select into a temp table, applying the equivalent code to the function but in the select. Even though the same code was being applied, to at least the same number of rows, and sometimes even more rows, there was a significant performance improvement (from 15 minutes down to sub second). The obvious difference seen in the trace was that there wasn't the hundreds of thousands of calls to the function.
I'll test iTVFs and see what I get.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 1, 2011 at 8:12 pm
I can't see any real benefit of using an iTVF, since as far as I can see, you can still only pass it a single paramater, even though it returns a table. If the function needs to be applied to different tables, the iTVF is actually more restrictive because it can only be used for this particular table where the scaler function can be used on any parameter you want.
Also if you setup the query to return values from the iTVF based on one row at a time, you are back to the same setup where you need to call the function multiple times.
The only time (in this case) that the iTVF comes into its own is to return a table that is then joined to the original table to give you your desired column. This is because unless the iTVF is going to be used in multiple places you may as well just code the iTVF syntax as a case statement in the original query as you will then only hit the table once to get your final result instead of twice.
My recommendation in general is to only use functions when the code is going to be used in a significant number of places in your stored procs, and only if the number of calls from any one query is relativly low (a bit vauge I know but the trick is to test what works). Try to write the code in such a way that the sysntax is only executed logically as few times as possible. For this inline code may often be better than function calls.
Like a lot of things, just because they are nice to have and work, doesn't always mean they are the best solution. Often they were a good solution that is later applied in a bad way then things don't work so well.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 1, 2011 at 11:45 pm
As I got to understand it, an itvf can be fully bound and taken into account at plan compile time.
That may provide more intelligent solutions then we might expect and swift plans.
I guess the only way to see if its worth the modification is for OP to actually test on his data volume and distribution and post the results of his current plan and the various tfv varieties tested. (actual plan / stats io, time on)
I must admit the cases I did such optimization turned out to perform very, very better than when the svf:w00t:
IMO the order of usage should be: itvf / tvf / svf ... if you actually want to use tsql functions in the first place.
In any case, avoid encapsulation of the function with 'begin' ... 'end' because that may shut out optimizations by the engine.
Leo.Miller (8/1/2011)
.....Like a lot of things, just because they are nice to have and work, doesn't always mean they are the best solution. Often they were a good solution that is later applied in a bad way then things don't work so well.
Cheers
Leo
+1 :w00t:
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 2, 2011 at 6:31 am
Leo.Miller (8/1/2011)
Since an iTVF is typically only called once it would be much like Vijay's original solution with the case statement, it would just be moved to the iTVF.
Hmmm... different techniques, I guess. When I write iTVF's, they're typically called for each row using a CROSS APPLY. A delimited string splitter function is the classic example of such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2011 at 6:34 am
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2011 at 1:52 pm
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
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 2, 2011 at 1:55 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 parameter[/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 efficient. 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
XML?
That would be stupidly inefficient in this case but it would answer your demand ;-).
August 2, 2011 at 1:58 pm
Actually...
Dump the main query into a temp table.
Put a synonym on it.
Call the itvf and use the synonym and use that to return the final dataset :alien:
http://www.sqlservercentral.com/Forums/Topic1145600-2965-1.aspx#bm1149754
So in essence it depends... on your level of insanity and obfuscation needs.
August 2, 2011 at 2:09 pm
Ninja's_RGR'us (8/2/2011)
Actually...Dump the main query into a temp table.
Put a synonym on it.
Call the itvf and use the synonym and use that to return the final dataset :alien:
http://www.sqlservercentral.com/Forums/Topic1145600-2965-1.aspx#bm1149754
So in essence it depends... on your level of insanity and obfuscation needs.
I almost missed your last comment.
Nothing is ever so complex that it can't be made more complex.
In the context of the original question, XML doesn't count.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply