posted on Thursday, February 03, 2005 9:32 AM by mlorengo

Optimizations in Complex Object Creation Part Deux

In comments to my article on Optimizations in Complex Object Creation, Frans Bouma suggests altering my query to use subqueries for the selecting the data rather than inner joins. I must confess, that I am not the all knowing Sql guru, so I appreciate the feedback, and it certainly makes sense.

To quote from his comment...

Method 2 is often the slowest. If you have 10 wines for a producer and per wine 10 varieties, you have 1 + 10 + 10*10 queries.

If you use:
fetch the wines.
Use the wines WHERE clause as the where clause for a subquery to fetch the winevarietal rows and use THAT where clause as the where clause for the subquery for varietal, you'll get 3 queries.
Then merge the results in memory, which is very straight forward. This is different from method 3 in that you don't pull a lot of data you don't want with the join, as joins can produce a lot of redundant data.

Well, wouldn't you know it, I'm using the slowest method currently. But again, it all depends how I want to use the data, let's say I don't want to lazy load the information, Frans suggests redoing my Select cause. So, after doing that, I now have something like this, that I can use for Method 3, which should reduce the amount of data coming back.

SELECT Wine.Id, Wine.ProducerId, Wine.Name, Wine.Vintage, Varietal.Name, WineVarietal.Percent
FROM Varietal, 
  (SELECT * from winevarietal, 

    (SELECT * from Wine Where wine.producerid=[@producerId)
      WHERE winevarietal.wineId = Wine.id) As WineSubQuery
 
    WHERE ((([WineVarietal].[VarietalId])=[Varietal].[Id]))
ORDER BY Wine.Id;

Dru, comments as to why I don't return a DataSet/DataTable to get all of the data  and then populate my objects.

My answer is that there is no reason that I couldn't, and in fact I have some methods in my IWineProvider interfaces that return the results as a DataSet. It's just that I don't need (right now) the extra overhead of a DataSet in loading my objects. And again, I can choose to modify the underlying methods as I or other people suggest improvements (as well as using the Sql suggestions).

So, Frans & Dru and anyone else thanks for the feedback, and if I missed the point you were trying to make, please let me know.

Comments