what is the fastest way to find a record by id in a database in c#?

Question

I have created a database using DataContext.CreateDatabase and inserted records in it.

There are a lot of records in it so I want to find a record by it's id in the fastest way.

firstly I tried:

foreach (var currentRecord in _context.Cities)
{
  if (currentRecord.ID == recordIdToFind)
    return currentRecord;
}

but it was very slow so I changed it to:

var recordToReturn = from r in _context.Cities
                     where r.ID == recordIdToFind
                     select r;
return recordToReturn.FirstOrDefault();

and got it faster.

is there is a better-faster way?

Answer 1

Whatever you are searching on (regularly) needs to have an index defined on it in the database for optimal speed. Note that some columns and certain kinds of searches don't index well (large text fields or "contains" searches, for example) and a different kind of index (full text) may be needed for those. In your case, it appears that you are using the primary key, which should have a clustered index on it.

Once you have the index(es) defined, you then want to perform queries that take advantage of the index. The first query does a full table scan, loading all the results into memory and then iterating through them in code. You haven't given the database any chance to help you speed up the query and are transferring a lot more data than you need. Your second query allows the database to use the index to find just the row that you are interested in by adding a where clause that specifies an indexed column. If done in isolation (i.e., you're only looking up this one row, not every row in order), then it's optimal. It does an index lookup for the row and then transfers just that row to your application.

If it is, in fact, the primary key, then you might improve on the readability, though not the performance, by using SingleOrDefault as there can only be one row with that key in the table. Enforcing the singleness of the query will also help detect potential errors, though not in the context of the primary key, if you have a column that you expect to be unique but isn't. In that case SingleOrDefault will throw an exception if more than one result is found, whereas FirstOrDefault will simply choose the first one in the set without giving you any information about the inconsistency.

Answer 2

your second code example should be the fastest way to do this

Answer 3

The second one got faster because it generates a where clause, something like WHERE ID = <whatever> when querying the database, which returns only the matching row to your application.

The first one is slow because it reads every record from the Cities table in the database and copies them all over to your application, which discards all but one of them.

If you don't have one already, an index (or more likely a Primary Key) on the ID column of the Cities table will make this even faster, particularly as you add more data to the table.

Answer 4

The linq statement is about as good as it gets. Under the hood it has to translate that linq statement and firstordefault into a statement to the SQL server. And upon retrieving the results it maps that into a cities object.

So it actually is sent as something akin to this prepared statement:

SELECT TOP 1 * FROM Cities where ID=@ID

You could theoretically speed it up by sending the prepared statement yourself as follows, but it would not give you the cities object and is not noticeably faster in most situations: (LET ME REPEAT, this is not likely what you want to do, but it is a faster way of getting the data)

string commandText = "SELECT TOP 1 * FROM Cities where ID=@ID;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    command.Parameters.Add("@ID", SqlDbType.Int);
    command.Parameters["@ID"].Value = recordIdToFind;

    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    try
    {
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
                reader[0], reader[1]));
        }
    }
    finally
    {
        // Always call Close when done reading.
        reader.Close();
    }
}