8
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();