November 17, 2020 at 5:33 pm
Eirikur Eiriksson wrote: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.
I've seen reductions of logical paths (conditional gateways within the execution plan) resulting in a large gain in performance, nowadays we should be calling it "code efficiency"
😎
Here is a good example
https://www.sqlservercentral.com/forums/topic/do-you-have-a-stripnonnumeric-itvf-function#bm1629360
November 17, 2020 at 5:58 pm
15 replies already And here I was initially concerned this might be a dumb question to even ask
Kindest Regards,
Just say No to Facebook!November 17, 2020 at 6:03 pm
on this forum, as far as I'm concerned, there is no such thing as a dumb question!
😎
The dumb thing is not asking the question.
For fun, here is a Haiku answer
You may think you have
but a questionable question
think the work you save
November 17, 2020 at 7:59 pm
The dumb thing is not asking the question.
The second dumb thing (not applicable here!) is not listening to the answers given by seasoned pros, because you don't like what you are hearing.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2020 at 4:54 pm
ScottPletcher wrote:Eirikur Eiriksson wrote: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.
I've seen reductions of logical paths (conditional gateways within the execution plan) resulting in a large gain in performance, nowadays we should be calling it "code efficiency"
😎
Here is a good example
https://www.sqlservercentral.com/forums/topic/do-you-have-a-stripnonnumeric-itvf-function#bm1629360
But that's not from removing a couple of IFs or ANDs (while introducing bugs!). I still say that such hyper-tuning is actually silly since that time could be spent tuning something with a lot more payback.
You've got to consider the opportunity cost as well as any supposed tiny, tiny performance "gain".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 18, 2020 at 10:28 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.
Actually most companies had separate keypunch operators, so it was very rare for a developer to punch his own cards: he/she would be (much) too slow and thus use too much machine time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 18, 2020 at 11:43 pm
IF (
(
(ABS(SIGN(@Var1-@VarA)))
+ (ABS(SIGN(@Var2-@VarB)))
+ (ABS(SIGN(@Var3-@VarC)))
) = 0
)
SET @Val1 = X
And you think all those mathematical operations and the added functions will somehow be cheaper than just comparing numeric variables to each other?? I don't think so.
Also, keep in back of your mind that the query plan the optimizer shows us is still extremely simplified compared to what's actually being executed. Thankfully SQL spares us most of the truly gory internal details.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 19, 2020 at 3:26 pm
>> Actually most people had separate keypunch operators, it was very rare for a developer to punch his own cards: he/she would be too slow at it and thus use the machine for too long. <<
The way it works in shops where I was employed in the 1960s was to submit a coding form (one for Fortran and one for COBOL programs) and the keypunch gals would send it back to you with a rubber band and your coding forms. Usually, there was a pickup window or pigeonholes to find your card decks. But the turnaround time of getting one or two single cards punched was much too slow if you went through the regular procedure. So there was usually one keypunch machine, off to the side on a raised platform for the programmers or other people that needed to do some kind of quick fix. By putting it on a platform there is no way to get a chair under it and sit down. There was always a queue so programmers were under pressure to be quick about it. A clever piece of human engineering 🙂 what we did was save all of our old card decks and reuse some of them in the next program. Not just the JCL (job control language) that was part of the IBM 360 operating system, but common program statements. The COBOL programmers kept their data division cards and Fortran programmers had all of their file declarations (sort of like DDL in SQL) for reuse.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 19, 2020 at 4:07 pm
But that's not from removing a couple of IFs or ANDs (while introducing bugs!). I still say that such hyper-tuning is actually silly since that time could be spent tuning something with a lot more payback.
You've got to consider the opportunity cost as well as any supposed tiny, tiny performance "gain".
Must correct you here, the naive approach to this algorithm is exactly a series of IFs and ANDs, keep numerical and discard any other characters. Changing the algorithm to only rely on one less than operator improved the performance drastically, roughly 70% reduction in execution time. That is not a "tiny, tiny performance gain" in my books.
😎
November 19, 2020 at 4:10 pm
Eirikur Eiriksson wrote:IF (
(
(ABS(SIGN(@Var1-@VarA)))
+ (ABS(SIGN(@Var2-@VarB)))
+ (ABS(SIGN(@Var3-@VarC)))
) = 0
)
SET @Val1 = XAnd you think all those mathematical operations and the added functions will somehow be cheaper than just comparing numeric variables to each other?? I don't think so.
Also, keep in back of your mind that the query plan the optimizer shows us is still extremely simplified compared to what's actually being executed. Thankfully SQL spares us most of the truly gory internal details.
This is not about what I think, it is what I know and have extensively tested. Mathematical operations are far less expensive than logical path branching in the execution plan.
😎
November 24, 2020 at 6:52 am
Talking about efficiency, using variables and flow control in T-SQL says "defficiency" by itself.
More appropriate T-SQL statements should look like this:
SELECT @Val1 = case when @Var1=@VarA and @Var1=@VarA and @Var1=@VarA then x end
or
SELECT @Val1 = x
WHERE @Var1=@VarA and @Var1=@VarA and @Var1=@VarA
And, of course, those compared values should not be variables but values from some joined tables.
_____________
Code for TallyGenerator
November 24, 2020 at 3:13 pm
Talking about efficiency, using variables and flow control in T-SQL says "defficiency" by itself.
More appropriate T-SQL statements should look like this:
SELECT @Val1 = case when @Var1=@VarA and @Var1=@VarA and @Var1=@VarA then x end
or
SELECT @Val1 = x
WHERE @Var1=@VarA and @Var1=@VarA and @Var1=@VarA
And, of course, those compared values should not be variables but values from some joined tables.
Sorry Sergiy,
maybe I'm being a little slow here, but your post looks to me as being contradictive.
😎
November 24, 2020 at 6:14 pm
ScottPletcher wrote:Eirikur Eiriksson wrote:IF (
(
(ABS(SIGN(@Var1-@VarA)))
+ (ABS(SIGN(@Var2-@VarB)))
+ (ABS(SIGN(@Var3-@VarC)))
) = 0
)
SET @Val1 = XAnd you think all those mathematical operations and the added functions will somehow be cheaper than just comparing numeric variables to each other?? I don't think so.
Also, keep in back of your mind that the query plan the optimizer shows us is still extremely simplified compared to what's actually being executed. Thankfully SQL spares us most of the truly gory internal details.
This is not about what I think, it is what I know and have extensively tested. Mathematical operations are far less expensive than logical path branching in the execution plan.
😎
Not on something that simple. It's just not worth the human time, i.e., the opportunity cost is just not worth it, period.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 24, 2020 at 6:23 pm
Eirikur Eiriksson wrote:ScottPletcher wrote:Eirikur Eiriksson wrote:IF (
(
(ABS(SIGN(@Var1-@VarA)))
+ (ABS(SIGN(@Var2-@VarB)))
+ (ABS(SIGN(@Var3-@VarC)))
) = 0
)
SET @Val1 = XAnd you think all those mathematical operations and the added functions will somehow be cheaper than just comparing numeric variables to each other?? I don't think so.
Also, keep in back of your mind that the query plan the optimizer shows us is still extremely simplified compared to what's actually being executed. Thankfully SQL spares us most of the truly gory internal details.
This is not about what I think, it is what I know and have extensively tested. Mathematical operations are far less expensive than logical path branching in the execution plan.
😎
Not on something that simple. It's just not worth the human time, i.e., the opportunity cost is just not worth it, period.
Take care of the processing cycles and the CPUs will take care of themselves!
😎
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply