On 2016/04/16 3:39 PM, Cecil Westerhof wrote:
> 2016-04-16 14:52 GMT+02:00 R Smith <rsmith at rsweb.co.za>:
>
>> Let me try the 100 million rows, this may take some time - I will post
>> again when it is done.
>>
> ?I am curious.
Well, here it is then, 100-million rows: The INSERT took a lot of time,
near 5 hours, but again, the DROP was only a few seconds.
The journal mode used here is also DELETE - which is quite fast. I am
not quite sure why your DROP would take that long. I think that this
exercise was large enough to reproduce any Cache-related problems on my
system too, so I'm not suspecting that anymore. Perhaps someone else
might have a thought....
-- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 4 Parameter Count: 0
-- 2016-04-16 14:52:07.639 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE testUniqueUUID (
UUID BLOB PRIMARY KEY
);
-- Item Stats: Item No: 1 Query Size
(Chars): 62
-- VM Work Steps: 43 Rows
Modified: 0
-- Full Query Time: 0d 00h 00m and 00.001s
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
WITH RndGen(i,RndBlob) AS (
SELECT 0, (randomblob(16))
UNION ALL
SELECT i+1, (randomblob(16)) FROM RndGen WHERE i<100000000
)
INSERT INTO testUniqueUUID (UUID) SELECT RndBlob FROM RndGen;
-- Item Stats: Item No: 2 Query Size
(Chars): 200
-- Result Columns: 0 Result Rows: 0
-- VM Work Steps: -1394967246 Rows
Modified: 100000001
-- Full Query Time: 0d 04h 56m and 56.963s
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
SELECT UUID FROM testUniqueUUID LIMIT 10;
-- UUID
-- ------------------------------------
-- 0x504D9DE6FF67E1BB868E0A2C66C0BCD6
-- 0x85F00A6BECC22F7247569B38EE38FA4C
-- 0x2129D4229A9541F911FD7078C6F58885
-- 0xC07C44C16CBAF4447348EF9058ADCEB4
-- 0xF71DA6C5A845A8AA84561D42E06FA986
-- 0x3F36F6EAC1D9B02D3C96B2678E990F11
-- 0xF127EC52E4C915A7D5B136B5B0999E6A
-- 0x4660D0FC0BA0D860E1578372EEFD664E
-- 0xB73F66F8791C79F62A94529389B55641
-- 0x99E266627DC455E49E604858074629E6
-- Item Stats: Item No: 3 Query Size
(Chars): 43
-- Result Columns: 1 Result Rows: 10
-- VM Work Steps: 48 Rows
Modified: 0
-- Full Query Time: -- --- --- --- --.----
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
DROP TABLE testUniqueUUID;
-- Item Stats: Item No: 4 Query Size
(Chars): 28
-- VM Work Steps: 149 Rows
Modified: 0
-- Full Query Time: 0d 00h 00m and 08.052s
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
-- Script Stats: Total Script Execution Time: 0d 04h 57m and
05.231s
-- Total Script Query Time: 0d 04h 57m and
05.016s
-- Total Database Rows Changed: 100000001
-- Total Virtual-Machine Steps: -1394967006
-- Last executed Item Index: 4
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2016-04-16 19:49:12.857 | [Success] Script Success.
