I am seeing a HUGE performance hit in populating a large list via WCF using CSLA as opposed to simply using a DataSet and am assuming we are doing something fundamentally wrong.
Returning 5000 rows reuslts in:
The problem appears to be in the DataPortal.Fetch method, which is averaging approximately 15 ms each.
CODE SNIPPETS
Stored Procedure Call:
ALTER procedure [dbo].[sp_GetCustomer] As
Select top 5000 customer_id,name_first,name_middle,name_last, phone_home from customer
I) DataTable ACCESS (136 ms):
public DataTable GetAllCustomers(string connectionString)
{
SqlParameter[] parameters = { };
return = SqlAccessor.ExecuteDataSet(connectionString, SqlAccessor.SqlCommandBuilder(new SqlCommand("sp_GetCustomer"), parameters), CommandType.StoredProcedure, "").Tables[0];
}
II) CSLA ACCESS (76212 ms):
CustomerList: (Performance hit is in the "while" loop processing 5000 records – it is KILLING us)
public class CustomerList : ReadOnlyListBase<CustomerList, Customer>
private void Fetch()
{
this.RaiseListChangedEvents = false;
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "sp_GetCustomer";
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
IsReadOnly = false;
while (dr.Read())
{
Customer info = Customer.GetCustomer(dr.GetDecimal("customer_id"));
this.Add(info);
}
IsReadOnly = true;
}
}
this.RaiseListChangedEvents = true;
}
CUSTOMER: (Averages approx 15 ms per access)
public class Customer : BusinessBase<Customer>
public static Customer GetCustomer(decimal customer_id)
{
return cust = DataPortal.Fetch<Customer>(new Criteria(customer_id));
}
I agree with the above. Its the fact that you are doing the one main database access and then 5000 more (one for each item) and you are going through the dataprotal 5001 times. As said above you should have the one SQL statement which returns all data that is required and then pass the datareader through to the load method (or what ever method you have). Once you so this you will see much better performance.
Anthony
I just wanted to add to this thread to add agreement to the prior two posters as well as refer to a thread where there is some performance discussion.
http://forums.lhotka.net/forums/thread/16966.aspx
ozitraveler also cited some statistics in this thread:
http://forums.lhotka.net/forums/thread/4044.aspx
Truly, the first post of this thread is, as the other two posters above me stated, not comparing apples to apples. The DataTable in the GetAllCustomers method seems to indicate that the sp_GetCustomer stored procedure will pull down all customers with the right parameters.
To adequately modify the Csla case, I would change the items in red to this:
while (dr.Read())
{
Customer info = Customer.GetCustomer(dr);
this.Add(info);
}
The factory method GetCustomer would contain an internal overload that takes a datareader and loads the customer data from the datareader.
I would encourage the author to make these changes and then cite the new statistics. If it's still slow, then I'd myself be interested in running a test of my own. But I would highly suspect that the author will find markedly different results when comparing apples to apples.
Chris
Thanks everyone. The team made the following changes:
New benchmarks are:
CSLA
DataTable
We are setting the fields.
you can get a small performance increase by using the int overloads of the Get methods on the data reader
Also make sure you are using 2.1.4 or preferably 3.0.3+. If you are using 2.0.x you are missing out on some important performance enhancements that would impact this scenario.
It is also important to be aware of what code is running during the load process. If you are calling ValidationRules.CheckRules() in each object's Fetch(), for example, then you are triggering a bunch of work - which may be beneficial, but takes time.
It is always important to remember that performance isn't the only metric - beneficial behaviors are sometimes worth some processing time. But it is also important to be aware of which behaviors are being triggered and to not trigger those you don't need.
Another thing to check is to make sure that your collection object turned off its eventing during the load process - no sense wasting time processing/raising events that go nowhere, and that you wouldn't want even if they did go somewhere.
RockfordLhotka:
It is also important to be aware of what code is running during the load process. If you are calling ValidationRules.CheckRules() in each object's Fetch(), for example, then you are triggering a bunch of work - which may be beneficial, but takes time.
It is always important to remember that performance isn't the only metric - beneficial behaviors are sometimes worth some processing time. But it is also important to be aware of which behaviors are being triggered and to not trigger those you don't need.
And to draw a dotted line to a related matter, if you are just pulling the dataset and then doing some validation later you may be only deferring your performance 'hit'. I mean if you are doing all the validation using CSLA the process might be taking ~800ms, but doing everything, where if you are doing a load with a straight dataset it might be taking ~500ms but later on when you do your processing/validation it might be adding more time.
Ryan
Should be fine – 2.1 was where the big perf changes
happened that would affect this scenario.
Rocky
From: BillyM
[mailto:cslanet@lhotka.net]
Sent: Wednesday, February 06, 2008 2:47 PM
To: rocky@lhotka.net
Subject: Re: [CSLA .NET] HUGE difference in performance using CSLA over
Datatable -- CSLA is SLOW!!!
Rocky... We are using 3.0.2... will that be a problem?
Just for comparison sake, we are using CSLA bolted over an ORMapper (mainly because it provides a database-independent DAL), and we can retrieve 2000+ BusinessBase objects / second even from a lowly workstation database.
No, this isn't as fast as your data table, but it's much faster than the technology we are porting from.
We really didn't notice any difference in this performance when we added CSLA into the mix (originally we talked directly to the mapper).
(NHibernate, incidentally, was a good deal faster than the mapper we use - 8,000 + objects/second, but only because it generates fast property access code at run time. If this feature was turned off, it was about the same).
Hi rsbaker0, I'm interested in knowing what mapper you are using?
/Maqster
We're using the Wilson ORMapper.
We looked at several alternatives because we have some specific requirements. We were migrating a legacy application in which users have the capability to modify both the size of existing fields (both numeric and character) as well as add additional custom fields and tables, which was very limiting in terms of what would work. NHibernate can do this "out of the box", but figuring out how to dynamically modify it's "session factory" at runtime to recognize the additional fields was *very* daunting. Wilson's mapper is small, acceptably fast, nicely written, and easy to modify. It also generates very nice, parameterized SQL.
As to the thread topic, Wilson implements an IObjectHelper interface that sets the fields when loading objects instead of using the properties (which cause all sorts of events and virtual method hits), so we noticed literally no change in loading a CSLA object versus a native mapped object. This as true even though we basically have to "clone" each object as it is loaded as Wilson's mapper doesn't have the capability to populate an object you have already created (which is what DataPortal_Fetch wants to do).
Copyright (c) Marimer LLC