List:General Discussion« Previous MessageNext Message »
From:Margaret MacDonald Date:July 9 2004 11:07am
Subject:Cost of joins?
View as plain text  
Is there a generally-accepted rule of thumb for estimating the
performance cost of joins?   I can't find one even in Date, but
intuitively it seems as though there must be one by now.

I'm thinking of something like 'if not doing anything costs 0, and
reading 1 table costs 100, then joining a second table brings the cost
to  150, a third to 225, etc' (or 110 or  500 or whatever the
numbers/functions really are).   

I'm working on a large information system --probably tens of millions
of records-- and am guessing that reads should outnumber writes by a
factor of 10 at least.   High performance from the human user's point
of view is an absolute requirement for success.

Rather than store category and other  'flavoring' strings repetitively
in the information records themselves, I've stored indexes into other
tables.  But that means every lookup joins 3-7 tables.  How much am I
paying for that?   

Adding the flavoring strings themselves to each record would increase
the overall storage requirement by some large fraction less than 50%.
Since disk space is already relatively cheap, that shouldn't be a
problem.  But some of the flavoring is guaranteed to change at an
unpredictable rate,  which would mean repeatedly traversing every
information record to update it.

I'm new to databases, but I'm sure this is a 'been there done that'
problem for the more experienced people on the list!

Thanks for any insights!
Margaret
Thread
Cost of joins?Margaret MacDonald9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
    • RE: Cost of joins?Lachlan Mulcahy12 Jul
      • Re: Cost of joins?Martijn Tonies12 Jul
      • Re: query gets count wrongGerald Taylor12 Jul
  • Re: Cost of joins?Alec.Cawley9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
    • Re: Cost of joins?Jochem van Dieten9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
      • Re: Cost of joins?Jochem van Dieten9 Jul
    • Re: Cost of joins?(Michael Johnson)9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
RE: Cost of joins?Bob)9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: query gets count wrongSGreen12 Jul