Table 1
Minute and second data table structure of specific database.
| Field name | Primary key | Field type | Example of value | Description of value |
|---|---|---|---|---|
| startDate | √ | date | 2006-02-23 | One record per day |
| stationID | √ | Char(5) | 44010 | National unified 5-digit station ID |
| pointID | √ | Char(1) | 4 | Positioned to each set of instrument |
| itemID | √ | Char(4) | 3125 | D, H, Z, F, I, X, Y |
| sampleRate | Char(2) | 01 | 01: Minute data, 02: Second data | |
| obsValue | Clob | 86.73 86.23 … 89.12 | The data is separated by a space character, 1440 minute data and 86400 second data are observed per day. |
Table 2
Average compression rate of every record.
| Data | Compression algorithm | Pre-compression capacity (KB) | Compressed clob capacity (KB) | Compressed blob capacity (KB) | Compression time (s) | Decompression time (s) | Clob compression rate (%) | Blob compression rate (%) |
|---|---|---|---|---|---|---|---|---|
| Second data | Bzip2 | 588.51 | 37.462 | 28.096 | 0.18909 | 0.02016 | 6.24 | 4.68 |
| Gzip | 588.51 | 63.376 | 47.531 | 0.03370 | 0.00692 | 10.45 | 7.84 | |
| GzipIO | 588.51 | 86.906 | 65.179 | 0.01328 | 0.00633 | 14.36 | 10.77 | |
| Minute data | Bzip2 | 8.842 | 0.961 | 0.720 | 0.00458 | 0.00113 | 10.89 | 8.15 |
| Gzip | 8.842 | 1.399 | 1.048 | 0.00100 | 0.00034 | 15.76 | 11.81 | |
| GzipIO | 8.842 | 2.008 | 1.505 | 0.00035 | 0.00027 | 22.62 | 16.95 |
[i] Note: The black bold characters refer to smallest compressed capacity, shortest compression and decompression time, and highest compression rate.
Table 3
Average data exchange speed test of every record.
| Field type | Compression algorithm | Second data | Minute data | ||||||
|---|---|---|---|---|---|---|---|---|---|
| Capacity of every record (KB) | Estimated exchange rate | Actual exchange time (s) | Actual exchange rate | Capacity of every ecord (KB) | Estimated exchange rate | Actual exchange time (s) | Actual exchange rate | ||
| Blob (Binary) | Bzip2 | 28.09 | 20.9 | 0.051 | 8.97 | 0.72 | 12.3 | 0.043 | 1.13 |
| Gzip | 47.53 | 12.4 | 0.058 | 7.89 | 1.05 | 8.4 | 0.044 | 1.11 | |
| GzipIO | 65.18 | 9.0 | 0.064 | 7.14 | 1.50 | 5.9 | 0.044 | 1.09 | |
| Uncompressed | 588.51 | 1.0 | 0.248 | 1.84 | 8.84 | 1.0 | 0.045 | 1.06 | |
| Clob (Ascii) | Bzip2 | 37.46 | 15.7 | 0.068 | 6.75 | 0.96 | 9.2 | 0.045 | 1.06 |
| Gzip | 63.38 | 9.3 | 0.083 | 5.49 | 1.40 | 6.3 | 0.045 | 1.08 | |
| GzipIO | 86.91 | 6.8 | 0.106 | 4.30 | 2.01 | 4.4 | 0.046 | 1.05 | |
| Uncompressed | 588.51 | 0.456 | 8.84 | 0.048 | |||||
Table 4
Read-write speed test result of four storage structures.
| An instrument (6 elements) Read and Write times(second) | Second data | Minute data | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 day | 31 day | 31 day | |||||||||||
| Local | Remote | Local | Remote | Local | Remote | ||||||||
| Field type | Compression algorithm | Read(s) | Write(s) | Read(s) | Write(s) | Read(s) | Write(s) | Read(s) | Write(s) | Read(s) | Write(s) | Read(s) | Write(s) |
| Blob (Binary) | Bzip2 | 0.20 | 1.21 | 1.73 | 2.92 | 5.36 | 44.43 | 39.98 | 96.32 | 1.74 | 2.32 | 26.76 | 51.93 |
| Gzip | 0.11 | 0.23 | 1.64 | 2.06 | 2.91 | 6.88 | 39.52 | 63.91 | 1.48 | 1.37 | 26.71 | 49.96 | |
| GzipIO | 0.12 | 0.14 | 1.87 | 2.04 | 3.10 | 4.11 | 45.82 | 63.02 | 1.56 | 1.23 | 26.75 | 49.95 | |
| Uncompressed | 0.36 | 0.37 | 4.63 | 6.43 | 11.02 | 12.28 | 194.44 | 200.60 | 0.97 | 1.40 | 25.83 | 50.39 | |
| Clob (Ascii) | Bzip2 | 1.03 | 1.23 | 17.15 | 3.01 | 28.54 | 44.72 | 556 | 105.63 | 3.07 | 2.14 | 43.42 | 51.53 |
| Gzip | 1.47 | 0.24 | 28.24 | 2.13 | 42.52 | 7.48 | 944 | 76.76 | 2.83 | 1.41 | 46.67 | 50.38 | |
| GzipIO | 1.95 | 0.16 | 38.21 | 2.22 | 57.29 | 4.97 | 1278 | 76.71 | 2.69 | 1.33 | 55.21 | 50.14 | |
| Uncompressed | 11.94 | 0.39 | 243.70 | 6.58 | 372.52 | 12.58 | 7568 | 205.56 | 6.25 | 1.58 | 139.15 | 50.88 | |
[i] Note: Read time = Database read time + Decompression time, Write time = Compression time + Database write time. The black bold characters refer to the highest read-write speed in the four storage structures.
Table 5
Read speed test result of three LOB read methods.