November 13, 2020 at 9:09 pm
I really hope this isn't a dumb question. Is there a reason other then style or preference as to why you'd do this:
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
SET @Val1 = X
as opposed to this:
IF @Var1 = @VarA AND @var2 = @VarB AND @Var3 = @VarC
SET @Val1 = X
Kindest Regards,
Just say No to Facebook!November 13, 2020 at 9:31 pm
The only reason I can think of would be if there is other code which pertains only to the first condition – like this
IF @Var1 = @VarA
AND @var2 = @VarB
BEGIN
IF @Var3 = @VarC
SET @Val1 = X;
IF @Var4 = 'Gigantic chicken burger'
SET @Var5 = 'Munchies';
END;
Your code does not include a BEGIN, so I assume that's not the case and agree with you that both versions are equivalent.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 14, 2020 at 1:43 am
Nope. The source code I was reviewing was exactly as I showed I just replaced the literal names with dummy variables. It had no BEGIN~END and for the life of me I could not under stand why it was structured the way it was using multiple IFs versus one with multiple conditions.
Thanks
Kindest Regards,
Just say No to Facebook!November 16, 2020 at 2:29 pm
My only guess about it is readability and habit from other languages.
What I mean is some coding languages take a new line as being the end of that statement so they MAY have thought SQL treated it that way too. With regards to readability, long lines make for hard-to-read code. So they MAY have thought that since they wanted it on a new line, it needed to begin with a statement keyword (in this case, IF) as opposed to just another AND.
The other other case I can think of is if you have an ELSE after the second IF. something like:
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
SET @Val1 = X
ELSE
SET @Val1 = Y
but you indicated this was not the case.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 16, 2020 at 4:46 pm
I really hope this isn't a dumb question. Is there a reason other then style or preference as to why you'd do this:
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
SET @Val1 = Xas opposed to this:
IF @Var1 = @VarA AND @var2 = @VarB AND @Var3 = @VarC
SET @Val1 = X
I think the first snippet is bad for readability and is totally unnecessary. The only thing it seems to do differently is be a bit harder to read and adds line count for those that may still be paid by the line of code generated. The second snippet is easier than the first to read especially when in a hurry.
To be honest, though, I probably wouldn't use even the format of the second snippet, either. I'd probably use my personal standard "river format" to help support any documentation and to make it even easier to read.
--===== Some comment here to explain the "WHY"
IF @Var1 = @VarA --Maybe a comment here if it adds clarity
AND @var2 = @VarB --Maybe a comment here if it adds clarity
AND @Var3 = @VarC --Maybe a comment here if it adds clarity
SET @Val1 = X
;
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2020 at 5:40 pm
I think its what Brian Gale is speculating, personal preference/style. We use an accounting software package that is for a very vertical market and to my knowledge their philosophy is if you can read thru the Cliff notes version of "Learn SQL in 24 hours" then you have all the SQL training/knowledge you need and that shows up in a lot of the SQL code we see. I'm no SQL master but I do know you need more training/experience than that and so we often find ourselves having to make custom corrected versions of things we use like reports. The technology the software uses is such that we can for most reports go in and edit the code, add stuff that we need and re-write it when its not exactly optimized.
To be fair I'm finally starting to see some decent good code in newer updates which tells me they finally are treating the SQL side of things more seriously then in the past. I'm %99 certain they have some kind of tool that will build the query the developer needs when he/she is working on some data entry form or report for the software. Think of the query builder you can use with MS Access but an in house version of that, something the vendor made for the devs to use since most aren't trained in SQL like they should be. Its like this because the lead developers are programmers and not DB guys and so they've never taken the SQL side of things as seriously as they should have and you can see this not only in the queries they produce but the design choices made as the DB has evolved over the years. Because of this I often ask things and post example code that looks really bad. I'm just glad that I now know I can anonymize the sample queries with SentryOne Plan Explorer. Its been a real pain to manually do that when I needed to post an example of something.
Kindest Regards,
Just say No to Facebook!November 16, 2020 at 6:10 pm
YSLGuru wrote:I really hope this isn't a dumb question. Is there a reason other then style or preference as to why you'd do this:
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
SET @Val1 = Xas opposed to this:
IF @Var1 = @VarA AND @var2 = @VarB AND @Var3 = @VarC
SET @Val1 = XI think the first snippet is bad for readability and is totally unnecessary. The only thing it seems to do differently is be a bit harder to read and adds line count for those that may still be paid by the line of code generated. The second snippet is easier than the first to read especially when in a hurry.
To be honest, though, I probably wouldn't use even the format of the second snippet, either. I'd probably use my personal standard "river format" to help support any documentation and to make it even easier to read.
--===== Some comment here to explain the "WHY"
IF @Var1 = @VarA --Maybe a comment here if it adds clarity
AND @var2 = @VarB --Maybe a comment here if it adds clarity
AND @Var3 = @VarC --Maybe a comment here if it adds clarity
SET @Val1 = X
;
That adds even more line count, doesn't it? 🙂
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 16, 2020 at 6:13 pm
I agree, the first method is unnecessary.
As to whether I put all on one line: if they are straightforward, I would code them in one line with ANDs. If one or more conditions are complex and/or lengthy, then I would separate those conditions out.
IF @month = 'Jan' AND @day = 1 AND @is_first_day_of_year_job = 1
vs.
IF @month = 'Jan'
AND EXISTS(SELECT 1 FROM dbo.some_table st WHERE st.column1 = @var2 AND ...)
AND @var3 * 12.5 / (SELECT value FROM dbo.other_table ot WHERE ot.column = 5) = 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 17, 2020 at 2:22 pm
Jeff Moden wrote:YSLGuru wrote:I really hope this isn't a dumb question. Is there a reason other then style or preference as to why you'd do this:
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
SET @Val1 = Xas opposed to this:
IF @Var1 = @VarA AND @var2 = @VarB AND @Var3 = @VarC
SET @Val1 = XI think the first snippet is bad for readability and is totally unnecessary. The only thing it seems to do differently is be a bit harder to read and adds line count for those that may still be paid by the line of code generated. The second snippet is easier than the first to read especially when in a hurry.
To be honest, though, I probably wouldn't use even the format of the second snippet, either. I'd probably use my personal standard "river format" to help support any documentation and to make it even easier to read.
--===== Some comment here to explain the "WHY"
IF @Var1 = @VarA --Maybe a comment here if it adds clarity
AND @var2 = @VarB --Maybe a comment here if it adds clarity
AND @Var3 = @VarC --Maybe a comment here if it adds clarity
SET @Val1 = X
;That adds even more line count, doesn't it? 🙂
BWAAA-HAAAA! Yep! It does, so great pay increase 😀 but the real reason for it is the conditions are all vertically aligned (easy to read) and it's a whole lot easier to comment if the code isn't totally obvious and it may keep the section header a lot shorter. It also only uses a single IF instead of the unnecessary double-if in the first example.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2020 at 3:12 pm
Just a quick thought:
Any IF statement will add an evaluation step to the execution
Any AND will do the same
Conditionals (IFs) introduce branching, even if there is only one path.
😎
Suggest you simplify the statement, if possible, to a single equation
IF (((@Var1-@VarA) + (@Var2-@VarB) + (@Var3-@VarC)) = 0) SET @Val1 = X
To see the actual execution path, embed this in a CTE and query it 😉
November 17, 2020 at 3:27 pm
Just a quick thought: Any IF statement will add an evaluation step to the execution Any AND will do the same Conditionals (IFs) introduce branching, even if there is only one path. 😎 Suggest you simplify the statement, if possible, to a single equation
IF (((@Var1-@VarA) + (@Var2-@VarB) + (@Var3-@VarC)) = 0) SET @Val1 = XTo see the actual execution path, embed this in a CTE and query it 😉
But you've changed the logical conditions to do the SET. It's not worth that risk for such a tiny gain. Aside from the requirement that @varb and @varc must be numeric.
DECLARE @var1 int
DECLARE @var2 int
DECLARE @var3 int
DECLARE @vara int
DECLARE @varb int
DECLARE @varc int
SELECT @var1 = 10, @var2 = 20, @var3 = 30
SELECT @vara = 10, @varb = 20, @varc = 30
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
PRINT 'Old Cond 1 is true'
IF (((@Var1-@VarA) + (@Var2-@VarB) + (@Var3-@VarC)) = 0)
PRINT 'New Cond 1 is true'
SELECT @vara = 5, @varb = 25, @varc = 30
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
PRINT 'Old Cond 2 is true'
IF (((@Var1-@VarA) + (@Var2-@VarB) + (@Var3-@VarC)) = 0)
PRINT 'New Cond 2 is true, even though not all values are equal as required.'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 17, 2020 at 4:14 pm
In the old days, particularly with PL/1, we would do the multiple IF's. Because it was easy to slip a new punchcard in the deck when we found we had forgotten something. Each simple if-statement was on one punchcard, so you just hold the duplicate key down stop when you got to the then clause, you type in whatever you forgot. This was less trouble than a retyping the code with a single if – statement. Hey, 50 years ago, it was hard for programmers to get time on keypunch machines.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 17, 2020 at 4:28 pm
Eirikur Eiriksson wrote:Just a quick thought: Any IF statement will add an evaluation step to the execution Any AND will do the same Conditionals (IFs) introduce branching, even if there is only one path. 😎 Suggest you simplify the statement, if possible, to a single equation
IF (((@Var1-@VarA) + (@Var2-@VarB) + (@Var3-@VarC)) = 0) SET @Val1 = XTo see the actual execution path, embed this in a CTE and query it 😉
But you've changed the logical conditions to do the SET. It's not worth that risk for such a tiny gain. Aside from the requirement that @varb and @varc must be numeric.
DECLARE @var1 int
DECLARE @var2 int
DECLARE @var3 int
DECLARE @vara int
DECLARE @varb int
DECLARE @varc int
SELECT @var1 = 10, @var2 = 20, @var3 = 30
SELECT @vara = 10, @varb = 20, @varc = 30
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
PRINT 'Old Cond 1 is true'
IF (((@Var1-@VarA) + (@Var2-@VarB) + (@Var3-@VarC)) = 0)
PRINT 'New Cond 1 is true'
SELECT @vara = 5, @varb = 25, @varc = 30
IF @Var1 = @VarA AND @var2 = @VarB
IF @Var3 = @VarC
PRINT 'Old Cond 2 is true'
IF (((@Var1-@VarA) + (@Var2-@VarB) + (@Var3-@VarC)) = 0)
PRINT 'New Cond 2 is true, even though not all values are equal as required.'
My bad, sorry for that, here is the corrected logic in a "scalar operator minimalistic term"
😎
IF (
(
(ABS(SIGN(@Var1-@VarA)))
+ (ABS(SIGN(@Var2-@VarB)))
+ (ABS(SIGN(@Var3-@VarC)))
) = 0
)
SET @Val1 = X
November 17, 2020 at 4:35 pm
It's not worth that risk for such a tiny gain.
In a single execution, the gain may be small, bring it up to 100K/Sec and that difference can kill any server!
😎
The modern way of looking at optimisation is not how much you safe in execution time, rather how much you safe on your Cloud Hosted Platform expenses.
November 17, 2020 at 5:03 pm
ScottPletcher wrote:It's not worth that risk for such a tiny gain.
In a single execution, the gain may be small, bring it up to 100K/Sec and that difference can kill any server!
😎
The modern way of looking at optimisation is not how much you safe in execution time, rather how much you safe on your Cloud Hosted Platform expenses.
A few IF statements won't kill any modern server. Far, far worse is a wrong result. Repeatedly causing wrong results to try to hyper-tune something can can kill your job not just your server!
Besides, most SQL servers -- outside SSRS / OLAP -- have CPU to spare. It's I/O that's the real issue, always has been.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply