Re: [sqlite] Why is this so much more efficient?

Aliases in SQL are not the same as variables in most procedural languages. So every time you mention 'totaltime' SQLite is probably recalculating that value by adding all the columns together. See the various discussions regarding no deterministic (random) functions last year. Less references

Re: [sqlite] Why is this so much more efficient?

You can actually index functions or expression: https://www.sqlite.org/expridx.html On Thu, Feb 16, 2017 at 9:32 PM, Cecil Westerhof wrote: > 2017-02-16 21:10 GMT+01:00 Dominique Pellé : > > > Cecil Westerhof wrote: > >

Re: [sqlite] Why is this so much more efficient?

2017-02-16 21:10 GMT+01:00 Dominique Pellé : > Cecil Westerhof wrote: > > > I have a table vmstat that I use to store vmstat info. ;-) > > At the moment it has more as 661 thousand records. > > > > In principle the values of usertime,

Re: [sqlite] Why is this so much more efficient?

Cecil Westerhof wrote: > I have a table vmstat that I use to store vmstat info. ;-) > At the moment it has more as 661 thousand records. > > In principle the values of usertime, systemtime, idletime, waittime and > stolentime should add up to 100. I just wanted to check

Re: [sqlite] Why is this so much more efficient?

Basically, the DB size and compactness state influences the speed at which values are read. (Every update implies a read). Add to that the fact that these functions verge on the bottom edge of time consumers... It's like testing diffusion speed of a perfume in a hurricane. Glad you found

Re: [sqlite] Why is this so much more efficient?

2017-02-15 14:18 GMT+01:00 Cecil Westerhof : > The OR version is the least efficient and it look likes the BETWEEN > version 2 is the most efficient. It looks like it uses less user and more > sys. > ​Which is the most efficient is also dependent on the state of the

Re: [sqlite] Why is this so much more efficient?

2017-02-15 13:40 GMT+01:00 Cecil Westerhof : > I wrote the following Bash script: > ​I wrote a better one. See end of post. Inprinciple you are only interested in the totals. I also changed the runs from 10 to 25. The OR version is the least efficient and it look likes

Re: [sqlite] Why is this so much more efficient?

2017-02-15 12:02 GMT+01:00 R Smith : > Note however that this may not be entirely true. The Query might read data > from the disk cache (or several memory caches may be in play) during the > second run. Run each statement many times, and compare average return times. > > Also

Re: [sqlite] Why is this so much more efficient?

2017-02-15 12:02 GMT+01:00 R Smith : > > On 2017/02/15 12:33 PM, Cecil Westerhof wrote: > >> I have a table vmstat that I use to store vmstat info. ;-) >> At the moment it has more as 661 thousand records. >> >> In principle the values of usertime, systemtime, idletime,

Re: [sqlite] Why is this so much more efficient?

On 2017/02/15 12:33 PM, Cecil Westerhof wrote: I have a table vmstat that I use to store vmstat info. ;-) At the moment it has more as 661 thousand records. In principle the values of usertime, systemtime, idletime, waittime and stolentime should add up to 100. I just wanted to check it.