DataTable reader loading is very slow

Question

I need to fetch some data based on a keyword, the query is tested to 100% accurate, but the problem is the the loading of the reader is pretty slow. I have tried replacing this query with one that does not contain inner joins at all and the loading was pretty fast. So I wonder, since I am only selecting one column as a result, why does DataTable.Load() take so much time? Is it the SQLite's ExecuteReader that loads the whole results and not just the one column?

Before using the DataTable, the average time of executing 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

I think this happens due a nature of SQLite and great number of joins.

Try to refactor database scheme, like denormalize data for faster access.

Answer 2

The SQLite Query Planner offers some hints about query optimization for SQLite.

Some items that may apply to your question:

1.) Due to the implementation in SQLite you might try to re-order the multiple joins:

The current implementation of SQLite uses only loop joins. That is to say, joins are implemented as nested loops. The default order of the nested loops in a join is for the left-most table in the FROM clause to form the outer loop and the right-most table to form the inner loop.

So, depending on how the JOINs are constructed there might be a difference in performance.

SQLite tries to optimize this automatically, but as far as I understood the documentation there is no guarantee for success (highlights by me):

However, SQLite will nest the loops in a different order if doing so will help it to select better indices. [...] Join reordering is automatic and usually works well enough that programmers do not have to think about it, especially if ANALYZE has been used to gather statistics about the available indices. But occasionally some hints from the programmer are needed.

2.) Also, please note that INNER JOINS are internally converted into WHERE clauses, so any of the performance tips in the WHERE section of the document might apply, too:

The ON and USING clauses of an inner join are converted into additional terms of the WHERE clause prior to WHERE clause analysis described above in paragraph 1.0. Thus with SQLite, there is no computational advantage to use the newer SQL92 join syntax over the older SQL89 comma-join syntax. They both end up accomplishing exactly the same thing on inner joins.

3.) You might consider to select more columns in your statement, if there are any indexes on them:

It is not necessary for every column of an index to appear in a WHERE clause term in order for that index to be used. But there can not be gaps in the columns of the index that are used.