September 20, 2013 at 5:40 am
Hi All,
OK I have a function that is working exactly as I had wanted it to, but I want to be sure that what I have done has the correctly syntax and that I will not causing performance problems later.
The function searches the PersonTable with a list of family names. By using the left and right ID’s of the PersonTable it can determine all of the family and sub family members. (e.g. provide the grandparents’ sir name(s) and the function will return all of the generations below the grandparents. So the function parses an input string of names into multiple strings of names and then runs individual queries using each parsed name. For each query I insert the results into a table and then return that table from the function.
My question is, based on the function below is there anything that I am doing completely wrong by inserting the sub queries into the return table like I have done?
Execute the function like this:
SELECT * FROM [fnGetPersons] ('Name 1|Name 2','|') order by name
The function definition
CREATE FUNCTION [fnGetPersons]
(
@sPersonNames nvarchar(MAX),
@sParseChar varchar
)
RETURNS @PersonList TABLE
(
ID int,
name nvarchar(255),
description nvarchar(255)
)
AS
BEGIN
DECLARE @sPersonName varchar(255) = NULL
WHILE LEN(@sPersonNames) > 0
BEGIN
IF PATINDEX('%' + @sParseChar + '%',@sPersonNames) > 0
BEGIN
SET @sPersonName = SUBSTRING(@sPersonNames, 0, PATINDEX('%' + @sParseChar + '%',@sPersonNames))
INSERT INTO @PersonList
SELECT ID, name, description
FROM PersonTable WITH (nolock)
WHERE
(
left_ID BETWEEN
(
SELECT left_ID
FROM PersonTable WITH (nolock)
WHERE name = @sPersonName
)
AND
(
SELECT right_ID
FROM PersonTable WITH (nolock)
WHERE name = @sPersonName
)
)
SET @sPersonNames = SUBSTRING(@sPersonNames, LEN(@sPersonName + @sParseChar) + 1, LEN(@sPersonNames))
END
ELSE
BEGIN
SET @sPersonName = @sPersonNames
SET @sPersonNames = NULL
INSERT INTO @PersonList
SELECT ID, name, description
FROM PersonTable WITH (nolock)
WHERE
(
left_ID BETWEEN
(
SELECT left_ID
FROM PersonTable WITH (nolock)
WHERE name = @sPersonName
)
AND
(
SELECT right_ID
FROM PersonTable WITH (nolock)
WHERE name = @sPersonName
)
)
END
END
RETURN
END
September 20, 2013 at 8:08 am
You have a couple of issues going on here. First is the fact that this function will not perform well because it is a multi-statement table function. The performance of this can actually be worse than the dreaded scalar function.
Second issue is you have littered your code with NOLOCK. Are you familiar with the pitfalls of that hint?
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
I am speculating that your PersonTable is a hierarchy of some sort? It looks like it might be using nested sets but hard to tell.
We can help you turn this into a single statement function but we will need some more details. We will need the create table statement for PersonTable. We will need some insert statements for that table. We also would want to know what the desired output based on a given set of parameters would be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 6:37 am
Sean,
Thank you for the response. I've looked at the articles and I'll clean up the With (NOLOCK) statements. As stated in the articles I was placing these here since I only need read only results from the queries and I did not want to lock the tables.
Unfortunately I am not able to release the design of the Person table. But what I can say is that the table will only have a couple of thousand rows and the query, normally, will only return a couple hundred of rows. We are using this function to serve data to an even more complex view. I'll continue to look at the query to see if there is a way for me to simplify it into a single select on my own.
So I guess you saying that use Scalar Functions are not a good thing? I was under the impression for large dataset returns they would be bad, but for small datasets there should not be a concern. Am I missing something else?
Steve
September 23, 2013 at 8:12 am
slpgma (9/23/2013)
Unfortunately I am not able to release the design of the Person table. But what I can say is that the table will only have a couple of thousand rows and the query, normally, will only return a couple hundred of rows. We are using this function to serve data to an even more complex view. I'll continue to look at the query to see if there is a way for me to simplify it into a single select on my own.So I guess you saying that use Scalar Functions are not a good thing? I was under the impression for large dataset returns they would be bad, but for small datasets there should not be a concern. Am I missing something else?
Steve
Can't imagine what could be so secret about the design of a table (especially if you remove the columns not relevant to the issue) or you could create a dummy table that has the same datatypes as the relevant columns. But no problem.
Scalar functions have their place but they can lead to really performance issues. My comment really was because you have a table function but because it is multi-line the performance can be even worse than a scalar function.
Nearly anything in sql server will perform acceptably for small datasets. It is when we have to deal with actual production data that performance issues start popping up. The problem with the mindset of thinking that performance is not an issue "because this is a small dataset" is that now you have an example of how to code for a given data situation. Somebody may see that and use the same code for another situation with a large amount of data and not understand why it takes so long. Bottom line is that if you write all of your code to perform as best as possible you save yourself those headaches in the future.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply