Hi Everybody, Thanks a lot for the suggestions. I really appreciate your help. :-)
Just wanted to share my results.. I deleted all indexes, kept only the indexes on the temporary tables, namely - tempSimArgs and tempPartArgs, and seemingly my original query's running time decreased to around 1 sec (when I ran the query the second time). The execution time of the first time I run my query varies wildly, I dunno why. Any ideas? Thanks in advance! Regards, Saswati Igor Tandetnik wrote: > > Doyel5 <doyel...@gmail.com> wrote: >> I have a 800MB MS Access database that I migrated to SQLite. The >> structure of the database is as follows (the SQLite database, after >> migration, is around 330MB): >> >> The table ‘Occurrence’ has 1,600,000 records. The table looks like: >> CREATE TABLE Occurrence >> ( >> SimulationID INTEGER, SimRunID INTEGER, OccurrenceID >> INTEGER, OccurrenceTypeID INTEGER, Period INTEGER, >> HasSucceeded BOOL, PRIMARY KEY (SimulationID, SimRunID, >> OccurrenceID) ) >> >> It has the following indexes: >> CREATE INDEX "Occurrence_HasSucceeded_idx" ON "Occurrence" >> ("HasSucceeded" ASC) > > Don't create indexes on fields with low selectivity (fields that only have > a small number of distinct values, such as boolean fields). A query like > "select * from Occurrence where HasSucceeded" would likely run faster > doing a full scan than using this index. > >> CREATE INDEX "Occurrence_OccurrenceID_idx" ON "Occurrence" >> ("OccurrenceID" ASC) >> >> CREATE INDEX "Occurrence_SimRunID_idx" ON "Occurrence" ("SimRunID" >> ASC) >> >> CREATE INDEX "Occurrence_SimulationID_idx" ON "Occurrence" >> ("SimulationID" ASC) > > This index is redundant. Your primary key can be used for searches on > SimulationID. > > >> The table ‘InitialParticipant’ has 130 records. The structure of the >> table is >> >> CREATE TABLE InitialParticipant >> ( >> ParticipantID INTEGER PRIMARY KEY, ParticipantTypeID >> INTEGER, ParticipantGroupID INTEGER >> ) >> >> The table has the following indexes: >> CREATE INDEX "initialpart_participantTypeID_idx" ON >> "InitialParticipant" ("ParticipantGroupID" ASC) >> >> CREATE INDEX "initialpart_ParticipantID_idx" ON "InitialParticipant" >> ("ParticipantID" ASC) > > Similarly, this index is redundant. The fact that ParticipantID is a > primary key means it's automatically indexed. > >> The table ‘ParticipantGroup’ has 22 records. It looks like >> CREATE TABLE ParticipantGroup >> ( >> ParticipantGroupID INTEGER, ParticipantGroupTypeID INTEGER, >> Description varchar (50), PRIMARY KEY( ParticipantGroupID ) >> ) >> >> The table has the following index: >> CREATE INDEX "ParticipantGroup_ParticipantGroupID_idx" ON >> "ParticipantGroup" ("ParticipantGroupID" ASC) > > Again, this index is redundant. > >> The query in Access is (the entire query takes 1 second to execute): >> SELECT ParticipantGroup.Description, Occurrence.SimulationID, >> Occurrence.SimRunID, Occurrence.Period, >> Count(OccurrenceParticipant.ParticipantID) AS CountOfParticipantID >> FROM ( >> ParticipantGroup INNER JOIN InitialParticipant ON >> ParticipantGroup.ParticipantGroupID = >> InitialParticipant.ParticipantGroupID ) INNER JOIN >> ( >> tmpPartArgs INNER JOIN >> ( >> ( >> tmpSimArgs INNER JOIN Occurrence ON >> (tmpSimArgs.SimRunID = Occurrence.SimRunID) AND >> (tmpSimArgs.SimulationID = Occurrence.SimulationID) ) INNER >> JOIN OccurrenceParticipant ON (Occurrence.OccurrenceID = >> OccurrenceParticipant.OccurrenceID) AND (Occurrence.SimRunID = >> OccurrenceParticipant.SimRunID) AND (Occurrence.SimulationID = >> OccurrenceParticipant.SimulationID) ) ON >> tmpPartArgs.participantID = OccurrenceParticipant.ParticipantID ) ON >> InitialParticipant.ParticipantID = >> OccurrenceParticipant.ParticipantID WHERE >> (((OccurrenceParticipant.RoleTypeID)=52 Or >> (OccurrenceParticipant.RoleTypeID)=49)) AND Occurrence.HasSucceeded = >> True GROUP BY ParticipantGroup.Description, Occurrence.SimulationID, >> Occurrence.SimRunID, Occurrence.Period; > > I recommend you drop all those parentheses around joins - they only > confuse matters. Just write > > SELECT ParticipantGroup.Description, Occurrence.SimulationID, > Occurrence.SimRunID, Occurrence.Period, > Count(OccurrenceParticipant.ParticipantID) AS CountOfParticipantID > FROM > ParticipantGroup INNER JOIN InitialParticipant ON > (ParticipantGroup.ParticipantGroupID = > InitialParticipant.ParticipantGroupID) > INNER JOIN OccurrenceParticipant ON > (InitialParticipant.ParticipantID = > OccurrenceParticipant.ParticipantID) > INNER JOIN tmpPartArgs ON > (tmpPartArgs.participantID = OccurrenceParticipant.ParticipantID) > INNER JOIN Occurrence ON > (Occurrence.OccurrenceID = OccurrenceParticipant.OccurrenceID) AND > (Occurrence.SimRunID = OccurrenceParticipant.SimRunID) AND > (Occurrence.SimulationID = OccurrenceParticipant.SimulationID) > INNER JOIN tmpSimArgs ON > (tmpSimArgs.SimRunID = Occurrence.SimRunID) AND > (tmpSimArgs.SimulationID = Occurrence.SimulationID) > WHERE > OccurrenceParticipant.RoleTypeID IN (49, 52) AND > Occurrence.HasSucceeded > GROUP BY > ParticipantGroup.Description, Occurrence.SimulationID, > Occurrence.SimRunID, Occurrence.Period; > > >> The SQLite query is as follows (this query takes around 10 seconds): > > Why did you feel the need to rewrite it? This mess of nested selects makes > SQLite's job much harder than it needs to be. > > The original query should have worked out of the box (well, almost - > there's no TRUE keyword in SQLite). In any case, the query shown above > should work fine in both Access and SQLite, and is hopefully easier to > read and undersand. > > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/SQlite-query-performs-10-times-slower-than-MS-Access-query-tp27026977p27366650.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users