Fastest way to get a single record with SubSonic

Question

I am new to SubSonic and Linq Stuff and I am trying a figure out the shortest and optimal way of retrieving a single record.

What other way is quicker and requires less code to write than this to get a single record?

User user2 = DB.Select().From(User.Schema)
.Where(User.PasswordColumn).IsEqualTo(password)
.And(User.SINumberColumn).IsEqualTo(siNumber)
.ExecuteSingle<User>();

I have used to AntsProfiler tool to check, and this takes avg of 29.12ms CPU time - tested over ten runs

Where as this takes even longer

UserController uc = new UserController();
Query query = new Query("User");
query.WHERE(User.Columns.Password, password);
query.WHERE(User.Columns.SINumber, siNumber);   
User user = uc.FetchByQuery(query).First<User>();

Just the last line take 256.08ms CPU time plus UserController takes 66.86ms.

Any suggestions?

Answer 1

The "bottleneck" would be the generated query executing, not SubSonic generating it or returning the result. If a particular query is slow, you should look into using your database provider's indexing functions to optimize it.

In the second case, I'm assuming the additional execution time is overhead for LINQ to return the first item from the collection.

So my answer would be, the first way is the best way to do it, and if 29ms isn't acceptable (which for a call to a DB, I don't think is unreasonable), add some indexes in your DB to speed up retrieval.

Answer 2

I think that John is right on the money. If you really want to test SubSonic to see how much time it takes you need to test how long it takes subsonic to create the SQL statement not how long it takes SubSonic to create the SQL statement and how long it takes your DB to return the results at the same time(You need to isolate your tests).

Perhaps you should test how long it takes to get the results with a Regular old SQLCommand and pass it your connection and parameters and see how long that takes so that you have a comparison.

Answer 3

IIRC, the Query object is fluent.. i.e.

query.WHERE(User.Columns.Password, password);
query.WHERE(User.Columns.SINumber, siNumber);

Should read as:

query = query.WHERE(User.Columns.Password, password);
query = query.WHERE(User.Columns.SINumber, siNumber);

The increased time in your tests may be accounted for because of this (you're asking for all User items and then grabbing the First).