Thursday, July 23, 2009

Efficient Nettiers Deepload - Filling object graphs in just one database call

After using several ORM's I found that when need a complete object graph or some related entities (INNER/LEFT JOIN "like") they haven't an efficient approach built-in.
Almost all ORM can lazy load related properties (collections or instances) that means additional db calls and specifically in Nettiers filling an object with n-childs implies (n+1) database calls with the related performance penalty.
On this post, I want expose one alternative to populate an entire object graph in just one database call.
The concept it's very simple and consists on deserialize a string returned from the database that contains an xml with the representation of the entire object graph.

Example
"In one application that uses the AdventureWorks database you need to get the orders with their order details and the product referenced in the order detail for a selected customer."

1) First develop a simple method to take a look to the string that the deserializer is expecting.
Usage:
string sampleData = GetSampleData(676);

Method:
protected string GetSampleData(int customerId)
{
SalesOrderHeaderService salesOrderHeaderService = new SalesOrderHeaderService();
Type[] childTypes = new Type[] { typeof(TList<SalesOrderDetail>), typeof(Product) };
TList<SalesOrderHeader> salesOrders = salesOrderHeaderService.DeepLoadByCustomerID(customerId, true, DeepLoadType.IncludeChildren, childTypes);
return EntityHelper.SerializeXml<SalesOrderHeader>(salesOrders);
}
2) Create an stored procedure that returns an xml with the representation of the entire object graph
CREATE PROCEDURE [dbo].[SalesOrdersGetWithDetailsAndProductsByCustomerId]
@customerId int,
@result ntext OUTPUT
AS
BEGIN
SET NOCOUNT ON;

SET @result=
'<?xml version="1.0" encoding="utf-16"?><ArrayOfSalesOrderHeader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'
+
(SELECT SalesOrderHeader.*,
'<SalesOrderDetailCollection>' +
(SELECT SalesOrderDetail.*,
(SELECT Product.*
FROM Production.Product Product
WHERE Product.ProductID = SalesOrderDetail.ProductID
FOR XML RAW ('ProductIDSource'), ELEMENTS)
FROM Sales.SalesOrderDetail SalesOrderDetail
WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
FOR XML AUTO, ELEMENTS)
+ '</SalesOrderDetailCollection>'
FROM
Sales.SalesOrderHeader SalesOrderHeader
WHERE SalesOrderHeader.CustomerID = @customerId
FOR XML AUTO, ELEMENTS)
+
'</ArrayOfSalesOrderHeader>'

SELECT @result

END;

3) Call the SP and deserialize the result
Usage:
TList<SalesOrderHeader> customerSalesOrders = SalesOrdersGetWithDetailsAndProductsByCustomerId(676);

Method:
protected TList<SalesOrderHeader> SalesOrdersGetWithDetailsAndProductsByCustomerId(int customerId)
{
SqlCommand command = new SqlCommand("SalesOrdersGetWithDetailsAndProductsByCustomerId");
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@result", SqlDbType.VarChar);
param.Direction = ParameterDirection.Output;
param.Size = 10000;
command.Parameters.Add(param);
command.Parameters.Add(new SqlParameter("@customerId", customerId));
DataRepository.Provider.ExecuteNonQuery(command);
string resultParameter = command.Parameters[0].Value.ToString().Replace("&lt;", "<").Replace("&gt;", ">").Replace("&amp;lt;", "<").Replace("&amp;gt;", ">");
return EntityHelper.DeserializeListXml<SalesOrderHeader>(resultParameter);
}
Notes:
- Remove XmlIgnore Attribute for the related entities that you want populate. You can identify them by the "Source" sufix (ProductIDSource in the example).
- Attributes serialization is case sensitive, if you has applied some style on the UsePascalCasing codesmith template property, your class fields names casing could be different that the db field definition. In this case use field aliasing in the stored procedure select clause.
- To deepload a collection put the subquery on the select fields and enclose with the collections delimiter ('SalesOrderDetailCollection' in the example).
- To deepload a one to one relation put the subquery on the select fields and use for "RAW" in FOR XML clause to overwrite the row element name generated automatically ('ProductIDSource' in the example).


6 comments:

Anonymous said...

You might want to check out the Entity Framework for something like this.

Martin Olivares said...

I will take a look, but if you have a link to share with a one database call object graph filling example using the EF will be great.

Anonymous said...

What would be the purpose of doing this for a list of objects? I understand the concept for an individual object, for instance, editing. If it is to display in a listed form, why not just use a custom procedure that only returns the data that you need to display? NetTiers will generate that code as well and return either a DataSet or IDataReader. This will give you optimal performance.

Martin Olivares said...

The purpose is fill a complete object graph like order-> order details -> order product in just one single db call and avoid the Select n+ 1 issue (http://www.google.com.ar/search?q=select+n%2B1).
About retrieve the required data in a Dataset, this is not an option in some cases like when you need manipulate the object to apply business rules.

Eric Pelletier said...

There is a problem with this.
When I run the stored procedure I have an error that I cannot define NText as local variable.

So i Try only to do a select but my return result is truncate for a lot of information arount 40000 char.

Martin Olivares said...

Hi Eric,

Which sql server version are you using? You could try use NVARCHAR(MAX) for the local variable (this turn to a datatype similar than NTEXT when size is more 8000 characters)