Ok, now done some better testing and the method with CASE WHEN is indeed, as
expected a bit faster, I would say about a third. I have only tested this
with some 8 different convert values, so maybe it will be different if there
are much more different values to convert.

RBS


-----Original Message-----
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 18 March 2007 17:17
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Question about speed of CASE WHEN

Done some testing now and surprisingly, it seems the lookup method with a
join to a lookup table is very slightly faster than the CASE WHEN
construction. There isn't much in it though.

Also tried the shorter CASE WHEN construction, like this:

> case ENTRY_TYPE
>       when  9 then 'Issue
>       when  2 then 'Note'
Etc

But didn't get that to work as it always produced the first WHEN option.


RBS


-----Original Message-----
From: Christian Smith [mailto:[EMAIL PROTECTED] 
Sent: 18 March 2007 15:29
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Question about speed of CASE WHEN

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]
----------------------------------------------------------------------------
-




----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to