Re: Designing database tables for performance?

From: Cimode <>
Date: 7 Mar 2007 01:29:03 -0800
Message-ID: <>

On Mar 7, 1:06 am, paul c <> wrote:
> joel garry wrote:
> > On Mar 6, 12:52 pm, "Cimode" <> wrote:
> >>On 6 mar, 05:59, "" <> wrote:
> >>>On 24 Feb, 13:30, "Cimode" <> wrote:
> >>>>On 23 fév, 22:33, "jgar the jorrible" <> wrote:
> >>>>>>In what RAM would be less physical than HD ? For any reason, an
> >>>>>>absurdity is an absurdity.
> >>>>>Not an absurdity, you just aren't paying attention to how the I/O is
> >>>>>counted.
> >>>>So you say there are *ways* to count IO's. Fair enough. Question is:
> >>>>what has the way of counting IO's has any bearing on the media that
> >>>>supports them and therefore qualifies their nature as physical or
> >>>>logical? What is the difference: speed?
> >>> Yes. A logical I/O does not go down an I/O channel but comes via the
> >>>memory bus.
> >>And in what a memory bus is less *physical* than an IO channel? How
> >>do you think such bus is filled with data at some point in time
> >>otherwise than by a pull on the IO channel.
> > ??? The whole point is that it might have been updated only in memory
> > and may have nothing to do with I/O until some time in the future, or
> > maybe never if it is rolled back.
> > ...
> No, that's only one aspect of the point. When Codd spoke of assymetry,
> he certainly had performance in mind. Then he advocated a limited
> version of relations that he could see could compete with the
> hardware-oriented approaches of the day.
> All IO's aren't equal, eg., process-to-process transfers might be an
> order of magnitude cheaper than disk I/O. How much depends on the
> algorithms and the concepts the engine tries to implement. RT is not
> immune to this.
> Today, many, perhaps a majority of programmer operate under the illusion
> that memory "IO" is of no time cost. In fact, it often reduces the CPU
> speed people think they bought by an order of magnitude.
> On the other hand, at one time in the 1970's, every 28 days, I had to
> consolidate the chocolate bar sales of the provinces of Canuckistan.
> This required much disk IO and sorting and merging. That was with a
> machine that had only 64K of main memory. I used to dream of 256K -
> with that I could have done the whole so-called country in one go,
> saving much setup time as well as other chances for errors.
> Later, I had access to mainframes that had as much as 16MB "real" (ha,
> ha) memory and was completely pissed off that the OS designers stole
> much of that for their own buffers of various kinds (some were even
> buffers for program code).
> Later still, when memory approached 1MB, the same damn thievery
> continued. But I was still telling programmers using a two-process
> architecture to count their logical accesses as one cent, their process
> transfers as ten cents and their physical IOs as a buck. (The ones that
> refused to do this got crappy reviews.)
> Now, memory latency is actually a bigger problem, relatively, than disk
> latency ever was, but the Wintel coalition doesn't talk about it much.
> Meanwhile, the essential aspects of the census for small monarchies like
> Canuckistan could be handled in "main memory" of a consumer machine.
> >> ...
> >>So because time is the difference that makes less physical. Don't you
> >>see anything wrong in that?
> > ...
> Don't see anything wrong, but as far as db is concerned, Cimode's
> observation seems accurate to me.

I am *specifically* speaking about db. I am sorry if some comments may have induced otherwise.

> p
Received on Wed Mar 07 2007 - 10:29:03 CET

Original text of this message