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.