SQL Strategies for parent - child tables

Question

I could use some help determining the best (most performant / easily maintainable) strategy for retrieving parent-child objects from a SQL db.

I inherited this code, and I've got relatively short deadline on this and I want to do as little foundational changes as possible. I'll have plenty of time to implement nhibernate or other ORM with the next spiral, I just can't do it now. I'm looking for the best thing to do in the shortest amount of time with the least amount of modification.

The twist is that there are different child types (which implement a common child interface).

For example,
Parent: VehicleFleet (contains fleet name, manager name, a list of Vehicles)
Child: IVehicle (contains make, model, location, etc)

However, there could be multiple types of IVehicle - such as Car, Van, Motorcycle - each with different properties/columns. There is a separate table for Car, Van, and Motorcycle. There may or may not be a VehicleBase table which contains columns which applies to any IVehicle.

What is the best strategy for returning multiple VehicleFleet objects, each with their related Vehicle children?

Here's a couple strategies I've tried (presented in pseudo-code) -

Assumptions:
All GetXXXX functions are using a DataReader behind the scenes

Method 1: Simple & Slow - this is the worst way to do it, for obvious reasons

IEnumerable<Fleet> GetFleetsAndVehicles () {
 foreach (var fleet in myFleetDao.GetAllFleets ()) {
  foreach (var vehicleTypeDao in myVehicleTypeDaos)
   fleet.Vehicles.Add (vehicleTypeDao.GetVehicles (fleet.Id);
  yield return fleet;
 }
 yield break;
}

Method 2: Prefetch children

IEnumerable<Fleet> GetFleetsAndVehicles () {
 var allVehicles = (from vtd in myVehicleTypeDaos
    from v in vtd.GetAllVehicles()
    select v).ToLookup (v => v.FleetId);

 foreach (var fleet in myFleetDao.GetAllFleets ())
 {
  fleet.Vehicles = allVehicles[fleet.Id].ToList ();
  yield return fleet;
 }
 yield break;
}

Method 3: Prefetch children, attach children asychronously

IEnumerable<Fleet> GetFleetsAndVehicles () {
 foreach (var fleet in new AsyncGetter.GetFleetsAndVehicles ())
  yield return fleet;
 yield break;
}

class AsyncGetter
{
 // left out instance variables, Auto/Manual Reset Events, locking, etc. for brevity
 IEnumerable<Fleet> GetFleetsAndVehicles ()
 {
  StartAsyncStuff ();

  while (myUnconsumedFleets.Count > 0)
  {
   yield return myUnconsumedFleets.Remove (0);
   WaitUntilMoreFleetsAreAdded ();
  }
  yield break;
 }

 void StartAsyncStuff ()
 {
  myAllVehicles = <same as method 2>

  foreach (var fleet in myFleetDao.GetAllFleets ())
  {
   AttachVehiclesAsync (fleet);
  }
 }

 void AttachVehiclesAsync (Fleet f)
 {
  // assume using ThreadPool.QueueUserWorkItem right now
  WaitForAllVehiclesToLoad ();
  f.Vehicles = myAllVehicles[f.Id].ToList ();
  myUnconsumedFleets.Add (f);
 }
}

Method 4: interleave parent/child queries

IEnumerable<Fleet> GetFleetsAndVehicles () {
 var allVehicles = from vtd in myVehicleTypeDaos
    from v in vtd.GetAllVehicles()
    orderby v.FleetId
    select v;
 var allVehiclesEnumerator = allVehicles.GetEnumerator ();

 foreach (var fleet in myFleetDao.GetAllFleets ())
 {
  fleet.Vehicles = GetAllChildVehiclesAndMaintainEnumeratorPosition (allVehiclesEnumerator, fleet);
  yield return fleet;
 }
}

So far, using some test data, I'm seeing that Method 3 is the most performant (27% faster than next best), while Method 1 is the worst (4x slower than Method 1).

So if you've got suggestions, I'd love to hear them!

Answer 1

At the risk of getting downvoted for not providing a helpful answer for the problem at hand, I still need to say: I thought we had left the 'DIY data access layers' behind these days. Sure, there are probably still use cases where you really need to do some custom data reader magic. Mapping an inheritance hierarchy from the database to your object model is generally not one of them though.

There's plenty of ORM's available that solve this problem for you. It's called "Table per Type inheritance mapping". Any decent ORM supports this, and allows you to eagerly fetch parent/child relations.

If performance is really that much of an issue (is it?), then you will probably gain the most by either switching to 'single table inheritance' strategy (all types in one single table, with a discriminator column).

Both Entity Framework and NHibernate support single-table and table-per-type out of the box. Linq 2 SQL (OK, maybe not a full ORM) only supports single-table inheritance; as @Albin Sunnanbo said, it may be an option if you can change the database schema. There's lots of other ORM's out there worth investigating.

There, it's off my chest ;-), hope it helps.