Re: Help Data-Types

On Tue, 20 Aug 2002 17:51:09 +0100, Paul Vernon <paul.vernon_at_ukk.ibmm.comm> wrote:

>>>My betting is that a CPU can do bit shifts quicker that getting a 11
>more
>>>bytes from RAM/disk
>
>> Seems reasonable - but is it really so?
>
>Of course it all depends where that data is. The outer edge of a disk when
>the head is at the inner? Well a 32 way SMP clocked at say 2GHz with 4
>execution units in each CPU could theoretically notchup say 4 * 2*10^9 *
>32 / .2 instructions in the 200 milliseconds it takes to retrieve that
>extra data. Even getting stuff from Level2, 3 & 4 RAM takes time.
>Plus you only need to do the bit shifts/arithmetic/string ops when
>displaying results to users. Joins, sorts etc can use the 'internal
>format'.
>On the other hand with a M/F with lots of I/O and not so much CPU, biasing
>internal representations away from those with compute heavy requirements
>can be a sensiable idea (e.g. store DATE TIMEs in BCD not num secs since
>1971).

<blush> after doing some math I can see that my estimate was wrong by more than 4 orders of magnitude.
Thank you for pointing this one out.

If you have patience would you please shed some more light on these calculations (I'd just like to have a clear picture...).

When I was doing my calculations I used following assumptions: 1 CPU 2GHz
HDD system that supplies data at 12 MB/s (I did not consider SMP system because things complicate, and one would need to consider disk arrays or some other technologies, I also did not consider seek times, track to track, nor track begining to end since I am not sure that this applies. Considering that what ever is the HDD time penalty for getting 4 bytes, I don't think that you will get another seek penalty for getting 11 bytes more - just the sequential read penalty. Is this reasoning wrong? If so, why?)

Now:
Time needed to read 4 bytes of data is:
x + 4 / ( 2 ^ 20 * 12 ) = x + 3.17 * 10^-7 s

Where x is unknown time of HDD time penalty for getting 4 bytes

Time needed to read 15 bytes of data is: x + 15 / (2 ^ 20 * 12 )

Time needed to read additional 11 bytes is then: 11 / ( 2 ^ 20 * 12 ) = 8.74 * 10^-7 s

In this time 2GHz processor can do additional 2 * 10^9 * 11 / ( 2 ^ 20 * 12 ) = 1748 cycles

Still, I don't know the answer on the following questions: In this extra amount of CPU cycles enough to do a funciton like F.IPV4DSPLY? What is min percentage of cycles eaten by OS and database engine? In what form are user defined functions in SQL executed? I mean what is the avg ratio beetween basic functions and operators in SQL and processor cycles (to what is it compiled to? what's the overhead?)?

all the best...

( GoranG79 AT hotmail.com ) Received on Thu Aug 22 2002 - 08:37:44 CEST