RE: Some new results

[William Washington III <w.washington3@xxxxxxxxxxxxx>]

Your "something ugly" may have more to do with the fact that SQL may not commit
the updates immediately than the fact that the extra index (or logical view)
exists.  I'd bet that if the either the file maintenance was changed from
*IMMED or the SQL version did a COMMIT after each database change, the timing
ratio would be closer to the one-record results.

SO, the optimal (highest performance) technique might be to use native I/O to a
file with access path maintenance to *DLY, then change the file back after the
process has finished.  Just a thought in need of an experiment.

William 

-
> date: Wed, 11 Aug 2004 16:30:52 -0500
> from: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
> subject: RE: Some new results
>
<<snip>> 
>
> Current results:
> 
>   1   Insert Test - Native I/O               1.77
>   2   Insert Test - SQL-A (VALUES)           6.35
>   3   Insert Test - SQL-B (SINGLE DS)        6.16
>   4   Insert Test - SQL-C (10 ROWS)          1.11
>   5   Insert Test - SQL-C (100 ROWS)          .43
>   6   Update Test - Native I/O               1.61
>   7   Update Test - SQL-A (CURSOR/FETCH)     4.95
>   8   Update Test - SQL-B (UPDATE SINGLE)   11.61
>   9   Update Test - SQL-C (10 ROWS)          5.25
>  10   Update Test - SQL-C (100 ROWS)         2.34
> 
> In the update test, Native I/O beats all flavors of SQL pretty handily,
> even updating 100 rows at a time.  Again, these are highly preliminary
> tests, and if you don't like them, COME HELP FIX THEM!
> 
> http://forums.plutabrothers.com/IAAI
> 
> Joe
> 
> P.S. Something ugly: adding just ONE extra logical view caused the
> native I/O write to slow down a ton.  From .3 seconds to 1.77 seconds.
> It had a milder, but still negative, effect on the SQL as well.
>