List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:July 9 2004 12:28pm
Subject:Re: Cost of joins?
View as plain text  
Margaret,


> 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.

Don't bother...

> 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!

Design for understanding, logic and maintenance, not performance.

If you need more performance, throw more hardware at it -
a larger cache (settings -> memory), faster disks and a faster CPU.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

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