April 11, 2011 at 2:06 pm
I have used functions to simplify code where multiple joins might be required. But I'm wondering what the impact on performance is if I use a function vs putting the join right in my initial sql code.
This is a very simple example, but which would be the preferred method to get the description.
SELECT co.co_num, coi.co_line, coi.item, i.description
FROM co co
LEFT JOIN coitem coi
ON coi.co_num = co.co_num
LEFT JOIN item i
ON i.item = coi.item
SELECT co.co_num, coi.co_line, coi.item, dbo.ItemDesc(coi.item)
FROM co co
LEFT JOIN coitem coi
ON coi.co_num = co.co_num
Does the function cause additional sql calls to the db, or does sql build the join itself either way?
April 11, 2011 at 2:12 pm
in your example, you are using a SCALAR function, which will degrade performance, especially with large data sets.
you could change that same function to be an inline table valued function, which would then perform just as well as a left outer join.
the syntax to call an ITVF is a little different, adn might look like this:
SELECT co.co_num, coi.co_line, coi.item, myf.Description
FROM co co
LEFT JOIN coitem coi
ON coi.co_num = co.co_num
CROSS APPLY dbo.ItemDesc(coi.item) myf
Lowell
April 13, 2011 at 7:26 am
Thanks Lowell. I had never used a table valued function or APPLY. This makes sense and is a perfect fit for another project of mine.
mpv
April 13, 2011 at 7:53 am
glad I could help a little bit; post your scalar function here if you need help converting it to an ILTV;
Lowell
April 13, 2011 at 8:01 am
I do have a question about how the APPLY works. I have looked at some other sites but still don't quite understand how it joins the rows from the function to the left table if no ON clause is present. Does there need to be a common column name in each?
April 13, 2011 at 8:25 am
it's kind of confusing, yeah.
no common data is required between the two.
I try to think of an ITVF the way UPPER() or ROW_NUMBER() function works: it's going to return something for every row i fiddle with...it's not really a join...it's just applied against my data.
Now based on the parameters that are passed, it's ging to return something, but that's a black box for this discussion...doesn't really matter.
I like to THINK that it's a join against the parameter column i passed, but technically, that's not correct, since i can have a function with no parameters.
I think it's more correct to say it's joined by the behind the scenes physical location.(SELECT %%physloc%%,* from YourTable)
Lowell
April 13, 2011 at 8:44 am
Got it. I was forgetting the fact that I am able to pass a parm to the function, whereas that is not available when joining to a table.
In my first example, my description function returns a different description per row because I am passing i.item as a parm. Then it basically attaches the description (or any other columns returned by the function) to the item rows.
So what happens if the function returns multiple rows? Does the row from the left table get repeated for each row from the function?
If I have a function called Sizes() that might return Small, Medium and Large and I CROSS APPLY it to the Item table where item='Shirt', would I have three rows like the following?
Shirt,Small
Shirt,Medium
Shirt,Large
April 13, 2011 at 9:02 am
yes, exactly...but don't take my word for it; play with this example i just slapped together: you get multiple rows back IF your function returns multipel rows:
CREATE Function AvailSizes(@ObjectName varchar(30))
returns table
AS
return
SELECT 'X-Small' As Size UNION ALL
SELECT 'Small' UNION ALL
SELECT 'Medium' UNION ALL
SELECT 'Large' UNION ALL
SELECT 'SX-Largemall'
GO
With myClothing(TheName)
AS
(
SELECT 'TShirt' UNION ALL
SELECT 'Jeans'
)
SELECT
myClothing.TheName,
myf.Size
FROM myClothing
CROSS APPLY dbo.AvailSizes(TheName) myf
Lowell
April 13, 2011 at 9:28 am
Thanks for the help understanding table-valued-functions and APPLY. It is all clear to me now.
Yesterday, after you told me about the tvf, I created one with a parm as it fit perfectly with what I was trying to accomplish. It helped me get rows from an audit table for a specific email address so I could email them the results.
The function returns all unprocessed rows if I pass NULL, or I can pass a specific email address.
Your suggestion and help opened up more possibilities, and opportunities for code reuse and is immensely appreciated.
Thank You,
mpv
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply