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

2017-02-16 Thread Barry Smith
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 t

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

2017-02-16 Thread Darko Volaric
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: > > > > > I have a table vmstat that I use to store vmstat info. ;-) > > > At t

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

2017-02-16 Thread Cecil Westerhof
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, systemtime, idletime, waittime and > > stolentime should add u

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

2017-02-16 Thread 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, systemtime, idletime, waittime and > stolentime should add up to 100. I just wanted to check it. Of-course there > coul

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

2017-02-16 Thread R Smith
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 the

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

2017-02-15 Thread Cecil Westerhof
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 database itself. I compacted t

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

2017-02-15 Thread Cecil Westerhof
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 the BETWEEN version 2 is

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

2017-02-15 Thread Cecil Westerhof
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 try this: > ... WHERE

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

2017-02-15 Thread Cecil Westerhof
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, waittime and >> stolentime

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

2017-02-15 Thread 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, waittime and stolentime should add up to 100. I just wanted to check it. Of-cou

[sqlite] Why is this so much more efficient?

2017-02-15 Thread Cecil Westerhof
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. Of-course there could be a rounding error, so I wrote th