database - Query is much faster in c# above System.Data.SQLite if first load SQLite table into .NET DataTable and then use DataTable.Select. why? -

i have tables in sqlite database. use c# , system.data.sqlite dll access database. test shows following query slow:

string sql = "select column1, column2 table_1 column1=1 , column2=2"  sqlitecommand mycommand = new sqlitecommand(cnn); mycommand.commandtext = sql; sqlitedatareader reader = mycommand.executereader(); tb.load(reader); reader.close(); 

but if first load whole table .net datatable below, , use datatable.select(), it's faster:

string sql = @"select * '" + data_table_name + "'" + ";\n"; sqlitecommand mycommand = new sqlitecommand(cnn); mycommand.commandtext = sql; sqlitedatareader reader = mycommand.executereader(); tb.load(reader); reader.close(); datarow[] rows = tb.select("column1=1 , column2=2"); 

the difference more significant if have multiple queries after loading whole table.

this cache whole table in .net. tried different kind of tables different sizes , primary keys, behave same.

for now, know use such optimization, wondering if there other workaround.

also, figured out if index of table string rather integer, long string short (tested 10 characters string), direct sql query faster without caching table. however, after caching table, using integer index faster string index.

anybody can explain behavior?