Performance increase between 3.7 and 3.8

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance increase between 3.7 and 3.8

Jan Slodicka
I know the reports about huge performance increase achieved within the last year. (Compliments for that.) However, those numbers ignore processor architecture and I/O. My question is a different one.

What speed difference do you perceive in real-world applications?

I know that there can't be any answer valid for everybody and for every situation...

Some of my results (I compared v3.7.15.2 vs v3.8.8.1, W7, 2.4 GHz i5, RAM 4G):

TestA
Stress tests that executed a few thousands random SQL commands (small tables, small commands, transactions were used to group write operations):
The differences were within statistical fluctuations

TestB (used SQLite shell)
I dumped real production database and modified the dump file by adding a commit after each table. Then I measured the time needed to re-create the database from the dump file.
v3.8 was faster by some 10-15% (WAL mode)

TestC
Same test as above, but this time one table column was filled with large strings. (emails)
There was no speed difference between v3.8 and v3.7.

What about your experience? Do you perceive better performance due to the last SQLite updates?

Reply | Threaded
Open this post in threaded view
|

Re: Performance increase between 3.7 and 3.8

Richard Hipp-3
On 2/2/15, Jan Slodicka <[hidden email]> wrote:

> I know the reports about huge performance increase achieved within the last
> year. (Compliments for that.) However, those numbers ignore processor
> architecture and I/O. My question is a different one.
>
> What speed difference do you perceive in real-world applications?
>
> I know that there can't be any answer valid for everybody and for every
> situation...
>
> Some of my results (I compared v3.7.15.2 vs v3.8.8.1, W7, 2.4 GHz i5, RAM
> 4G):

From the description, your tests sound like you are pushing a bunch of
separate SQL statements into SQLite.  In other words, the compute time
is likely dominated by the time need to parse the SQL and prepare
plans.   Have you rerun the tests using prepared statements, where you
do a limited number of sqlite3_prepare_v2() calls and then reuse each
prepared statement multiple times with different bound parameters?
That's the kind of work-load we have been striving to optimize, since
we figure anybody who is interested in performance is already making
heavy use of prepared statements.

>
> TestA
> Stress tests that executed a few thousands random SQL commands (small
> tables, small commands, transactions were used to group write operations):
> The differences were within statistical fluctuations
>
> TestB (used SQLite shell)
> I dumped real production database and modified the dump file by adding a
> commit after each table. Then I measured the time needed to re-create the
> database from the dump file.
> v3.8 was faster by some 10-15% (WAL mode)
>
> TestC
> Same test as above, but this time one table column was filled with large
> strings. (emails)
> There was no speed difference between v3.8 and v3.7.
>
> What about your experience? Do you perceive better performance due to the
> last SQLite updates?
>
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Performance-increase-between-3-7-and-3-8-tp80355.html
> Sent from the SQLite mailing list archive at Nabble.com.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance increase between 3.7 and 3.8

Eduardo Morras-2
In reply to this post by Jan Slodicka
On Mon, 2 Feb 2015 10:08:36 -0700 (MST)
Jan Slodicka <[hidden email]> wrote:

> I know the reports about huge performance increase achieved within
> the last year. (Compliments for that.) However, those numbers ignore
> processor architecture and I/O. My question is a different one.
>
> What speed difference do you perceive in real-world applications?
>
> I know that there can't be any answer valid for everybody and for
> every situation...
>
> What about your experience? Do you perceive better performance due to
> the last SQLite updates?

I use a big test db to assure new versions of sqlite works properly. With 3.7.15.2 it takes 43 minutes, with 3.8.8.2 on same hardware 27 minutes, it's 16/0.43 = 37% less or 27/0.43 = 63% improve.

---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance increase between 3.7 and 3.8

Jan Slodicka
In reply to this post by Richard Hipp-3
Richard Hipp-3 wrote
From the description, your tests sound like you are pushing a bunch of
separate SQL statements into SQLite.  In other words, the compute time
is likely dominated by the time need to parse the SQL and prepare
plans.
Yes

  Have you rerun the tests using prepared statements, where you
do a limited number of sqlite3_prepare_v2() calls and then reuse each
prepared statement multiple times with different bound parameters?
That's the kind of work-load we have been striving to optimize, since
we figure anybody who is interested in performance is already making
heavy use of prepared statements.
We call SQLite from C# and use a .net wrapper inspired by system.data.sqlite package. In other words there is a lot of overhead in high level layer. (Moreover, we rely on Mono implementation, which is even slower.) In the old days I run a lot of tests trying to find out the fastest way until we decided for the current algorithm. Since then we heavily optimized the C# layer and you now optimized the C code.

Maybe the original decision is not true anymore. If I find spare time I'll run the tests again. But right now I am primarily interested whether it makes sense to upgrade SQLite code.

Reply | Threaded
Open this post in threaded view
|

Re: Performance increase between 3.7 and 3.8

Jan Slodicka
In reply to this post by Eduardo Morras-2
Eduardo Morras-2 wrote
I use a big test db to assure new versions of sqlite works properly. With 3.7.15.2 it takes 43 minutes, with 3.8.8.2 on same hardware 27 minutes, it's 16/0.43 = 37% less or 27/0.43 = 63% improve.
Thanks, Eduardo. Could I ask you for a rough characterization of the test performed?
Reply | Threaded
Open this post in threaded view
|

Re: Performance increase between 3.7 and 3.8

Eduardo Morras-2
On Tue, 3 Feb 2015 02:43:00 -0700 (MST)
Jan Slodicka <[hidden email]> wrote:

> Eduardo Morras-2 wrote
> > I use a big test db to assure new versions of sqlite works
> > properly. With 3.7.15.2 it takes 43 minutes, with 3.8.8.2 on same
> > hardware 27 minutes, it's 16/0.43 = 37% less or 27/0.43 = 63%
> > improve.
>
> Thanks, Eduardo. Could I ask you for a rough characterization of the
> test performed?

A ~8000MB db with app example data. More than 1000 query-corp created as part of test driven development of the app. We have precalculated the correct results in tables and its number of rows. No write, only read queries and don't use other improvements like partial indexes. Queries run in sequential order at a time.

Some queries test own sql functions and a virtual table, no improvements expected on them.

The test was done with same application version in debug mode and recompiled with 3.7.15.2 on same computer. All tests passed.

---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance increase between 3.7 and 3.8

Jan Slodicka
Eduardo Morras-2 wrote
A ~8000MB db with app example data. More than 1000 query-corp created as part of test driven development of the app. We have precalculated the correct results in tables and its number of rows. No write, only read queries and don't use other improvements like partial indexes. Queries run in sequential order at a time.
That means that (complex) queries are substantially faster in v3.8. That's important for me, too.

Thanks.
Reply | Threaded
Open this post in threaded view
|

Re: Performance increase between 3.7 and 3.8

Eduardo Morras-2
On Tue, 3 Feb 2015 06:39:02 -0700 (MST)
Jan Slodicka <[hidden email]> wrote:

> Eduardo Morras-2 wrote
> > A ~8000MB db with app example data. More than 1000 query-corp
> > created as part of test driven development of the app. We have
> > precalculated the correct results in tables and its number of rows.
> > No write, only read queries and don't use other improvements like
> > partial indexes. Queries run in sequential order at a time.
>
> That means that (complex) queries are substantially faster in v3.8.
> That's important for me, too.

Yes, the 'next generation query planner' helps a lot in queries with more than 4 tables, temp or normal tables.

>
> Thanks.


---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users