RB Smissaert uttered:
Looking at the fastest way to convert a field in a table and wonder if in
general an update with a CASE WHEN construction or an update with a join to
a lookup table is faster.
My guess is that the CASE WHEN form will be faster for small number of
possibilities. It compiles into a straight list of compare instructions.
These conversions are of this type:
UPDATE A3SQL77D_J
SET ENTRY_TYPE = (case
when ENTRY_TYPE = 9 then 'Issue
when ENTRY_TYPE = 2 then 'Note'
when ENTRY_TYPE = 1 then 'Encounter'
when ENTRY_TYPE = 8 then 'Authorisation'
when ENTRY_TYPE = 11 then 'Prescription'
when ENTRY_TYPE = 5 then 'Treatment'
when ENTRY_TYPE = 3 then 'Problem'
when ENTRY_TYPE = 13 then 'Discontinuation'
when ENTRY_TYPE = 6 then 'Reminder'
when ENTRY_TYPE = 14 then 'Adverse reaction'
when ENTRY_TYPE = -1 then 'Unknown'
when ENTRY_TYPE = 4 then 'Sub-problem'
when ENTRY_TYPE = 7 then 'Battery'
when ENTRY_TYPE = 10 then 'Return-Script'
else ENTRY_TYPE end)
So, an integer value to be converted to a limited number of strings.
I could figure this out with some testing, but maybe there are some general
rules that apply to this.
Testing is probably the best way. However, the difference in speed may not
be significant given the increased maintainability of the table lookup
based solution.
Also, would the order of the CASE WHEN options make a difference,
speed-wise, so would it be faster to have the WHEN options in decreasing
order of frequency?
The sequence of code generated compares the cases in the order written. So
the common cases should go first.
RBS
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
