2016-04-22 14:06 GMT+02:00 E.Pasma <pasma10 at concepts.nl>:
>
> 22 apr 2016, Cecil Westerhof:
>
>>
>> ?With createBigTable.sh ...
>>
> Can you paste the svript in the message? Attachments are not sent.
>
?createBigTable.sh:
#/usr/bin/env bash
# An error should terminate the script
# An unset variable is also an error
set -o errexit
set -o nounset
declare -r INSERT_TEMPLATE="INSERT INTO testUniqueUUIDBig
SELECT uuid, %d FROM testUniqueUUID
;
"
declare -r NR_OF_COPIES=10
declare insert=""
function getInsertStr {
printf "${INSERT_TEMPLATE}" "${1}"
}
for i in $(seq "${NR_OF_COPIES}") ; do
insert+="$(getInsertStr ${i})
"
done
sqlite3 checkUUID.sqlite <<EOT
.echo ON
.timer ON
DROP TABLE IF EXISTS testUniqueUUIDBig;
CREATE TABLE testUniqueUUIDBig (
UUID blob,
count int,
PRIMARY KEY(UUID, count)
CHECK(TYPEOF(UUID) = 'blob' AND
LENGTH(UUID) = 16 AND
SUBSTR(HEX(UUID), 13, 1) == '4' AND
SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
)
);
${insert}
EOT
The logging:
.timer ON
DROP TABLE IF EXISTS testUniqueUUIDBig;
Run Time: real 293.257 user 6.708000 sys 28.844000
CREATE TABLE testUniqueUUIDBig (
UUID blob,
count int,
PRIMARY KEY(UUID, count)
CHECK(TYPEOF(UUID) = 'blob' AND
LENGTH(UUID) = 16 AND
SUBSTR(HEX(UUID), 13, 1) == '4' AND
SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
)
);
Run Time: real 0.277 user 0.000000 sys 0.000000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 1 FROM testUniqueUUID
;
Run Time: real 89.930 user 48.872000 sys 28.196000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 2 FROM testUniqueUUID
;
Run Time: real 133.674 user 56.416000 sys 43.032000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 3 FROM testUniqueUUID
;
Run Time: real 269.029 user 59.520000 sys 48.840000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 4 FROM testUniqueUUID
;
Run Time: real 356.622 user 61.196000 sys 51.956000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 5 FROM testUniqueUUID
;
Run Time: real 398.048 user 61.924000 sys 57.540000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 6 FROM testUniqueUUID
;
Run Time: real 413.252 user 61.684000 sys 59.816000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 7 FROM testUniqueUUID
;
Run Time: real 464.911 user 61.672000 sys 63.200000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 8 FROM testUniqueUUID
;
Run Time: real 545.974 user 61.900000 sys 66.916000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 9 FROM testUniqueUUID
;
Run Time: real 695.315 user 64.016000 sys 69.692000
INSERT INTO testUniqueUUIDBig
SELECT uuid, 10 FROM testUniqueUUID
;
Run Time: real 1129.854 user 64.428000 sys 76.704000
performanceTest.sh:
#/usr/bin/env bash
# An error should terminate the script
# An unset variable is also an error
set -o errexit
set -o nounset
declare -r DB=checkUUIDSmall.sqlite
declare -r DEINIT=".timer OFF
.echo OFF"
declare -r INIT=".echo ON
.timer ON"
declare -r TABLE=testUniqueUUID
declare -r TABLE_BIG=testUniqueUUIDBig
declare -r DELETE_AND_DROP="DELETE FROM ${TABLE};
DROP TABLE ${TABLE};
DELETE FROM ${TABLE_BIG};
DROP TABLE ${TABLE_BIG};"
declare -r DROP="DROP TABLE ${TABLE};
DROP TABLE ${TABLE_BIG};"
function cpDB {
giveMessage "Copying database"
rm --force "${DB}"-journal
cp "${DB}".bck "${DB}"
}
function giveMessage {
printf "%s: %s\n" "$(date +%T)" "${1}"
}
cpDB
giveMessage "DROP only"
sqlite3 "${DB}" <<EOT
${INIT}
${DROP}
${DEINIT}
EOT
cpDB
giveMessage "DELETE and DROP"
sqlite3 "${DB}" <<EOT
${INIT}
${DELETE_AND_DROP}
${DEINIT}
EOT
cpDB
giveMessage "SECURE_DELETE=0; DROP only"
sqlite3 "${DB}" <<EOT
${INIT}
PRAGMA SECURE_DELETE=0;
${DROP}
PRAGMA SECURE_DELETE=1;
${DEINIT}
EOT
cpDB
giveMessage "SECURE_DELETE=0; DELETE and DROP"
sqlite3 "${DB}" <<EOT
${INIT}
PRAGMA SECURE_DELETE=0;
${DELETE_AND_DROP}
PRAGMA SECURE_DELETE=1;
${DEINIT}
EOT
giveMessage "Done"
The logging:
23:31:24: Copying database
23:31:50: DROP only
.timer ON
DROP TABLE testUniqueUUID;
Run Time: real 92.543 user 1.216000 sys 6.324000
DROP TABLE testUniqueUUIDBig;
Run Time: real 1474.282 user 12.812000 sys 63.832000
.timer OFF
.echo OFF
23:57:58: Copying database
23:58:23: DELETE and DROP
.timer ON
DELETE FROM testUniqueUUID;
Run Time: real 13.811 user 1.064000 sys 4.556000
DROP TABLE testUniqueUUID;
Run Time: real 0.258 user 0.000000 sys 0.004000
DELETE FROM testUniqueUUIDBig;
Run Time: real 1287.970 user 12.984000 sys 63.412000
DROP TABLE testUniqueUUIDBig;
Run Time: real 0.525 user 0.004000 sys 0.000000
.timer OFF
.echo OFF
00:20:06: Copying database
00:20:32: SECURE_DELETE=0; DROP only
.timer ON
PRAGMA SECURE_DELETE=0;
0
Run Time: real 0.000 user 0.000000 sys 0.000000
DROP TABLE testUniqueUUID;
Run Time: real 75.249 user 0.632000 sys 1.596000
DROP TABLE testUniqueUUIDBig;
Run Time: real 872.330 user 6.900000 sys 15.956000
PRAGMA SECURE_DELETE=1;
1
Run Time: real 0.001 user 0.000000 sys 0.000000
.timer OFF
.echo OFF
00:36:20: Copying database
00:36:45: SECURE_DELETE=0; DELETE and DROP
.timer ON
PRAGMA SECURE_DELETE=0;
0
Run Time: real 0.000 user 0.000000 sys 0.000000
DELETE FROM testUniqueUUID;
Run Time: real 3.085 user 0.452000 sys 0.324000
DROP TABLE testUniqueUUID;
Run Time: real 0.277 user 0.000000 sys 0.000000
DELETE FROM testUniqueUUIDBig;
Run Time: real 209.797 user 5.564000 sys 10.860000
DROP TABLE testUniqueUUIDBig;
Run Time: real 0.243 user 0.000000 sys 0.000000
PRAGMA SECURE_DELETE=1;
1
Run Time: real 0.001 user 0.000000 sys 0.000000
.timer OFF
.echo OFF
00:40:19: Done
--
Cecil Westerhof
