Re: Anyone care to vet / explain this test result?

On Tue, 16 Mar 2004 13:57:27 -0600, Ed Stevens <nospam_at_noway.nohow> wrote:

>Platform: Oracle 8.1.7 on Win2k
>
>Goal: demonstrate effect (or lack of) of the placement of the primary
>key within a row def.

<snip>
>Then, just to make things easier, I wrap the above test code in an
>outer loop: (note the loop controlled by the variable 'x')
>
>DECLARE

<snip>
>BEGIN
> SELECT COUNT(*)
> INTO rn
> FROM EDS_TEST_KEY_FIRST;
>
> dbms_output.put_line('Key First Key Last');
>
>
> i := dbms_utility.get_time();

 Surely this needs to be INSIDE the loop.

> for x in 1 .. 20
> loop
> FOR i IN REVERSE 1 .. rn
> LOOP
> SELECT clm_key
> INTO s
> FROM EDS_TEST_KEY_FIRST
> WHERE SUPL_KEY = i;
> END LOOP;
>
> kf_tme := to_char(dbms_utility.get_time()-i);

 From the second iteration onwards, you're timing how long it takes to do BOTH statements, as 'i' only ever gets set below.
>
> i := dbms_utility.get_time();
>
> FOR i IN REVERSE 1 .. rn
> LOOP
> SELECT clm_key
> INTO s
> FROM EDS_TEST_KEY_LAST
> WHERE SUPL_KEY = i;
> END LOOP;
>
> kl_tme := to_char(dbms_utility.get_time()-i);
>
> dbms_output.put_line(kf_tme || ' ' || kl_tme);
> END LOOP;
>END;
>/
>
>But look at the results with the
>second version, where the test itself is wrapped in a loop:
>
>161 184
>366 182
>364 184
>365 185
>366 184

 First iteration is timed correctly - you reset 'i' before the loop. Second iteration onwards is doubled up.

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>