Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Views / SELECT extremely slow #3580

Open
rkoe opened this issue Sep 27, 2019 · 6 comments

Comments

@rkoe
Copy link

commented Sep 27, 2019

Details

Database-interactions in SQliteStudio are extremely slow, as soon as they are a bit more complex, e.g. views with joins or subqueries.

Test cases:

  • A: Get rows of view "A", accessing with several case-statements and some subqueries of other views. The query of view "A" is ~2400 characters long.
  • B: Get rows of view "B", accessing a table, and view "A" with subqueries. View "B" is:
    SELECT
        a,
        date,
        round(sum1-sum2, 2) as diff,
        b,
        sum1-sum2 as sum,
        sum1,
        sum2
    FROM
    (SELECT
        t.date,
        t.a,
        t.b,
        (SELECT sum(val1) FROM viewA WHERE viewA.c = t.a AND viewA.date <= t.date) as sum2,
        (SELECT sum(val2) FROM viewA WHERE viewA.d = t.a AND viewA.date <= t.date) as sum1
    FROM table1)
    ORDER BY a,date
    

Test results:

  • sqlite3-commandline-tool (SELECT * FROM view):
    • A: <1 second, 581 rows
    • B: ~4 seconds, 60 rows
  • SQLiteStudio: Click on the "update"-button on "Grid view" of the database-view:
    • A: ~50 seconds with 100% CPU, 581 rows
    • B: ~4.75 hours(!) with 100% CPU, 60 rows

Debug-output:

  • A:

    DEBUG:    "SQL mydb> SELECT [...~30000 chars...]
    WARNING:  Could not execute query with smart method: "SELECT [...~2400 chars...]"
    Error message: "no such column: b.split"
    Skipping smart execution.
    DEBUG:    Smart execution failed at step QueryExecutorExecute ""
    Using simple execution method.
    DEBUG:    "SQL mydb> SELECT [...~2400chars...]
    

    Then wait ~50s until done.

  • B:

    DEBUG:    "SQL mydb> SELECT [...~56000 chars...]
    WARNING:  Could not execute query with smart method: "SELECT [...~600 chars...]"
    Error message: "no such column: b.split"
    Skipping smart execution.
    DEBUG:    Smart execution failed at step QueryExecutorExecute ""
    Using simple execution method.
    DEBUG:    "SQL mydb> SELECT [...~600chars...]
    

    Then, the GUI freezes, and CPU-load goes up to 100% for several hours...

It looks like SQLiteStudio is doing something horribly wrong here, to get so horribly slow.

Is there any hope that this might be fixed soon? Since these delays make it quite unusable.

Steps to reproduce

Unfortunately, I cannot give you my database, but since the performance difference is so huge, it should be easy to reproduce / find.

Operating system

Linux

SQLiteStudio version

3.2.1

@pawelsalawa

This comment has been minimized.

Copy link
Owner

commented Sep 27, 2019

Is there a chance you could share the testing database? If you don't want to publish it here, perhaps sending a (dropbox?) link to me over an email would be the way to go?

@rkoe

This comment has been minimized.

Copy link
Author

commented Sep 27, 2019

No, but I might create a stripped-down version of it.

@pawelsalawa

This comment has been minimized.

Copy link
Owner

commented Sep 27, 2019

That would be really helpful. There are two problems in fact. One is that - so called - "smart execution" has some problem understanding the query, so it falls back to "simple execution". That's one thing to be fixed and this one can be fixed even without sample db.

Still, the simple execution should work just fine anyway. It should even be slightly faster. Both ways should be fast, but if any of them were to be slower, I would suspect rather "smart" one. Therefore I'm even more confused on why is this slow...

Having a reproducible test database would help a lot with this one.

@rkoe

This comment has been minimized.

Copy link
Author

commented Sep 27, 2019

Now, I have a simplified, stripped-down version: sqlitestudio-slooow.txt

The results for this version are:

  • sqlite3-commandline-tool (SELECT * FROM viewA / viewB):
    viewA: 0.03 seconds (incl. starting sqlite3, loading the database etc.), 500 rows
    viewB: 0.8 seconds (incl. starting sqlite3, loading the database etc.), 38 rows
  • SQLiteStudio: Click on the "update"-button on "Grid view" of the database-view:
    viewA: ~9 seconds with 100% CPU, 500 rows
    viewB: ~1600 seconds with 100% CPU, 38 rows

Additional notes:

  • The performance of SQLiteStudio seems to heavily depend on the data in the tables!
    Replacing data in colums, which were not used by viewA, with "DUMMY", reduced the run-time for viewA from 30s to 9s.
  • Using a view which UNIONs two tables is slow (see view "combine"). If I remove the UNION with the empty table "table2_empty" in "combine", the runtime for viewA drops from 9s to 1.5s, and for viewB from 1600s to ~100s.
  • When running more complex queries, the SQLiteStudio GUI freezes, so the "Interrupt-button" is not usable anymore. I can only abort it with Ctrl-C in the commandline, which usually results in a Segmentation Fault.
  • SQLiteStudio gives wrong error-messages, e.g. when replacing "txt2" in line 578 of the dump with something else, and then querying viewA, SQLiteStudio incorrectly says:
    no such column: combine2.split
    The sqlite3-commandline-tool gives the right error-message
    Error: no such column: txt2
@pawelsalawa pawelsalawa added the bug label Oct 11, 2019
@pawelsalawa pawelsalawa added this to the 3.2.2 milestone Oct 11, 2019
@rkoe

This comment has been minimized.

Copy link
Author

commented Oct 12, 2019

I think this problem could be solved by:

  • removing the "smart execution"
  • not replacing views in the "simple execution" but simply using views in the way they are intended to

Two questions:

  1. Is there a way to completely disable the "smart execution" in the current version? That would probably help a lot.
    (Since according to my analyis below, the "smart execution" does not seem to be smart at all: It ignores database-features (and tries to implement them on its own), it does not scale at all, it causes wrong error-messages, it converts very simple queries to horribly huge queries...)
  2. When getting data from a view, SELECT * FROM view; (+metadata+LIMIT) would usually be enough. But SQLiteStudio (even with the "simple execution method") instead seems to execute the query which was used to create the view.
    Why?

Details:

I've analyzed another case here:

  • task: simply get data from a view
  • SELECT * FROM myview; would be sufficient (ok, plus a query about the field names)
  • sqlite3 instantly returns the result (<0.05s)
  • SQLiteStudio needs ~8s of 100% CPU

Analyzing the SQLiteStudio debug-logs showed why SQLiteStudio is so horribly slow:
It recursively replaces all views (which are involved in the query) by subqueries, tries to be "smarter" than the database, and fails. What SQLiteStudio does in this case is:

  1. SQLiteStudio asks the database recursively for all SQL-code which is somehow involved
    (-> it asks for all SQL-code which was used to create every view and every table, which were used in myview and recursively down in the views/tables used by them).
    This results here in:

    • 5* asking for the names of all views in the database
    • 8* asking for the SQL-code of 5 different views/tables (4 views/tables were even queried twice)

    and takes about 0.2s.

  2. Then it tries to replace all views recursively by subqueries (WTF?), asks the database again for the names of all views in the database and the SQL of all involved tables.

    This takes about 2s and results in a SQL-SELECT-query of ~116350 characters (WTF?).

  3. It then takes about 4 seconds more; probably for trying to "optimizing" the huge SQL-SELECT-query, resulting in a query of ~117100 characters.

  4. It then fails with a wrong error-message ("no such column: b.split").

  5. Then it (finally) "skips" smart execution and uses the "simple execution method".
    But it needs about 0.7s more before running the "simple" query.

  6. Then it executes the SQL-code which was used to create the view, and returns the results.

So, instead of a simple "SELECT * FROM view", SQLiteStudio tries to build its own query, which is over 117000 characters long, takes >200x as much time, fails, and the runs a ~300 character long query (instead of a 19 character long one). :((

@rkoe

This comment has been minimized.

Copy link
Author

commented Oct 12, 2019

As test, I've now created a simple patch which disables the "smart execution". I've attached the test-patch.

It's much faster now, and I didn't have any segfaults (which were reproducible without the patch).
So, #3581 is probably also caused by the "smart execution".
But beware, with this patch, you probably won't be able to insert/update data in SQLiteStudio.

Patch: sqlitestudio-3.2.1_forcesimplemode.patch.txt

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.