RE: Selecting (and accessing) from many tables

[prev] [thread] [next] [Date index for 2004/09/21]

  =20
=20
echoing some of what Tony wrote:

> > 200 queries isn't itself a problem. When there are ten or
> > twenty or more people using the database at the same time,
> > however, doing queries some of which are computationally or
> > I/O intensive, I'd rather not subject it to an extra 199
> > queries/user for each request.
>=20
> This still sounds like premature optimisation to me. Most=20
> databases are
> really very efficient at this sort of thing (a single row select on a
> primary or foreign key).

Depending on your choice of DB, you may find that it can do the 200 =
single queries in almost the same time it takes to do the single join =
query.

Also don't forget the savings in data transfer. For example you only =
have to return each Artist once - no matter how many Albums they have.

> > Perhaps I can do the main query all raw, and then use CDBI for
> > some of the accessorizing. That had been my goal with using
> > set_sql, but I hadn't realized that getting things out would
> > be this complicated.
>=20
> It's only complicated if you want to make it so. You're getting a load
> of data back that doesn't map to a single object. So you need=20
> to decide
> whether to make it be a 'pretend' object (a trivial subclass with some
> extra TEMP columns), or whether you want to flesh out all the=20
> associated
> classes instead. The first is fiarly trivial. The second is more
> involved, as you're setting some values to be other objects, but it's
> hardly rocket surgery.

Either of these would effectively just be moving the load though, from =
the database to the front-end. Of course that may be more acceptable =
(especially if you're paying for Oracle licensing :-).

If you really want to improve performance, grab the DB Handle from CDBI =
and use native DBI methods.

Carl  =20
--------------------------------------------------------
GMG Regional Digital is part of the Guardian Media Group plc.=20
=20
=20
 =20
=20
CONFIDENTIALITY NOTICE. The information contained in this e-mail is =
intended only for cdbi-talk@xxxxxx.xxxxx.xxx. It may contain privileged =
and confidential information that is exempt from disclosure by law and =
if you are not an intended recipient, you must not copy, distribute or =
take any action in reliance on it. If you have received this e-mail in =
error, you may notify us by telephone on 44 (0)161 832 7200. E-mail =
transmission cannot be guaranteed to be secure or error-free. The sender =
(carl.johnstone@xxxxx.xx.xx) therefore does not accept liability for any =
errors or omissions in the contents of this message, which arise as a =
result of e-mail transmission. If verification is required please =
request a hard-copy version. =20
 =20
 =20
 =20
 =20
=20
=20