On 25 May 2012, at 7:38pm, Udi Karni <uka...@gmail.com> wrote: > I am running the following query - > > CREATE TABLE XYZ AS > SELECT ID, MIN (DATE) > FROM SOURCE-TABLE > WHERE CRITERIA > GROUP BY ID ; > > SOURCE-TABLE has 600 million rows, 2 million meet the WHERE criteria, and > get grouped to 100,000 distinct IDs. There are no indexes as there is too > much variety in the WHERE clauses and index creation on this table takes > many hours. Temp_store=memory. When executed - the source table is read > very slowly, and runtime is 1/2 hour. > > I split this SQL into 2 steps - the first just extracting - the second just > grouping. The extract read the table extremely fast and finished in 3 > minutes. The GROUP BY ran in a few seconds. So a 10X improvement - which is > pretty incredible speed.
Just for testing purposes, change MIN(DATE) to DATE and check the timings. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
