NVL versus IF...THEN [message #38111] |
Thu, 21 March 2002 05:53 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
Hi,
1.I was asked to solve very painful performance issues without changing the code but by simply improving it ...
Can anybody tell me what is faster in sp or it is the same since the Oracle does the same IF...THEN evaluation:
NVL(col, '')
or
IF col IS NUL
THEN
col='';
2.Another question.
Same instance, 3 identical schemas, different run time.
Like in one schema 12 hours, in another 18 hours, in third 30 hours.
3. After the server was bounced, first run took 12 hours, 2nd run in the same instance, the same schema took 26 hours. And this is a pattern in all three schemas.
Any ideas?
Thank you
|
|
|
Re: NVL versus IF...THEN [message #38112 is a reply to message #38111] |
Thu, 21 March 2002 06:07 |
Grant
Messages: 578 Registered: January 2002
|
Senior Member |
|
|
Not sure about NLV but in general it is always better to run an SP cause it runs on the server. There are a few things you can check.
1. Make sure you do not have a locking issue. If the records are locked it will appear the way you have observed.
2. Use estat/bstat or STATSPACK (preferred) to see if you have other performance issues.
3. Then tackle the application performance.
|
|
|
|
Re: NVL versus IF...THEN [message #38118 is a reply to message #38111] |
Thu, 21 March 2002 07:52 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Regarding the first question: If this is indeed the real code, it is not needed (in either format) because there is no difference between a NULL value and an empty string.
|
|
|