The table A3Test7D4_E is deleted and re-written every time. The table ENTRY_ATTRIBUTES is large table (millions of records) and it will need an analyze when made first time. Doesn't look cache is the reason for this behaviour here.
RBS On Wed, Jul 13, 2011 at 1:44 PM, Stephen C <pontia...@gmail.com> wrote: > What happens when you let the query run the first time, delete the > records, then re-run the query? > > If the same length of time is spent (50 seconds) then I'm at a loss. > If the shorter length of time happens, I'd say blame caching. > > On 07/13/2011 06:00 AM, Bart Smissaert wrote: >> Have the following query: >> >> INSERT INTO A3Test7D4_J (PATIENT_ID, ENTRY_ID, READ_CODE, TERM_TEXT, >> ADDED_DATE, START_DATE, NUMERIC_VALUE, TYPE_SPECIFIC_INFO) SELECT >> E.PATIENT_ID, E.ENTRY_ID, E.READ_CODE, E.TERM_TEXT, E.ADDED_DATE, >> E.START_DATE, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM A3Test7D4_E >> E INNER JOIN ENTRY_ATTRIBUTES EA ON (EA.ENTRY_ID = +E.ENTRY_ID) WHERE >> EA.ATTRIBUTE_TYPE = 101 ORDER BY E.PATIENT_ID ASC, E.ENTRY_ID ASC, >> EA.NUMERIC_VALUE ASC >> >> And as it was quite slow (50 secs) I did an analyze on the table >> ENTRY_ATTRIBUTES and after that the query was much faster, less than >> one second. Now, what I don't understand about this is that the query >> plan is still the same: >> >> 0--0--0--SCAN TABLE A3Test7D4_E AS E (~18491 rows)<--> >> 0--1--1--SEARCH TABLE ENTRY_ATTRIBUTES AS EA USING COVERING INDEX >> IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE_ENTRY_ID_TYPE_SPECIFIC_INFO_NUMERIC_VALUE >> (ATTRIBUTE_TYPE=? AND ENTRY_ID=?) (~2 rows)<--> 0--0--0--USE TEMP >> B-TREE FOR ORDER BY >> >> And also the data in sqlite_stat1 is still the same: >> >> IDX_ENTRY_ATTRIBUTES_NUMERIC_VALUE 1389416 176 >> IDX_ENTRY_ATTRIBUTES_TYPE_SPECIFIC_INFO 1389416 9 >> IDX_ENTRY_ATTRIBUTES_ENTRY_ID 1389416 2 >> IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE 1389416 198488 >> IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE_ENTRY_ID_TYPE_SPECIFIC_INFO_NUMERIC_VALUE >> 1389416 >> 198488 2 2 1 >> >> The data in the table is the same as well. >> The other table involved A3Test7D4_E is exactly the same as well. >> So why runs this query so much faster after running the analyze? >> >> >> RBS >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
