Performance Q&A
One of the first questions people always ask about PLINQO is "do we have any performance statistics?" Well, to quote a wiser man than myself: "People can come up with statistics to prove anything. 14% of people know that." ~ Homer J. Simpson
PLINQO's primary plan for performance improvement is simple: reduce the number of trips that you need to make to the database. A round trip to the database and back is one of the most costly things an application can do, but it's also one of the most common things an application must do. PLINQO offers several easy to use features that can dramatically reduce database transactions, but it's up to the developer to use them.
Bottom Line: PLINQO can and will out preform standard LINQ to SQL. By how much? That is entirely up to you!
Batch Updates and Deletes
Updates with LINQ to SQL
To update records with LINQ to SQL, you must retrieve objects from the database, make your updates, and then commit the changes back to the database. This requires TWO round trips to the database.
1) Build the query, query the database, wait for a response, store the data in memory.
2) Make your updates, commit the changes to the database, wait for the response.
Batch Updates with PLINQO
PLINQO offers a batch update method on its table objects. To update as many records as you want, merely create a query, send it to the database, and get back your result. Unlimited rows, ONE trip.
1) Build the query, update the database, get the response.
// Update all Tasks with a StatusId of 1 to have a StatusId of 2,
context.Task.Update(t1 => t1.StatusId == 1, t2 => new Task() { StatusId = 2 });
Batch Deletes
To delete a record in LINQ to SQL you must first retrieve that record. That is TWO complete round trips to the database to delete one little record! PLINQO allows for batch deletes in the same manner as batch updates, as many records as you want, without loading records into memory, and in just ONE trip.
// Delete all tasks where StatusId is 2,
context.Task.Delete(t => t.StatusId == 2);
Stored Procedures
While the LINQ to SQL designer does support stored procedures, it only supports returning a single result set. PLINQO supports stored procedures with multiple result sets, and provides a simple way to handle those results. Again, this is yet another way PLINQO helps get you more data with fewer trips to the database.
// Create Procedure [dbo].[GetUsersWithRoles]
// As
// Select * From [User]
// Select * From UserRole
// GO
var results = context.GetUsersWithRoles();
List<User> users = results.GetResult<User>().ToList();
List<UserRole> roles = results.GetResult<UserRole>().ToList();
Batch Queries
In LINQ to SQL every query is a trip to the database. It doesn't matter if you have five queries in succession that require no logic in between, you must still make each and every query separately. The PLINQO DataContext offers an ExecuteQuery overload that will execute as many queries as you want in a single transaction. This is an extremely simple feature to use, and it can drastically improve performance in every day development scenarios.
var q1 = from u in context.User select u;
var q2 = from t in context.Task select t;
IMultipleResults results = context.ExecuteQuery(q1, q2);
List<User> users = results.GetResult<User>().ToList();
List<Task> tasks = results.GetResult<Task>().ToList();
