Hi,
I have a table with 10 000 000 records
there is compound primary key (ObjectID, AttributeID)
1. when I exequte next query
SELECT * FROM ObjectValue WHERE ObjectID=40000 AND AttributeID=3;
in managment tool e.g.sqliteadmin the time of execution is ~16ms
(return result : 1 row)
2. execute this query using System.Data.SQLite:
using (var _cnn = new SQLiteConnection(connectionString))
{
_cnn.Open();
using (DbCommand cmd = _cnn.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM ObjectValue WHERE
ObjectID=@objectId AND AttributeID=@attributeID;";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter("@objectId",
40000));
cmd.Parameters.Add(new SQLiteParameter("@attributeID",
3));
cmd.Prepare();
var dtStart = Environment.TickCount;
using (DbDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
sb.AppendFormat("PersonId: {0}\r\n", reader[0]);
}
}
var dtEnd = Environment.TickCount;
.....
time of execution is ~17000ms
WHY?
My proposition is the index isn't use...
--
Regards,
Evgeny Gavrilenko
email: egavrile...@gmail.com
+375 25 777 19 19
+375 29 218 73 27
skype: evgeny_gavrilenko
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
