Re: Object oriented method is inefficient with database calling
- From: "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xxxxxxxxx>
- Date: Fri, 11 Aug 2006 02:34:02 -0700
Jon Skeet [C# MVP] wrote:
Linda Liu [MSFT] <v-lliu@xxxxxxxxxxxxxxxxxxxx> wrote:
I think you should take different strategies depending on the
amount of records a database query to the table t_car returns.
If a database query to the table t_car returns a great deal of
records a time, I think you'd better load a Car object for each
record and leave the Driver property in the Car object aside. As
you said, when the public property Driver is accessed, use the
private property DriverID to access the database and load a Driver
object.
In the above scenario, if you load the Driver object when you load
the Car object, the network traffic would be very heavy because
there're a great deal of Car objects after the query and you are
going to load a Driver object for each Car object.
I don't think you should use a join to query the database in this
instance either, because the join operation will consume a lot of
resources as well.
You think it's better to do 501 queries to retrieve 500 cars rather
than 1 which has a join? I have to disagree. If you're pretty sure
you're going to need to use the driver, then joining the two tables
is the logical approach IMO.
Joins aren't the solution. It might be in a simple 2 node graph with a
m:1 fetch (this particular situation) but once you have multiple graph
paths, it's not the way to go as a join will make it impossible to
formulate several common scenario's of prefetch paths in 1 query.
What's faster are 2 queries: one for the cars and one for the drivers.
You then use a small trick.
- if the # of master rows (cars in this case) is below a given
threshold, do:
select ... from drivers where driverid in (@D1, @D2, @D3.... @Dn)
where @Dx is a driverid from the cars.
- if the # of master rows is equal or above a given threshold, do:
select ... from drivers where driverid in (select driverid from cars
where <filter on cars>)
and you merge them on the client with hashvalues, which is pretty
simple and straightforward and fast.
This gives the least amount of roundtrips and overhead and the queries
always succeed, no matter how complex the graph of paths is, so you can
create generic code to produce these queries.
Joins seem a logical choice, eventually with UNIONs, though they
aren't.
Also, when pulling master-detail data in a 1:n scenario from the db,
it's often more efficient to use a subquery, due to the duplicates on
the master-side it will give. (one of the serious performance issues in
DLinq, if they fail to implement a subquery directive hint)
The advantage of loading a Driver object when the public property
Driver is accessed is that this avoids the amount of querying
database being too much a time.
On the other hand, if you query the table t_car and get one record
a time, you could load a Car object for the record and then access
the table t_driver by the value of DriverID field in the Car object
and load a Driver object. This won't cause network traffic being
heavy.
It'll cause far more queries though. I would have thought that would
actually mean heavier network traffic than fewer queries returning
the same total amount of data in effectively bigger chunks.
I agree, lazy loading on the car object to pull the driver from the db
is not the way to go if you want to load ALL drivers associated with a
loaded set of cars.
Lazy loading is only useful if you want to pull related data of a
small subset of entities on an occasional basis from the db, not in
graph-oriented fetches.
Frans
--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
.
- Follow-Ups:
- Re: Object oriented method is inefficient with database calling
- From: Jon Skeet [C# MVP]
- Re: Object oriented method is inefficient with database calling
- References:
- Object oriented method is inefficient with database calling
- From: TS
- RE: Object oriented method is inefficient with database calling
- From: Linda Liu [MSFT]
- RE: Object oriented method is inefficient with database calling
- From: Jon Skeet [C# MVP]
- Object oriented method is inefficient with database calling
- Prev by Date: Add and set a key in Remote Systems's registry in C#
- Next by Date: Re: Broadcast
- Previous by thread: RE: Object oriented method is inefficient with database calling
- Next by thread: Re: Object oriented method is inefficient with database calling
- Index(es):
Relevant Pages
|
