Re: Oracle For Fun

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1075682270.753428_at_yasure...
>
> As promised I have performed the testing:
>
> The contestants and results can be found at:
> http://www.psoug.org/sql_fun.html
>
> One lesson comes through very clearly from the results ... test, test,
> test. Don't be too quick to think you know what's best just by looking
> at the code you've written.
>
> And never was that more obvious than with this example.

<sour grapes>
I claim goalpost moving, we started with create a function in less than 8 lines of code, and now you gratuitously decide it has to perform well as well :(
</sour grapes>

I'd add at least one thing to your conclusions.

Sometimes you have to consider readability and maintainability of code as a trade off to performance.

It looks to me as though the 'traditional' approach might well perform best even if you add further optional tests, different locales etc, but that the worst performing solutions may still be readable and understandable. For example you could extend the regexp solutions to multiple locales by having a 2 column lookup table with valid regexps for different locales. This would still be pretty readable, but I suspect the 'traditional' approach would start to look horrid. Errm the performance of my solution is still utter cr*p though...

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK