The 22 September quiz asked you to analyze a piece of code and decide:
Which of the choices describe a change I can make to the plch_emp_loop procedure that will not affect the external behavior of the procedure (what it displays or which rows it updates), but could improve its performance?
The quiz was intended to be about your ability to identify
loop invariants, expressions within the body of the loop whose outcomes do not change with each execution of the loop. A standard optimization step is to replace the invariants with a variable (or better yet a constant!) that is not re-evaluated with each iteration of the loop.
Several players wrote with concerns about the way this question was worded and scored:
1. Hi Steven, I usually do not complain about quizzes, but I felt that yesterdays quiz was a little bit vague and not up to par or standards of the plsql challenge. First thing is how the question is formed. I don't like vague words like could to be in a quiz. The should always be will, specially when it comes to performance enhancements. But looking at the question itself. "Which of the choices describe a change I can make to the plch_emp_loop procedure that will not affect the external behavior of the procedure (what it displays or which rows it updates), but could improve its performance?". What if all the data in the table have salaries less that the minimum. Then this procedure never runs and the enhancements have no effect at all anyways. 2 answer came out wrong for me, probably because I emphasized too much on the performance part of the question rather that reading the code itself. Therefore I fell into the SYSDATE trap, marking it as correct. It does improve performance, there is no doubt of that, but it of course is a changing variable and therefore will change the internal running of the program. The other one was the number 8123 "l_year NUMBER := TO_CHAR (date_in, 'YYYY');" This will make the IF statement later an implicit conversion (CHAR => NUMBER), that will not neccessarily run faster and therefore I don't think it could be a correct answer.
2. How much performance gain do you expect to get from changing from the explicit conversion TO_CHAR (date_in, 'YYYY') to the implicit conversion of l_year? I tried it, and I didn't get any significant gain.
3. Answer 3 and 4 of the 22 sept quiz contradict! On 31-12-2010 to 01-01-2011 the year changes. PS answer 3 makes an assumption about what date should be used and that changing de date is the best programming solution.
4. Option 3 was scored as incorrect: " The question did not include any constraints on when and for how long this program runs. If during the execution of the procedure, the hour changes, then we cannot safely extract the TO_CHAR (SYSDATE, 'HH24') expression and evaluate it just once at the start of the procedure." This statement is correct but I think this is not a very good example. It is a correct that the hour can change during the running of the program, but do you want to have just a part of your employees updated during a run of the program; I don't think so!
5. An answer in quiz 5142 states "The question did not include any constraints on when and for how long this program runs", but the quiz question says "never takes more than 4 hours to complete" - so I would have thought it would be safe to capture l_hour - although I guess it depends on when the program was launched. I was also curious about the affect on performance on the function call to plch_config.min_salary, since it is just an assignment, no conversion required - performance gain almost nominal?
I will offer some responses here, but then open it up for discussion from players.
- I used the word "could" because I wanted players to focus on a logical analysis of the code (what in theory could improve performance? and not so much on analyzing the actual gains of specific changes, which as some noted could be very minimal indeed).
- Excellent point about the change to l_year resulting in an implicit conversion, when previously there was no conversion. I did not catch that and I believe it makes that choice ambiguous, not clearly a net performance gain when the loop invariant is extracted. I think I will probably need to issue a correction for that choice.
- The answer for choice 3 was incorrect and has been changed to remove the leading sentence. I added time constraint text to the question, but forgot to remove this from the answer.
- I do not see a contradiction between answers 3 and 4 (8122 and 8123 to use their IDs).The year does not change while the program executes, based on " it is only executed during the first month of each quarter (January, April, July and October) and never takes more than 4 hours to complete".
Other comments and points of analysis?
