Hello,
> On 10 Apr 2015, at 15:38, Hamish Symington <info at hamishsymington.com>
> wrote:
>
>>> A follow up to this. If I run ANALYZE on the ?fast? version of the database
>>> - ie *after* I?ve recreated the index - performance drops back to the
>>> original slow speed.
>>
>> Please run ".fullschema" using the sqlite3.exe command-line shell on
>> your original database and send us the output.
I've trimmed down the tables, triggers and indexes to a much smaller database;
the problem still persists.
The result of .fullschema is as follows:
CREATE TABLE Txn (Txn_ID integer NOT NULL PRIMARY KEY, Txn_UUID varchar NOT
NULL DEFAULT '', Txn_Modified integer NOT NULL DEFAULT 0, Txn_Created timestamp
NOT NULL DEFAULT '', Txn_Type integer NOT NULL DEFAULT 0, Txn_Type_m integer
NOT NULL DEFAULT 0, Txn_ContactUUID varchar NOT NULL DEFAULT '',
Txn_ContactUUID_m integer NOT NULL DEFAULT 0, Txn_NameFull varchar NOT NULL
DEFAULT '' COLLATE NOCASE, Txn_NameFull_m integer NOT NULL DEFAULT 0,
Txn_Company varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Company_m integer
NOT NULL DEFAULT 0, Txn_Address1 varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Txn_Address1_m integer NOT NULL DEFAULT 0, Txn_Address2 varchar NOT NULL
DEFAULT '' COLLATE NOCASE, Txn_Address2_m integer NOT NULL DEFAULT 0,
Txn_Address3 varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Address3_m integer
NOT NULL DEFAULT 0, Txn_City varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Txn_City_m integer NOT NULL DEFAULT 0, Txn_County varchar NOT NULL DEFAULT ''
COLLATE NOCASE, Txn_County_m integer NOT NULL DEFAULT 0, Txn_Postcode varchar
NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Postcode_m integer NOT NULL DEFAULT 0,
Txn_Country varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Country_m integer
NOT NULL DEFAULT 0, Txn_Date date NOT NULL DEFAULT '', Txn_Date_m integer NOT
NULL DEFAULT 0, Txn_Amount currency NOT NULL DEFAULT 0, Txn_Amount_m integer
NOT NULL DEFAULT 0, Txn_Method varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Txn_Method_m integer NOT NULL DEFAULT 0, Txn_Reference integer NOT NULL DEFAULT
0, Txn_Reference_m integer NOT NULL DEFAULT 0, Txn_PaymentReference varchar NOT
NULL DEFAULT '' COLLATE NOCASE, Txn_PaymentReference_m integer NOT NULL DEFAULT
0, Txn_PaidInDate date NOT NULL DEFAULT '', Txn_PaidInDate_m integer NOT NULL
DEFAULT 0, Txn_VATReportDate date NOT NULL DEFAULT '', Txn_VATReportDate_m
integer NOT NULL DEFAULT 0, Txn_Notes varchar NOT NULL DEFAULT '' COLLATE
NOCASE, Txn_Notes_m integer NOT NULL DEFAULT 0);
CREATE INDEX idx_Txn_UUID ON Txn (Txn_UUID);
CREATE INDEX idx_Txn_ContactUUID ON Txn (Txn_ContactUUID);
CREATE INDEX idx_Txn_Reference ON Txn (Txn_Reference);
CREATE TABLE Appointment (Appointment_ID integer NOT NULL PRIMARY KEY,
Appointment_UUID varchar NOT NULL DEFAULT '', Appointment_Modified integer NOT
NULL DEFAULT 0, Appointment_Created timestamp NOT NULL DEFAULT '',
Appointment_AppointmentTypeUUID varchar NOT NULL DEFAULT '',
Appointment_AppointmentTypeUUID_m integer NOT NULL DEFAULT 0,
Appointment_StartDate date NOT NULL DEFAULT '', Appointment_StartDate_m integer
NOT NULL DEFAULT 0, Appointment_StartTime timestamp NOT NULL DEFAULT '',
Appointment_StartTime_m integer NOT NULL DEFAULT 0, Appointment_EndTime
timestamp NOT NULL DEFAULT '', Appointment_EndTime_m integer NOT NULL DEFAULT
0, Appointment_AddressUUID varchar NOT NULL DEFAULT '',
Appointment_AddressUUID_m integer NOT NULL DEFAULT 0, Appointment_Description
varchar NOT NULL DEFAULT '' COLLATE NOCASE, Appointment_Description_m integer
NOT NULL DEFAULT 0, Appointment_ShootUUID varchar NOT NULL DEFAULT '',
Appointment_ShootUUID_m integer NOT NULL DEFAULT 0, Appointment_SessionUUID
varchar NOT NULL DEFAULT '', Appointment_SessionUUID_m integer NOT NULL DEFAULT
0, Appointment_SessionStartOrEnd varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Appointment_SessionStartOrEnd_m integer NOT NULL DEFAULT 0,
Appointment_ContactUUID varchar NOT NULL DEFAULT '', Appointment_ContactUUID_m
integer NOT NULL DEFAULT 0, Appointment_SaleUUID varchar NOT NULL DEFAULT '',
Appointment_SaleUUID_m integer NOT NULL DEFAULT 0, Appointment_QuoteUUID
varchar NOT NULL DEFAULT '', Appointment_QuoteUUID_m integer NOT NULL DEFAULT
0, Appointment_PurchaseUUID varchar NOT NULL DEFAULT '',
Appointment_PurchaseUUID_m integer NOT NULL DEFAULT 0, Appointment_TxnUUID
varchar NOT NULL DEFAULT '', Appointment_TxnUUID_m integer NOT NULL DEFAULT 0,
Appointment_ResourceUUID varchar NOT NULL DEFAULT '',
Appointment_ResourceUUID_m integer NOT NULL DEFAULT 0, Appointment_iCalUID
varchar NOT NULL DEFAULT '' COLLATE NOCASE, Appointment_iCalUID_m integer NOT
NULL DEFAULT 0, Appointment_iCalMD5 varchar NOT NULL DEFAULT '' COLLATE NOCASE,
Appointment_iCalMD5_m integer NOT NULL DEFAULT 0, Appointment_SaleItemUUID
varchar NOT NULL DEFAULT '', Appointment_SaleItemUUID_m integer NOT NULL
DEFAULT 0, Appointment_GoogleCalendarUID varchar NOT NULL DEFAULT '' COLLATE
NOCASE, Appointment_GoogleCalendarUID_m integer NOT NULL DEFAULT 0);
CREATE INDEX idx_Appointment_UUID ON Appointment (Appointment_UUID);
CREATE INDEX idx_Appointment_AddressUUID ON Appointment
(Appointment_AddressUUID);
CREATE INDEX idx_Appointment_ShootUUID ON Appointment (Appointment_ShootUUID);
CREATE INDEX idx_Appointment_SessionUUID ON Appointment
(Appointment_SessionUUID);
CREATE INDEX idx_Appointment_ContactUUID ON Appointment
(Appointment_ContactUUID);
CREATE INDEX idx_Appointment_SaleUUID ON Appointment (Appointment_SaleUUID);
CREATE INDEX idx_Appointment_QuoteUUID ON Appointment (Appointment_QuoteUUID);
CREATE INDEX idx_Appointment_PurchaseUUID ON Appointment
(Appointment_PurchaseUUID);
CREATE INDEX idx_Appointment_ResourceUUID ON Appointment
(Appointment_ResourceUUID);
CREATE INDEX idx_Appointment_SaleItemUUID ON Appointment
(Appointment_SaleItemUUID);
);nCalc_GroupHeadPhone NOT NULL DEFAULT ''T '',
CREATE INDEX idx_TxnCalc_TxnUUID ON TxnCalc(TxnCalc_TxnUUID);
CREATE INDEX idx_TxnCalc_ContactUUID ON TxnCalc(TxnCalc_ContactUUID);
CREATE INDEX idx_appointment_txnuuid on appointment(Appointment_TxnUUID);
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('TxnCalc','idx_TxnCalc_ContactUUID','3670 4');
INSERT INTO sqlite_stat1 VALUES('TxnCalc','idx_TxnCalc_TxnUUID','3670 1');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_appointment_txnuuid','1716
1716');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_SaleItemUUID','1716 1716');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_ResourceUUID','1716 1716');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_PurchaseUUID','1716 1716');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_Appointment_QuoteUUID','1716
1716');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_Appointment_SaleUUID','1716
1716');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_ContactUUID','1716 31');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_SessionUUID','1716 1716');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_Appointment_ShootUUID','1716
2');
INSERT INTO sqlite_stat1
VALUES('Appointment','idx_Appointment_AddressUUID','1716 429');
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_Appointment_UUID','1716 1');
INSERT INTO sqlite_stat1 VALUES('Txn','idx_Txn_Reference','3670 1');
INSERT INTO sqlite_stat1 VALUES('Txn','idx_Txn_ContactUUID','3670 4');
INSERT INTO sqlite_stat1 VALUES('Txn','idx_Txn_UUID','3670 1');
ANALYZE sqlite_master;
In the middle of all that, after the line
CREATE INDEX idx_Appointment_SaleItemUUID ON Appointment
(Appointment_SaleItemUUID);
is some slightly odd output - that's just as it appears in the command line
output in Terminal on my Mac. If you need to know what TxnCalc actually is,
then it's this:
CREATE TABLE TxnCalc (TxnCalc_TxnUUID VARCHAR, TxnCalc_UnallocatedSum currency,
TxnCalc_AllocatedSum currency, TxnCalc_Amount currency NOT NULL DEFAULT 0.00,
TxnCalc_Type NOT NULL DEFAULT '', TxnCalc_Date date NOT NULL DEFAULT '',
TxnCalc_Method NOT NULL DEFAULT '', TxnCalc_NameFull NOT NULL DEFAULT '',
TxnCalc_Company NOT NULL DEFAULT '', TxnCalc_ContactUUID NOT NULL DEFAULT '',
TxnCalc_Year NOT NULL DEFAULT '', TxnCalc_Month NOT NULL DEFAULT '',
TxnCalc_Email NOT NULL DEFAULT '', TxnCalc_Phone NOT NULL DEFAULT '',
TxnCalc_Website NOT NULL DEFAULT '', TxnCalc_GroupHeadEmail NOT NULL DEFAULT
'', TxnCalc_GroupHeadPhone NOT NULL DEFAULT '');
For the avoidance of doubt, the output above is from when the query is slow. If
I drop idx_appointment_txnuuid and recreate it, then the query is fast; the
line
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_appointment_txnuuid','1716
1716');
is then missing from the .fullschema output, but all the rest (including the
line which creates the index) is the same.
If I then run ANALYZE and run .fullschema again, the line
INSERT INTO sqlite_stat1 VALUES('Appointment','idx_appointment_txnuuid','1716
1716');
has reappeared.
Hopefully this will give some of you who know more about SQLite than I do
something to go on!
Best wishes,
Hamish
