Question about performance of lists/collections

Vij · **Joined:** Fri Apr 21, 2006 5:33 pm **Posts:** 12

Hi all,

I have a question around performance of collections/lists:

Take the following code example:

Code:

void add(OrderItem item) {
    if (!_order.OrderItems.contains(item))
        _order.OrderItems.add(item);    
}

This seemingly innocuous piece of code would:

If I were to do the same using a data-oriented approach, I'd use the following (pseudo code for brevity):

Code:

SqlCommand _cmd = _sqlConnection.CreateCommand();
_cmd.CommandText = "SELECT COUNT(*) FROM OrderItem WHERE OrderItemID = " + item.ID.ToString();
int _count = (int)_cmd.ExecuteScalar();
if (_count == 0)
    // Insert the record into the table

The data oriented approach is much better for performance - one db hit to do a count, and then one to do the insert. I'm wondering how to get this kind of behaviour using the POCO approach.

I'm guessing that I need an 'intelligent' collection, which only loads all of it's elements in specific cases. For example, doing a contains() could hit the db directly, whereas an enumeration would need all elements.

Any thoughts or ideas on this matter?
TIA

sergey · **Posted:** Thu Mar 22, 2007 5:34 am

It's not possible yet in NHibernate, but Hibernate 3 has this feature (called extra-lazy collections), so it could be ported to NHibernate in the future.

Vij · **Joined:** Fri Apr 21, 2006 5:33 pm **Posts:** 12

Thanks Sergey.

In the meantime, can you (or anyone else) suggest alternative ideas?

xasp · **Posted:** Thu Mar 22, 2007 10:39 am

Hi,

I guess you could just do the same with POCO as you would with SQL :

Code:

            ICriteria crit = session.CreateCriteria(typeof(OrderLine));
            crit.Add(new EqExpression("Order", order));
            crit.Projection = Projections.RowCount();
            int count = (int)crit.UniqueResult();

            if (count == 0)
            {
                //  create orderline
                OrderLine newoline = new OrderLine();
                newoline.Order = order;
                // ...

                //  save orderline to DB
                session.SaveOrUpdate(newoline);
                session.Flush(); 
            }
            
            //  warning: order.Lines does at this point NOT contain the newly added line, because adding the
            //  orderline to the order ( order.Lines.Add(newoline) ) would initialize the order.Lines collection.

Note that I'm assuming here that there's a bidirectional relationship between Order and OrderLine (= OrderLine has an association 'Order').
The code above should result in the same amount of roundtrips to the database as SQL would.