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