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
