Re: [sqlite] 50% faster than 3.7.17

On Thu, 2014-10-09 at 17:41 +0700, Dan Kennedy wrote: > I think I understand the second optimization. You're saying that given this: > >SELECT DISTINCT main.uid > FROM main LEFT JOIN email_list ON main.uid = email_list.uid > WHERE email_list.email LIKE 'al%' > > SQLite

Re: [sqlite] 50% faster than 3.7.17

On 10/09/2014 04:38 PM, David Woodhouse wrote: On Thu, 2014-09-25 at 11:13 +0100, David Woodhouse wrote: I suggested a couple of specific optimisations which the query planner might be able to make, which should hopefully have benefits wider than just my own use case. Are those not viable? I'm

Re: [sqlite] 50% faster than 3.7.17

On Thu, 2014-09-25 at 11:13 +0100, David Woodhouse wrote: > > I suggested a couple of specific optimisations which the query planner > might be able to make, which should hopefully have benefits wider than > just my own use case. Are those not viable? I'm preparing to commit a workaround to

Re: [sqlite] 50% faster than 3.7.17

"Simon Slavin" wrote... On 24 Sep 2014, at 2:13pm, jose isaias cabrera wrote: This would be a nice set of options. On my case, I would set all connections to our project to be" max_performance", as it is what we need. Just thinking out loud. How much max is

Re: [sqlite] 50% faster than 3.7.17

On Tue, 2014-09-23 at 17:48 +0100, David Woodhouse wrote: > That looks really promising; thanks for all this work. > > Tristan, you have a comprehensive set of benchmarks for Evolution's > addressbook; is it possible for someone else to run those or would it > take more of your time to babysit

Re: [sqlite] 50% faster than 3.7.17

On Wed, 2014-09-24 at 19:36 -0600, Keith Medcalf wrote: > > Interesting. From that code you might want to try something like this: > > SELECT uid, vcard, bdata > FROM folder_id > WHERE uid in ( select uid FROM email_list where value like 'p%' >union > select

Re: [sqlite] 50% faster than 3.7.17

In the vein of configurability, and in a day dream I just had, it would be nice (But probably not possible as there could be compiler directives you can't use at the same time) that we could have a single output DLL/SO/whatever dumped from the compiler that had everything available, then, via

Re: [sqlite] 50% faster than 3.7.17

ad.org] >Sent: Wednesday, 24 September, 2014 07:25 >To: Keith Medcalf >Cc: General Discussion of SQLite Database >Subject: Re: [sqlite] 50% faster than 3.7.17 > >On Wed, 2014-09-24 at 06:13 -0600, Keith Medcalf wrote: >> >> Would it not be more efficient to skip the j

Re: [sqlite] 50% faster than 3.7.17

On 24/09/14 06:19, Simon Slavin wrote: > How much max is max ? Not giving up ACID. But for example stat4 is better than the default stat1. Memory mapping (especially on 64 bit) is great. So is WAL. All are off by default. If you want to give up ACID then you should really be on your own to

Re: [sqlite] 50% faster than 3.7.17

On Wed, 2014-09-24 at 06:13 -0600, Keith Medcalf wrote: > > Would it not be more efficient to skip the join altogether since all > you want is the list of uid's, and assuming that you have maintained > the referential integrity of your database mail_list(list_uid) > references main(uid)? > >

Re: [sqlite] 50% faster than 3.7.17

On 24 Sep 2014, at 2:13pm, jose isaias cabrera wrote: > This would be a nice set of options. On my case, I would set all connections > to our project to be" max_performance", as it is what we need. Just thinking > out loud. How much max is max ? Are you willing to

Re: [sqlite] 50% faster than 3.7.17

"Roger Binns" wrote... On 22/09/14 10:48, Richard Hipp wrote: But if you have any new ideas on how we can further reduce the I/O, we'd love to hear from you. The single biggest problem for me is defaults. SQLite supports memory mapped i/o which has many advantages. The stat4 analyze does

Re: [sqlite] 50% faster than 3.7.17

Would it not be more efficient to skip the join altogether since all you want is the list of uid's, and assuming that you have maintained the referential integrity of your database mail_list(list_uid) references main(uid)? SELECT list_uid FROM mail_list WHERE email LIKE 'al%' UNION SELECT

Re: [sqlite] 50% faster than 3.7.17

On Fri, 2014-09-19 at 21:14 -0400, Richard Hipp wrote: > The 50% faster number above is not about better query plans. Speaking of better query plans, though... here's a query which takes about 1700ms on my data set, followed by a couple of optimisations which seem like they might be generically

Re: [sqlite] 50% faster than 3.7.17

On 22/09/14 10:48, Richard Hipp wrote: > But if you have any new ideas on how we can further reduce the I/O, we'd love > to hear from you. The single biggest problem for me is defaults. SQLite supports memory mapped i/o which has many advantages. The stat4 analyze does a really good job. WAL

Re: [sqlite] 50% faster than 3.7.17

On Fri, 2014-09-19 at 21:14 -0400, Richard Hipp wrote: > The latest SQLite 3.8.7 alpha version (available on the download page > http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 release > from 16 months ago. That is to say, it does 50% more work using the same > number of CPU

Re: [sqlite] 50% faster than 3.7.17

On Tue, Sep 23, 2014 at 2:33 AM, Donald Shepherd wrote: > Are any of these improvements specifically in the area of the online backup > API, or are they more in the general running of SQLite? > The "speedtest1" benchmark does not use the backup API. However, many of

Re: [sqlite] 50% faster than 3.7.17

Are any of these improvements specifically in the area of the online backup API, or are they more in the general running of SQLite? On 20 September 2014 11:14, Richard Hipp wrote: > The latest SQLite 3.8.7 alpha version (available on the download page >

Re: [sqlite] 50% faster than 3.7.17

ok, Nearly all the time is spent in a big 'CTE' select. So maybe this sort of ugly CTE query is not threadable. with f0_k as (SELECT f.rowid as nof2, f.*, p.Dn, p.g1, p.g2, p.w, p.other FROM F0 AS f, Pt AS p WHERE f.Io =p.Io) ,F0_mcalc as (SELECT f.*, p.*, (case when Priority=999 then Cg

Re: [sqlite] 50% faster than 3.7.17

On Mon, Sep 22, 2014 at 8:29 AM, Valentin Davydov wrote: > On Fri, Sep 19, 2014 at 09:14:17PM -0400, Richard Hipp wrote: > > > The latest SQLite 3.8.7 alpha version (available on the download page > > http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 >

Re: [sqlite] 50% faster than 3.7.17

On Mon, Sep 22, 2014 at 1:43 PM, big stone wrote: > Hi, > > This 3.8.7alpha release seems to bring about 5% win from 3.8.6 , on my > particular SQL test case. > > Question : "PRAGMA threads=2" didn't bring any speed-up on my "2 true" > cores machine. > > Did I miss a

Re: [sqlite] 50% faster than 3.7.17

Hi, This 3.8.7alpha release seems to bring about 5% win from 3.8.6 , on my particular SQL test case. Question : "PRAGMA threads=2" didn't bring any speed-up on my "2 true" cores machine. Did I miss a compilation option ? ___ sqlite-users mailing list

Re: [sqlite] 50% faster than 3.7.17

On Fri, Sep 19, 2014 at 09:14:17PM -0400, Richard Hipp wrote: > The latest SQLite 3.8.7 alpha version (available on the download page > http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 release > from 16 months ago. That is to say, it does 50% more work using the same > number

Re: [sqlite] 50% faster than 3.7.17

On Sat, Sep 20, 2014 at 1:34 PM, wrote: > In trying to see if the new version breaks any of my queries, I ran > several of them repeatedly, and they all appear to have produced the > expected output. > > The only thing I noticed which maybe of interest in relation to speed >

Re: [sqlite] 50% faster than 3.7.17

In trying to see if the new version breaks any of my queries, I ran several of them repeatedly, and they all appear to have produced the expected output. The only thing I noticed which maybe of interest in relation to speed performance was (with .timer on) that although the first two run time

Re: [sqlite] 50% faster than 3.7.17

I, as well, wish to thank you for this tool Dr. Hipp. I've never published a public application using this engine, but, at my place of employment, where my primary responsibility is to just monitor servers world wide, I've coded a few tidbit web and desktop applications that have made my job SO

Re: [sqlite] 50% faster than 3.7.17

"Richard Hipp" wrote... The latest SQLite 3.8.7 alpha version (available on the download page http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 release from 16 months ago. That is to say, it does 50% more work using the same number of CPU cycles. This performance gain is

[sqlite] 50% faster than 3.7.17

The latest SQLite 3.8.7 alpha version (available on the download page http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 release from 16 months ago. That is to say, it does 50% more work using the same number of CPU cycles. This performance gain is over and above the query