Re: [sqlite] Question about speed of CASE WHEN
> Or is it just a case of needing to view > the final data in a user friendly environment? That is it. They can format, sort, print etc. in Excel. They won't even have to know about SQLite. The SQLite database file can safely be deleted and it will still work the same. RBS > Hi RBS, > >> I use SQLite as a data manipulator, not as a database. I get data >> from a >> server database, dump to SQLite, manipulate the data and finally >> dump to >> Excel. As this is reporting software speed is important, so I will >> go with >> the fastest method. > > OK, I have to ask. What do you then do with the data in Excel? I > spend so much of my time with clients converting them from using > spreadsheets (80% of the time when it's more appropriate) to using a > database, that my ears prick up whenever I hear someone doing the > reverse. > > Can you create whatever facilities you're using in Excel, directly in > the SQLite database (eg via CREATE VIEW)? Or is it just a case of > needing to view the final data in a user friendly environment? > > Tom > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about speed of CASE WHEN
Hi RBS, I use SQLite as a data manipulator, not as a database. I get data from a server database, dump to SQLite, manipulate the data and finally dump to Excel. As this is reporting software speed is important, so I will go with the fastest method. OK, I have to ask. What do you then do with the data in Excel? I spend so much of my time with clients converting them from using spreadsheets (80% of the time when it's more appropriate) to using a database, that my ears prick up whenever I hear someone doing the reverse. Can you create whatever facilities you're using in Excel, directly in the SQLite database (eg via CREATE VIEW)? Or is it just a case of needing to view the final data in a user friendly environment? Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
I use SQLite as a data manipulator, not as a database. I get data from a server database, dump to SQLite, manipulate the data and finally dump to Excel. As this is reporting software speed is important, so I will go with the fastest method. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 23:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert wrote: > Ok, now done some better testing and the method with CASE WHEN is indeed, as > expected a bit faster To me the lookup table method seems like exactly what a relational database is used for. The CASE WHEN would have to be dramatically faster, and in an area where timing was critical, for me to choose that way. If there were thousands of items, and changes were frequent, you wouldn't even consider CASE WHEN, would you? Remember: timing isn't important, except when it is. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about speed of CASE WHEN
RB Smissaert wrote: Ok, now done some better testing and the method with CASE WHEN is indeed, as expected a bit faster To me the lookup table method seems like exactly what a relational database is used for. The CASE WHEN would have to be dramatically faster, and in an area where timing was critical, for me to choose that way. If there were thousands of items, and changes were frequent, you wouldn't even consider CASE WHEN, would you? Remember: timing isn't important, except when it is. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
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] -
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] -
RE: [sqlite] Question about speed of CASE WHEN
Sorry, had to rush off and missed your alternative. Will do some testing now. RBS -Original Message- From: T [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 14:55 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN Hi RBS, >> Perhaps the alternative form of the CASE statement would be faster > > What form is that? The form that I showed in my previous email. That is, using: > case ENTRY_TYPE > when 9 then 'Issue > when 2 then 'Note' etc instead of: >> case >> when ENTRY_TYPE = 9 then 'Issue >> when ENTRY_TYPE = 2 then 'Note' etc Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
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] -
Re: [sqlite] Question about speed of CASE WHEN
Hi RBS, Perhaps the alternative form of the CASE statement would be faster What form is that? The form that I showed in my previous email. That is, using: case ENTRY_TYPE when 9 then 'Issue when 2 then 'Note' etc instead of: case when ENTRY_TYPE = 9 then 'Issue when ENTRY_TYPE = 2 then 'Note' etc Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about speed of CASE WHEN
Hi RBS, 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. Perhaps the alternative form of the CASE statement would be faster, but I don't know: case ENTRY_TYPE when 9 then 'Issue when 2 then 'Note' when 1 then 'Encounter' when 8 then 'Authorisation' when 11 then 'Prescription' when 5 then 'Treatment' when 3 then 'Problem' when 13 then 'Discontinuation' when 6 then 'Reminder' when 14 then 'Adverse reaction' when -1 then 'Unknown' when 4 then 'Sub-problem' when 7 then 'Battery' when 10 then 'Return-Script' else ENTRY_TYPE end Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Question about speed of CASE WHEN
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. 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. 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? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
