The fastest way to get one recording from SubSonic
I am new to SubSonic and Linq Stuff and I am trying to figure out the shortest and most optimal way to get a single entry.
What other way is faster and requires less code to write than to get one record?
User user2 = DB.Select().From(User.Schema)
.Where(User.PasswordColumn).IsEqualTo(password)
.And(User.SINumberColumn).IsEqualTo(siNumber)
.ExecuteSingle<User>();
I used the AntsProfiler tool to test and it takes an average CPU test time of 29.12ms over ten runs
If it takes 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>();
The last line alone takes 256.08ms of CPU time plus the UserController takes 66.86ms.
Any suggestions?
IIRC, the request object is free. Those.
query.WHERE(User.Columns.Password, password);
query.WHERE(User.Columns.SINumber, siNumber);
Should read like:
query = query.WHERE(User.Columns.Password, password);
query = query.WHERE(User.Columns.SINumber, siNumber);
Increased times in tests can be accounted for due to this (you query all custom items and then grab the first one).
source to share
The bottleneck will be the generated query, not the SubSonic generating it or returning the result. If a particular query is slow, you should examine its database provider's indexing features to optimize it.
In the second case, I assume the extra runtime 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 is not acceptable (which is for a DB call, I don't think it is unreasonable), add some indexes to your DB to speed up searches.
source to share
I think John is right about the money. If you really want to test SubSonic to see how long it takes, you need to check how long it takes SubSonic to generate the SQL statement, not how long it takes SubSonic to create the SQL statement and how long it takes for your DB to return results to the same time (you need to isolate your tests).
Maybe you should check how long it takes to get the results with the plain old SQLCommand and pass your connection and parameters to it and see how long it takes to compare.
source to share