Hello, We are submitting the third report with two more interesting cases with bisecting result using "SQL-Perf-Fuzzer".
This is the timeline of our activities: [Apr 1, 2019] Submitted 1st report (3 queries, 1 bisected commits) ( http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083863.html ) ==> confirmed the problem with correct bisect result (Apr/1) [Apr 2, 2019] Submitted 2nd report: 2 queries, 2 unique bisected commits ( http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083868.html ) ==> not yet confirmed [Apr 5, 2019] Submitted 3rd report (2 queries, 2 unique bisected commits) ==> this report Here are the steps for reproducing our observations. All steps are same except for the link to download new test-cases: [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/report3.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 | +----------------------+--------+ | 10002.sql (v3.23) | 789 | | 10002.sql (v3.27.2) | 1270 | +----------------------+--------+ | 10052.sql (v3.23) | 3094 | | 10052.sql (v3.27.2) | 4478 | +----------------------+--------+ 1) 10002.sql shows 60% performance regression - bisect fossil commit: === 2018-12-31 === [9fb646f29c] *MERGE* Merge enhancements and bug fixes from trunk. (user: drh tags: reuse- subqueries) 2) 10052.sql shows 40% performance regression - bisect fossil commit: === 2018-12-24 === [7153552bac] Improvements to EXPLAIN QUERY PLAN formatting. The MULTI-INDEX OR now shows a separate "INDEX" subtree for each index. SCALAR SUBQUERY entries provide a subquery number that is related back to the .selecttrace output. (user: drh tags: reuse- subqueries) Thanks for your checking and support. Best regards, Jinho Jung _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
