sqlite3 performance problems only in python

[Christian Heimes]

Stef Mientki schrieb:
> hello,
> 
> until now I used only small / simple databases in Python with sqlite3.
> Now I've a large and rather complex database.
> 
> The most simple query (with just a result of 100 rows),
> takes about 70 seconds.
> And all that time is consumed in "cursor.fetchall"
> 
> Using the same database in Delphi,
> using the same query,
> takes less than 5 seconds (including displaying the full table in a grid).

Side note:

Since you are coming from Delphi you are probably glad to hear that
Firebird (formally known as Borland InterBase) is well supported. The
kinterbasdb DBA provides a DBA 2.0 compatible interface plus lots of
additional Firebird specific features. You might be better of using
Firebird if you need large and complex databases.

With Firebird you can then create a lower case index:

  CREATE INDEX idx_lower_name ON table COMPUTED BY (LOWER(name));

and use it to query the table for lower case names with:

  SELECT ... FROM table t WHERE LOWER(t.name) STARTING WITH LOWER(:query)

or maybe:

  SELECT ... FROM table t WHERE t.name STARTING WITH LOWER(:query) PLAN
JOIN (t INDEX(idx_lower_name));

Firebird 2.1 supports even more useful features to join on columns
without falling back to a full table scan like UNICODE_CI.

Christian