We are experiencing incredibly slow delete times when deleting a large number
of rows:-
We are using SQLite on an embdedded platform with an ARM7 processor, 2Mb RAM
and 32Mb NOR Flash memory 25Mb of which is allocated for storage for our
database.
There are 3 tables in the database but the two we are concerned with here are
created as follows:-
CREATE TABLE EXAMINATIONS (
'EXAM_ID' integer PRIMARY KEY AUTOINCREMENT,
'PATIENT_ID' varchar(15) NOT NULL,
'STATUS_FLAG' smallint,
'DATE' timestamp,
'EXAM_TYPE' smallint,
'DATE' timestamp,
'EXAM_TYPE' smallint,
'HEIGHT' smallint,
'WEIGHT' smallint,
'DYSPNOEA_SCORE' smallint,
'NOTES' varchar(450),
FOREIGN KEY (PATIENT_ID) REFERENCES PATIENTS(PATIENT_ID) ON DELETE
CASCADE )
CREATE TABLE SPIRO_TEST(
'TEST_ID' integer PRIMARYKEY AUTOINCREMENT,
'EXAM_ID' integer NOT NULL,
'STATUS_FLAG' smallint,
'TEST_TYPE' smallint,
'DATE' timestamp,
'CONTENT' blob,
FOREIGN KEY (EXAM_ID) REFERENCES EXAMINATIONS(EXAM_ID) ON DELETE
CASCADE )
Note: the following columns are indexed:-
EXAMINATIONS:-
PATIENT_ID
DATE
EXAM_TYPE
STATUS_FLAG
SPIRO_TEST
EXAM_ID
It is taking 6 minutes just to execute the following SQL:-
DELETE FROM SPIRO_TEST
Where SPIRO_TEST contains 11,601 records.
In reality we will never peform the above SQL on the database, we did this only
to test how long it would take to delete all the records from the SPIRO_TEST
table.
We did this as during testing we noticed that when running the following SQL it
took 11 minutes to complete:-
DELETE FROM EXAMINATIONS WHERE DATE < datetime('2005-10-11 00:00:00')
There is a trigger on the SPIRO_TEST table to clear out related records in the
module tables when an exam is deleted as such:-
CREATE TRIGGER MODULE_EXAM_TRIGG
BEFORE DELETE ON EXAMINATIONS
FOR EACH ROW BEGIN
DELETE FROM SPIRO_TEST WHERE EXAM_ID = OLD.EXAM_ID
END;
We have also tried removing this trigger and deleting related records from the
SPIRO_TEST table maunually before deleting the exams but this took 20 minutes
to complete.
We also notice that queries generally run slower when the database contains a
large number of records.
Can anyone please help us determine what the problem is and suggest any fix?
Kind Regards
Mark Allan
PS
The SQL:- DELETE FROM EXAMINATIONS WHERE DATE < datetime('2005-10-11
00:00:00'), when the trigger exists, results in the following opcodes:-
0 Goto 0 112
1 Statement 0 0
2 Integer 0 0
3 OpenRead 2 14 keyinfo(1,BINARY)
4 KeyAsData 2 1
5 SetNumColumns 2 2
6 String8 0 0 11/10/2005 00:00
7 Function 1 1 dateTime(1)
8 NotNull -1 11
9 Pop 1 0
10 Goto 0 21
11 MakeRecord 1 0 n
12 MemStore 0 1
13 Rewind 2 21
14 MemLoad 0 0
15 IdxGE 2 21
16 RowKey 2 0
17 IdxIsNull 1 20
18 IdxRecno 2 0
19 ListWrite 0 0
20 Next 2 14
21 Close 2 0
22 OpenPseudo 0 0
23 SetNumColumns 0 9
24 ListRewind 0 0
25 ListRead 0 110
26 Dup 0 0
27 Integer 0 0
28 OpenRead 1 9 # EXAMINATIONS
29 SetNumColumns 1 9
30 MoveGe 1 0
31 Recno 1 0
32 RowData 1 0
33 PutIntKey 0 0
34 Close 1 0
35 ContextPush 0 0 # begin trigger fkd_SPIRO_TEST_EXAM_ID
36 ResetCount 0 0
37 Integer 0 0
38 OpenRead 4 19 keyinfo(1,BINARY)
39 KeyAsData 4 1
40 SetNumColumns 4 2
41 Recno 0 0
42 NotNull -1 45
43 Pop 1 0
44 Goto 0 55
45 MakeRecord 1 0 i
46 MemStore 1 0
47 MoveGe 4 55
48 MemLoad 1 0
49 IdxGE 4 55 +
50 RowKey 4 0
51 IdxIsNull 1 54
52 IdxRecno 4 0
53 ListWrite 0 0
54 Next 4 48
55 Close 4 0
56 ListRewind 0 0
57 Integer 0 0
58 OpenWrite 3 16 # SPIRO_TEST
59 SetNumColumns 3 6
60 Integer 0 0
61 OpenWrite 4 20 keyinfo(1,BINARY)
62 Integer 0 0
63 OpenWrite 5 19 keyinfo(1,BINARY)
64 ListRead 0 76
65 NotExists 3 75
66 Recno 3 0
67 Column 3 2
68 MakeRecord 1 16777216 i
69 IdxDelete 4 0
70 Recno 3 0
71 Column 3 1
72 MakeRecord 1 16777216 i
73 IdxDelete 5 0
74 Delete 3 1
75 Goto 0 64
76 ListReset 0 0
77 Close 4 20
78 Close 5 19
79 Close 3 0
80 ResetCount 1 0
81 ContextPop 0 0 # end trigger fkd_SPIRO_TEST_EXAM_ID
82 Integer 0 0
83 OpenWrite 1 9 # EXAMINATIONS
84 SetNumColumns 1 9
85 Integer 0 0
86 OpenWrite 2 15 keyinfo(1,BINARY)
87 Integer 0 0
88 OpenWrite 3 14 keyinfo(1,BINARY)
89 Integer 0 0
90 OpenWrite 4 13 keyinfo(1,BINARY)
91 NotExists 1 105
92 Recno 1 0
93 Column 1 4
94 MakeRecord 1 16777216 i
95 IdxDelete 2 0
96 Recno 1 0
97 Column 1 3
98 MakeRecord 1 16777216 n
99 IdxDelete 3 0
100 Recno 1 0
101 Column 1 1
102 MakeRecord 1 16777216 t
103 IdxDelete 4 0
104 Delete 1 1
105 Close 2 15
106 Close 3 14
107 Close 4 13
108 Close 1 0
109 Goto 0 25
110 ListReset 0 0
111 Halt 0 0
112 Transaction 0 1
113 VerifyCookie 0 17
114 Goto 0 1
115 Noop 0 0
DISCLAIMER:
This information and any attachments contained in this email message is
intended only for the use of the individual or entity to which it is addressed
and may contain information that is privileged, confidential, and exempt from
disclosure under applicable law. If the reader of this message is not the
intended recipient, or the employee or agent responsible for delivering the
message to the intended recipient, you are hereby notified that any
dissemination, distribution, forwarding, or copying of this communication is
strictly prohibited. If you have received this communication in error, please
notify the sender immediately by return email, and delete the original message
immediately.
