On 4/10/15, Hamish Symington <info at hamishsymington.com> wrote: > Hi there, > > 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. > > Hamish > >> On 10 Apr 2015, at 14:24, Hamish Symington <info at hamishsymington.com> >> wrote: >> >> Hello, >> >> I have a curiously slow query, and I?m not sure if it?s caused by >> something I?m doing (most likely) or by something odd in sqlite (almost >> certainly not). >> >> The tables we have are: >> Appointment(Appointment_UUID, Appointment_TxnUUID, Appointment_StartDate, >> and other columns) >> Txn(Txn_UUID, Txn_Created, Txn_Reference amongst others). >> TxnCalc(TxnCalc_TxnUUID, TxnCalc_Date amongst others). >> >> The query I am running is this (using a prepared statement): >> >> SELECT TxnCalc_TxnUUID AS Txn_UUID >> FROM ( >> SELECT Txn.Txn_UUID AS TempTxn_UUID >> FROM Txn >> LEFT JOIN Appointment ON Appointment_TxnUUID=Txn_UUID >> WHERE ( date( Appointment_StartDate ) >= trim( ? ) AND date( >> Appointment_StartDate ) <= trim( ? ) ) ) >> AS SubQueryResult, >> TxnCalc, >> Txn >> WHERE TxnCalc_TxnUUID=TempTxn_UUID >> AND TxnCalc_TxnUUID=Txn_UUID >> GROUP BY TxnCalc_TxnUUID >> ORDER BY TxnCalc_Date DESC, >> Txn_Reference DESC, >> Txn_Created DESC >> >> If possible, I?d like you to ignore the structure of the query, which >> isn?t quite as good as it could be, I know; it?s the next bit that?s >> curious. >> >> We have indexes on Txn_UUID, TxnCalc_TxnUUID, Appointment_UUID - nothing >> controversial there - and also on Appointment_TxnUUID. That field has >> many, many blank values (as Appointments are most often linked to other >> things) and only a very few non-blank values. They?re blank (ie ??) and >> not NULL, if that makes a difference. >> >> When running that query using SQLite 3.7.14.1, it takes around 230ms to >> run. The query plan is this: >> SCAN TABLE Txn (~3670 rows) >> SEARCH TABLE Appointment USING AUTOMATIC COVERING INDEX >> (Appointment_TxnUUID=?) (~5 rows) >> SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?) >> (~1 rows) >> SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?) (~1 rows) >> USE TEMP B-TREE FOR GROUP BY >> USE TEMP B-TREE FOR ORDER BY >> Interestingly, it?s not using the index which we created for the SEARCH >> TABLE Appointment. >> >> When running the query using SQLite 3.8.8, it takes around 1600ms to run. >> The query plan is this: >> SCAN TABLE Txn USING COVERING INDEX idx_Txn_UUID >> SCAN TABLE Appointment >> SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?) >> SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?) >> USE TEMP B-TREE FOR GROUP BY >> USE TEMP B-TREE FOR ORDER BY >> ie no index on Appointment at all. >> >> Now for the curious bit. If I drop the index which I made, and recreate it >> using the exact same code with which I created it when I made my database >> - CREATE INDEX idx_Appointment_TxnUUID ON Appointment(Appointment_TxnUUID) >> - using sqlite 3.7.14.1 the query is down to 12ms, and using 3.8.8, it?s >> down to 4ms. >> >> With the database in its ?slow? state, running ANALYZE makes no >> difference. Likewise, REINDEX idx_Appointment_TxnUUID makes no difference. >> >> >> Background: I create the database, make the indexes, and then do a bunch >> of INSERTs into it, along with UPDATES, DELETES etc. - basically using it >> as a normal workhorse database. >> >> So, the question, after this somewhat long explanation: should deleting >> and recreating an index like this cause such a massive performance >> improvement? If so, why? What am I doing wrong in the first place? >> >> Thanks in advance for your assistance, >> >> Hamish >> >> -- >> Hamish Symington >> 07815 081282 : info at hamishsymington.com >> >> I also set custom cryptic crosswords: www.customcrypticcrosswords.com >> (@CustomCryptics on Twitter) >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > -- > Hamish Symington > 07815 081282 : info at hamishsymington.com > > I also set custom cryptic crosswords: www.customcrypticcrosswords.com > (@CustomCryptics on Twitter) > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
