System.Data.SQLite

Login
Ticket Hash: f9010bdcf91104d51da3ec7e6175da3bf5a16b4e
Title: Query execution takes longer time with System.Data.Sqlite1.0.113.0 and the same executes faster with 1.0.66.0 version dll
Status: Deferred Type: Performance
Severity: Critical Priority: Medium
Subsystem: Native_Assembly Resolution: Need_More_Info
Last Modified: 2020-10-29 02:33:27
Version Found In: 1.0.113.0
User Comments:
anonymous added on 2020-06-16 05:27:22:

Query execution takes longer time(10sec) with System.Data.Sqlite 1.0.113.0 and the same executes faster(1sec) with System.Data.Sqlite 1.0.66.0 version dll, but my customer is willing to use the latest dll as there is no support for old version.

Query:SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Id = Table2.Id WHERE 1=1 AND (Table2.value1=1 AND Table2.value2=1) ORDER BY DateTime DESC, Number DESC LIMIT 50 OFFSET 117050 ;

Kindly provide the solution.


anonymous added on 2020-06-16 05:35:16:

We have already made use of all the optimization techniques available in this blog. https://blog.devart.com/increasing-sqlite-performance.html


mistachkin added on 2020-09-24 22:27:29:
It will be very difficult to progress on this ticket without the schema of
the database in question and perhaps some example C# code that demonstrates
the issue.

Some related information that could be useful:

1. How large is the database and what page size is it using?

2. What is the output of "EXPLAIN QUERY PLAN <query>" when using the old
and new versions of System.Data.SQLite?

mistachkin added on 2020-10-29 02:33:27:
There is not enough information to proceed on this ticket.  It will not block
the next release.