SMO and object-relational mapping

I've been looking at the SMO (SQL Server Management Objects) model recently. Because the amount of data they retrieve is potentially large and potentially expensive to produce (say sp_spaceused on every database), they use most/all of the *data transfer optimization* techniques: both sparse and eager population, lazy instanciation, caching, deferred/chunky writeback, and locality of reference. Reasonable, documented defaults with progammable overrides.

What's this got to do with object-relational mapping? Well, nothing. And everything. These techniques are data optimization techniques. They are the same whether you talk about them in terms of SQL SELECT statements or objects. The fact that pieces of data in a database are mapped to properties of classes in an object model (data model mapping) is only peripherally related to data access optimization, at best.

Before I go forward, I'll state that I don't consider myself an O/R mapping (or XML-relational or XML-object) bigot on either side, either for it or against it. Really. But many folks, especially when they are bashing mapping, use the data transfer optimization techniques (along with another data access concept – locality of reference) to justify their arguments. That's like me blaming the inventor of the knife and fork for my current extra weight. Data optimization, badly implemented, will kill a data-centric object model. But you can accomplish the same thing with bare SQL statements and DataReaders/Rowsets.

For an easy to picture example, imagine paging through a large resultset in ASP.NET. You can:
1. Cache the entire resultset (eager population + caching)
2. Use TOP queries to SELECT on a per-page basis using a custom ASP.NET pager
3. Send the whole bunch to client for paging (eager population + locality of reference)
4. Fetch entire resultset each time and throw all but one-page away. (bad perf).

You can accomplish all this, including the bad design, with DataReaders only, using DataSets for your cache. Not an object-mapped-to-a-relation in sight. Another easy-to-picture example is the Windows file explorer, with plus signs next to each directory. Sparse population/lazy instanciation. The “show details“ setting: sparse property population choice.

Here's a few more quick examples, using ObjectSpaces (technology on-hold, but this was the latest implementation I'd studied) and SQL statements.

// baseline
ObjectReader<Customers> or = os.GetObjectReader<Customer>("");

SqlCommand cmd = new SqlCommand("select * from customers");
SqlDataReader rdr = cmd.ExecuteReader();

// eager population
ObjectReader<Customers> or = os.GetObjectReader<Customer>("", "Orders, Details");

SqlCommand cmd = new SqlCommand(
 "select a.*, b.*, c.* from customers a
  inner join orders b on a.custid = b.custid
  inner join details c on b.orderid = c.orderid");
SqlDataReader rdr = cmd.ExecuteReader();

// sparse population
// customers contains a customer photo
// sparse population in mapping file, can be overriden on query
ObjectReader<Customers> or = os.GetObjectReader<Customer>("");

// leave customer photo out of the SELECT
SqlCommand cmd = new SqlCommand("select customerid, customer_name… from customers");
SqlDataReader rdr = cmd.ExecuteReader();

SO…is mapping among data models a bad idea, or is not knowing/designing for/using the data optimization techniques a bad idea? Mapping objects to data *badly* (or having a model so narrow there are no choices) is just as bad as writing bad SELECTs. You have to know where your data lives, how expensive it is to produce it, and how you are going to use it in ANY case. O/R mapping DOESN'T ABSOLVE YOU from that responsibility. But bad designs are the result of uninformed designers, not a specific technology. Thinking that a specific technology is a panacea and now you don't have to consider design, is also dangerous.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.