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
