The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
Oracle calculation performance
How does Oracle handle repeated calculations? Is it evaluated once or is it evaluated each time it is encountered?
For instance in a case statement creating buckets for counting
CASE
WHEN A Date - Discharge Date < 31 THEN '000-030'
WHEN A Date - Discharge Date < 61 THEN '031-060'
WHEN A Date - Discharge Date < 91 THEN '061-090'
ELSE '091+'
END
Can this be rewritten in the other CASE form as
CASE A Date - Discharge Date
WHEN ???? THEN '000-030'
...
ELSE '091+'
END
I realize the SQL is not in Oracle SQL format. My question is on the substance as the tool I use will put it in Oracle format when the query is built.
I know some databases will calculate the A Date - Discharge Date once and others would in this case calculate it 3 times. This is just an example. My real statement I am working with has the calculation repeated 14 times thus my concern for speed
mlmcc
For instance in a case statement creating buckets for counting
CASE
WHEN A Date - Discharge Date < 31 THEN '000-030'
WHEN A Date - Discharge Date < 61 THEN '031-060'
WHEN A Date - Discharge Date < 91 THEN '061-090'
ELSE '091+'
END
Can this be rewritten in the other CASE form as
CASE A Date - Discharge Date
WHEN ???? THEN '000-030'
...
ELSE '091+'
END
I realize the SQL is not in Oracle SQL format. My question is on the substance as the tool I use will put it in Oracle format when the query is built.
I know some databases will calculate the A Date - Discharge Date once and others would in this case calculate it 3 times. This is just an example. My real statement I am working with has the calculation repeated 14 times thus my concern for speed
mlmcc
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 74
In general, a calculation in the selected results will be evaluated only once, or perhaps once per row if the calculation involves values for that row (as in your example.)
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Thanks. That is what I expected but was concerned that it might evaluate each one in turn thus causing the query to run long.
mlmcc
mlmcc
Here the code will run 1-3 times.. until it gets a match
Don't Use CASE here
Don't Use CASE here
DDate = A Date - Discharge Date
IF DDate < 31 THEN '000-030'
ELSE IF DDate < 61 THEN '031-060'
Else If DDate< 91 THEN '061-090'
ELSE '091+'
END
CASE
WHEN A Date - Discharge Date < 31 THEN '000-030'
WHEN A Date - Discharge Date < 61 THEN '031-060'
WHEN A Date - Discharge Date < 91 THEN '061-090'
ELSE '091+'
END
The IF syntax doesn't apply to Oracle SQL.
You can use IF with ORACLE PL/SQL, but then the syntax would be different (ELSIF instead of ELSE IF, and END IF instead of END)
CASE can be use in SQL or PL/SQL
You can use IF with ORACLE PL/SQL, but then the syntax would be different (ELSIF instead of ELSE IF, and END IF instead of END)
CASE can be use in SQL or PL/SQL
I am not working in the database but rather the SAP universe development tool. There is no IF available just the CASE.
mlmcc
mlmcc
In most Oracle queries in most systems, the "where" clause has a much bigger impact on performance than calculations done on each row retrieved.
True, if a large number of records are retrieved, the calculations done on each record will have at least a small impact on performance. In your case, if you know, or expect that the "91+" values will be more numerous than the "<31" values, you may want to check for the larger values first. But, if more records are expected to be in the "<31" range, you should keep your CASE statement in the order you have it.
True, if a large number of records are retrieved, the calculations done on each record will have at least a small impact on performance. In your case, if you know, or expect that the "91+" values will be more numerous than the "<31" values, you may want to check for the larger values first. But, if more records are expected to be in the "<31" range, you should keep your CASE statement in the order you have it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database
From novice to tech pro — start learning today.
-
Business CommunicationCertification: PMI RMP Project Management Institute Risk Management… 113 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - PowerPoint 2007… 229 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.