Archives of the TeradataForum

Message Posted: Mon, 28 Jul 2008 @ 12:22:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello,

we have decided to experiment with setting FSP parameter, however the test results are quite confusing.

Initial state of each test

- Table with initial load of 215 mil. of records (29GB fastloaded after the FSP parameter was set on table)

Test scenario

- Insert test data to simulate data transformation for 5 continuous production days

- Number of records in each SQL insert - 3,3 mils. (1,5%)

- Packdisk is not run during the test

- Check elapsed time and showfsp results after each insert

Results

1) Table with non partitioned primary index

a) Initial FSP = 0

          Business date|Elapsed Time|FSP at the end of the insert
          26.5.2008|0:04:55|66
          27.5.2008|0:02:51|65
          28.5.2008|0:03:02|65
          29.5.2008|0:03:08|64
          30.5.2008|0:03:16|64

b) Initial FSP = 20

          Business date|Elapsed Time|FSP at the end of the insert
          26.5.2008|0:05:28|51
          27.5.2008|0:04:06|62
          28.5.2008|0:03:46|64
          29.5.2008|0:03:31|65
          30.5.2008|0:03:24|65

2) Table with partitioned primary index

a) Initial FSP = 0

          Business date|Elapsed Time|FSP at the end of the insert
          26.5.2008|0:02:08|39
          27.5.2008|0:01:35|41
          28.5.2008|0:01:36|43
          29.5.2008|0:01:35|44
          30.5.2008|0:01:37|46

b) Initial FSP = 20

Questions:

q1) I know that SQL commands do not respect the FSP parameter, but in these tests we have added only 7,5% of data. Why is the final FSP so high? As i understand it the space reserved by FSP should be used first before allocating new cylinders regadles of the way the data are loaded.

Am I wrong?

q2) Why are the elapsed times for inserts with FSP 0 lower than the times with FSP 20? Space reserved by FSP should improve performance, shouldn't it?

Thanks for your help

Lubomir

	<Prev	Next>		<<First	<Prev	Next>	Last>>