DataTable loading is very slow

Question

I need to get some data based on a keyword, the request is verified to be 100% accurate, but the problem is that the reader loading is rather slow. I tried replacing this request with one that does not contain inner join , and the download was pretty fast. Therefore, I am surprised since I select only one column, why does DataTable.Load () take so long? Is SQLite ExecuteReader loading all results, not just a single column?

Prior to using the DataTable, the average runtime of each reader.Read() was 7 seconds.

This is my code:

 _database.Connect(); var selectCommand = new SQLiteCommand( @"SELECT A.ID AS MY_ID FROM MD INNER JOIN TMD ON MD.ID = TMD.ID_MD INNER JOIN TR ON TR.ID = TMD.ID_TR INNER JOIN P ON P.ID = TR.ID_P INNER JOIN DP ON DP.ID_P = P.ID INNER JOIN CD ON CD.ID = DP.ID_CD WHERE CD.DESC = @desc" ); selectCommand.Parameters.AddWithValue("@desc", value); using (DbDataReader reader = _database.ExecuteQuery(selectCommand)) { DataTable data = new DataTable("MyData"); data.Load(reader); } _database.Disconnect(); 

Answer 1

SQLite Query Scheduler offers some tips on query optimization for SQLite.

Some items that may apply to your question:

1.) Due to the implementation in SQLite, you may try to reorder multiple connections:

The current SQLite implementation uses only loop joins. That is, let's say joins are implemented as nested loops. The default order for nested loops in the join for the leftmost table in the FROM clause is to form the outer loop and the rightmost table to form the inner loop.

So, depending on how JOINs are created, there may be a difference in performance.

SQLite is trying to optimize this automatically, but as far as I understand the documentation, there is no guarantee of success (emphasized by me):

However, SQLite will set the loops in a different order, if this is done it will help him choose the best indicators. [...] Registration of reordering occurs automatically and usually works well enough so that programmers do not need to think about it, especially if ANALYZE was used to collect statistics on available indexes. But sometimes some hints from a programmer.

2.) Also note that INNER JOINS are internally converted to WHERE clauses, so any performance recommendations in the WHERE clause of a document can also apply:

The ON and USING clauses of the inner join are converted to additional WHERE clauses before the WHERE clause described in clause 1.0 above is analyzed. Thus, with SQLite, there is no computational advantage of using the new SQL92 join syntax over the older SQL89 comma syntax. They both achieve exactly the same thing on internal connections.

3.) You may consider choosing more columns in your application if they have indexes:

It is not necessary that each index column is displayed in WHERE to use this index. But there cannot be spaces in the columns of the index used.

Answer 2

I think this is due to the nature of SQLite and the large number of joins.

Try creating a refactoring database schema, for example, denormalizing data for faster access.