RE: [sqlite] Decimal conversion
Thanks Dennis, I will give that a shot. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Monday, January 23, 2006 11:20 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Decimal conversion nbiggs wrote: >I was using version 3.2.1. I downloaded 3.3.1 and your suggestion >worked, but it is very slow compared to 3.2.1. I use the original query >to test the speed. 3.2.1 returned the data in a little over 10 seconds >while 3.3.1 took a minute. Why is that? > > > I don't know. There were some performance issues reported with version 3.3.1. I believe that version 3.3.2, which should be released shortly, will address these issues, but I don't think any of them were causing this much of a slowdown. I though they were on the order of 10% slower, not 500%. I wouldn't have expected the conversion calculation to take very long. On the other hand, the round function does the same type of calculation internally, but it is implemented in C rather than as SQLite VDBE opcodes. And, by looking at the explain output, I can see that SQLIte is actually executing the calculation (both the round, or the cast) twice (once for each input row to build a temp table to sort, and once as each temp table row is scanned to do he group by). That means that the performance improvement of the round function is doubled. Your best bet may be to create a user defined function to truncate a number. This would perform the same calculation as the cast expression, but be implemented in C. It should execute even faster than the round function (Since it wouldn't need to do the conversion to a string that round does. The output does need to be converted to a string once to be displayed, but that is only done once for each output row in the histogram, not twice for each row in the table). HTH Dennis Cote
Re: [sqlite] Decimal conversion
nbiggs wrote: I was using version 3.2.1. I downloaded 3.3.1 and your suggestion worked, but it is very slow compared to 3.2.1. I use the original query to test the speed. 3.2.1 returned the data in a little over 10 seconds while 3.3.1 took a minute. Why is that? I don't know. There were some performance issues reported with version 3.3.1. I believe that version 3.3.2, which should be released shortly, will address these issues, but I don't think any of them were causing this much of a slowdown. I though they were on the order of 10% slower, not 500%. I wouldn't have expected the conversion calculation to take very long. On the other hand, the round function does the same type of calculation internally, but it is implemented in C rather than as SQLite VDBE opcodes. And, by looking at the explain output, I can see that SQLIte is actually executing the calculation (both the round, or the cast) twice (once for each input row to build a temp table to sort, and once as each temp table row is scanned to do he group by). That means that the performance improvement of the round function is doubled. Your best bet may be to create a user defined function to truncate a number. This would perform the same calculation as the cast expression, but be implemented in C. It should execute even faster than the round function (Since it wouldn't need to do the conversion to a string that round does. The output does need to be converted to a string once to be displayed, but that is only done once for each output row in the histogram, not twice for each row in the table). HTH Dennis Cote
RE: [sqlite] Decimal conversion
I was using version 3.2.1. I downloaded 3.3.1 and your suggestion worked, but it is very slow compared to 3.2.1. I use the original query to test the speed. 3.2.1 returned the data in a little over 10 seconds while 3.3.1 took a minute. Why is that? I used .dump to create .sql files and then loaded 3.3.1 from the .sql files. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 2:31 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Decimal conversion nbiggs wrote: >When I try executing the command I get 'SQL error: near "as": syntax >error'. > >Let me explain what I am trying to do, there might be a better way to do >it. I have a table of weights as one of the columns. I am trying to >return data so that I can create a histogram of the data. My query is >as follows: > >Select round(field, 1), count(*) >from table >group by round(field, 1); > > > > Nathan, What version of SQLite are you using? The cast syntax was added fairly recently. I'm using version 3.2.7 to test. With a current version of SQLite this should work. select cast (field * 10 as integer) / 10.0 as bin, count(*) from table group by bin; Note, you wont get a result row for any bin values that would have had a count of zero. I.e. if there are no rows with a value of 49.8?? then there will not be a result row in the histogram for that value with a count of zero. The histogram output only has rows where the count was 1 or more. This may or may not be what you want. HTH Dennis Cote
Re: [sqlite] Decimal conversion
nbiggs wrote: When I try executing the command I get 'SQL error: near "as": syntax error'. Let me explain what I am trying to do, there might be a better way to do it. I have a table of weights as one of the columns. I am trying to return data so that I can create a histogram of the data. My query is as follows: Select round(field, 1), count(*) from table group by round(field, 1); Nathan, What version of SQLite are you using? The cast syntax was added fairly recently. I'm using version 3.2.7 to test. With a current version of SQLite this should work. select cast (field * 10 as integer) / 10.0 as bin, count(*) from table group by bin; Note, you wont get a result row for any bin values that would have had a count of zero. I.e. if there are no rows with a value of 49.8?? then there will not be a result row in the histogram for that value with a count of zero. The histogram output only has rows where the count was 1 or more. This may or may not be what you want. HTH Dennis Cote
RE: [sqlite] Decimal conversion
When I try executing the command I get 'SQL error: near "as": syntax error'. Let me explain what I am trying to do, there might be a better way to do it. I have a table of weights as one of the columns. I am trying to return data so that I can create a histogram of the data. My query is as follows: Select round(field, 1), count(*) from table group by round(field, 1); -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 1:31 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Decimal conversion nbiggs wrote: >How do I convert the number 49.991 to just 49.9 in a select statement? >Using the round(weight, 1) returns 50.0. > >Is there a truncate function? > > Nathan, You can use: select cast ((field * 10) as integer) / 10.0 If this is something you do a lot of it might make sense to define a custom function. HTH Dennis Cote
Re: [sqlite] Decimal conversion
nbiggs wrote: How do I convert the number 49.991 to just 49.9 in a select statement? Using the round(weight, 1) returns 50.0. Is there a truncate function? Nathan, You can use: select cast ((field * 10) as integer) / 10.0 If this is something you do a lot of it might make sense to define a custom function. HTH Dennis Cote
