0

First, to define what I'm talking about, the "length" of a source is how many characters it has. Having more characters allows improving readability by using more descriptive function and variable names, as well as indentation.

In compiled languages, the length of the source's text does not matter, but in interpreted languages (which AFAIK SQL is) it can. Does that also extend to stored procedures/functions?

In other words, do I need to try to minimize something like this:

CREATE FUNCTION dbo.Add_Highest_Subjects
    (@studentScoreInMath INT,
     @studentScoreInBiology INT,
     @studentScoreInLiterature INT)
RETURNS INT
AS
BEGIN
    DECLARE @bestOfTwo INT

    SET @bestOfTwo = 
        CASE
            WHEN @studentScoreInMath > @studentScoreInBiology 
                AND @studentScoreInBiology > @studentScoreInLiterature
                THEN @studentScoreInMath + @studentScoreInBiology

            WHEN @studentScoreInMath > @studentScoreInBiology 
                AND @studentScoreInBiology < @studentScoreInLiterature
                THEN @studentScoreInMath + @studentScoreInLiterature

            ELSE @studentScoreInBiology + @studentScoreInLiterature
        END

    RETURN @bestOfTwo
END

into that:

CREATE FUNCTION dbo.ahs(@m INT, @b INT, @l INT)
RETURNS INT
AS
BEGIN
    DECLARE @r INT

    SET @r = CASE 
                 WHEN @m > @b AND @b > @l THEN @m + @b 
                 WHEN @m > @b AND @b < @l THEN @m + @l 
                 ELSE @b + @l 
             END
    RETURN @r
END

Or is there no need?

PS: this is quite hard to search for, as I keep finding explanations of how the LEN function works, instead of the answer to my question.

PPS: I can't believe I have to say this: the function above is an example stripped down to essentials (variable names and indentations). I don't need advice on how to add two integers efficiently, but thank you.

Improve this question
15
  • 9
    You have two horses. You can race them and find out. Though this is going to some micro optimisation if any at all relative to everything else happening in the SQL Server Commented Oct 9 at 9:25
  • 6
    What's the actual problem here? SQL doesn't get executed as-is, the scripts get compiled to execution plans. The content of a function is infinitely more important than the source size. If the function is large enough for the size to matter, the content is wrong. Commented Oct 9 at 9:33
  • 3
    Both those functions will generate the same execution plan that will be slower unless the functions can be inlined, allowing SQL Server to optimize the entire query instead of calling the function on every row. And both cold become a single-line UDF with RETURN. The second one though is almost impossible to maintain Commented Oct 9 at 9:37
  • 1
    Thank you, @panagiotis-kanavos for providing an actual answer ("stored functions are compiled, not interpreted"), instead of recommending I "race the horse" (or "eat the pudding" as they say.) Not everything needs to be an experiment. If you type this up into an answer (a link to a manual that says the same would make it perfect,) I can accept it. Commented Oct 9 at 9:40
  • 2
    I would expect the length of the variable names would have a slight detrimental effect simply due to the parsing time, but it's going to be absolutely miniuscule. Like maybe you'd have an issue if you had 10,000 lines of whitespace everywhere, or 1000s of giant names, but in normal use it won't make a difference, certainly not against the obvious downside of using a slow scalar function in the first place. Honestly, you are nano-optimizing here, focus on actual performance issues instead. Commented Oct 9 at 11:25

1 Answer 1

Reset to default
0

In short: No, variable name lengths usually do not matter.

Interpreted languages usually parse variable names and assign IDs or some other form of identifier, so usually all variable names tend to end up with names of the same lengths internally.

There are a few other performance concerns that may arise depending on database access, data transfer, and so on. Since some of these processes might actually involve the storage or transfer of these huge variable names, it would indeed slow down the process, increase the amount of data transferred and various other issues. Normally though? A variable name that isn't 200+ characters barely makes a difference and I don't know if 200 characters would already even be measurable in useful frames of reference.

Just be advised that long names can hurt readability.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Just be advised that long names can hurt readability. And that single character names dramatically harm comprehension.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.