[sqlite] Need advice: Some queries show performance regression

[Jinho Jung]

Hello,

We are developing a tool called sqlfuzz for automatically finding
performance regressions in SQLite. sqlfuzz performs mutational fuzzing to
generate SQL queries that take more time to execute on the latest version
of SQLite compared to prior versions. We hope that these queries would help
further increase the utility of the regression test suite.

We are sharing four SQL queries that exhibit regressions in this report.
Here’s an illustrative query:


EXAMPLE:

select ref_0.NO_O_ID
from
  main.NEW_ORDER as ref_0
where ref_0.NO_W_ID is not NULL
  and (EXISTS (
    select 1
    from (
      select 1
      from main.STOCK as ref_2
        left join main.CUSTOMER as ref_3 on (ref_2.S_YTD = ref_3.C_ID)
      where ref_3.C_ZIP is not NULL) as subq_0
    where (ref_0.NO_W_ID is not NULL)
  )
);

Time taken on SQLite v3.23.0:   73 (milliseconds)
Time taken on SQLite v3.27.2: 4955 (milliseconds)


Here are the steps for reproducing our observations:

[Our test environment]
* Ubuntu 16.04 machine "Linux sludge 4.4.0-116-generic #140-Ubuntu SMP Mon
Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux"
* Database: TPC-C benchmark

[Setup Test Environment]

1. build SQLite 3.27.2 (verion of Feb 2019)
  $ wget https://www.sqlite.org/2019/sqlite-src-3270200.zip
  $ unzip sqlite-src-3270200.zip
  $ mv sqlite-src-3270200 sqlite327
  $ cd sqlite327
  $ ./configure
  $ make
  $ cd ..

2. build SQLite 3.23.0 (verion of Apr 2018)
  $ wget https://www.sqlite.org/2018/sqlite-src-3230000.zip
  $ unzip sqlite-src-3230000.zip
  $ mv sqlite-src-3230000 sqlite323
  $ cd sqlite323
  $ ./configure
  $ make
  $ cd ..

3. download tpc-c for sqlite3 (scale-factor of 1)
  $ mkdir testcase
  $ cd testcase

  $ wget https://gts3.org/~/jjung/sqlite/tpcc_sqlite.tar.gz
  $ tar xzvf tpcc_sqlite.tar.gz

; download regression queries
  $ wget https://gts3.org/~/jjung/sqlite/report1.tar.gz
  $ tar xzvf report1.tar.gz
  $ cd ..

4. launch two SQLites
  - start
    $ sqlite327/sqlite3 testcase/test.db
    $ sqlite323/sqlite3 testcase/test.db

  - for each DB, set up timer
    sqlite> .timer on

 - copy and paste extracted queries


Here’s the time taken to execute four SQL queries on old (v3.23) and newer
version (v3.27.2) of SQLite (in milliseconds). We also try bisecting to
know which commit activate the regression.

+----------------------+--------+
| Query                |   Time |
+----------------------+--------+
| 1.sql (v3.23)        |    148 |
| 1.sql (v3.27.2)      |    314 |
+----------------------+--------+
| 1-1.sql (v3.23)      |      1 |
| 1-1.sql (v3.27.2)    | > 5min |
+----------------------+--------+
| 338.sql (v3.23)      |     73 |
| 338.sql (v3.27.2)    |   4955 |
+----------------------+--------+

1) 1.sql shows x2.12 slow query execution.
 - bisect fossil commit: 4978ee8b54

2) 1-1.sql is slight modification from 1.sql. However, we observed much
slower execution speed.
 - bisect fossil commit: X (we couldn't bisect due to the long execution
time)

3) 338.sql shows x67 show query execution.
 - bisect fossil commit: 4978ee8b54

We would greatly appreciate feedback from the community regarding these
queries and are looking forward to improving the tool based on the
community’s feedback.

Thanks.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users