Archives of the TeradataForum
Message Posted: Mon, 28 Jul 2008 @ 12:22:40 GMT
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)
- 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
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 = 20Business date|Elapsed Time|FSP at the end of the insert 26.5.2008|0:01:42|37 27.5.2008|0:02:08|43 28.5.2008|0:01:48|48 29.5.2008|0:01:43|50 30.5.2008|0:01:37|52
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|