Why you change the query? Use de Access version or someone without joining a subquery.
Is preferible to join table to table (to more tables) than join table to subqueries. Emilio Doyel5 escribió: > 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) > > 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) > > > > The table ‘OccurrenceParticipant’ has 3,400,000 records. The table looks > like: > CREATE TABLE OccurrenceParticipant > ( > SimulationID INTEGER, SimRunID INTEGER, OccurrenceID > INTEGER, > RoleTypeID INTEGER, ParticipantID INTEGER > ) > > It has the following indexes: > CREATE INDEX "OccurrenceParticipant_OccurrenceID_idx" ON > "OccurrenceParticipant" ("OccurrenceID" ASC) > > CREATE INDEX "OccurrenceParticipant_ParticipantID_idx" ON > "OccurrenceParticipant" ("ParticipantID" ASC) > > CREATE INDEX "OccurrenceParticipant_RoleType_idx" ON "OccurrenceParticipant" > ("RoleTypeID" ASC) > > CREATE INDEX "OccurrenceParticipant_SimRunID_idx" ON "OccurrenceParticipant" > ("SimRunID" ASC) > > CREATE INDEX "OccurrenceParticipant_SimulationID_idx" ON > "OccurrenceParticipant" ("SimulationID" ASC) > 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) > > > > 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) > > > > The table ‘tmpSimArgs’ has 18 records. It has the following structure: > CREATE TABLE tmpSimArgs (SimulationID varchar, SimRunID int(10)) > > And the following indexes: > CREATE INDEX tmpSimArgs_SimRunID_idx ON tmpSimArgs(SimRunID ASC) > > CREATE INDEX tmpSimArgs_SimulationID_idx ON tmpSimArgs(SimulationID ASC) > > > > The table ‘tmpPartArgs’ has 80 records. It has the below structure: > CREATE TABLE tmpPartArgs(participantID INT) > > And the below index: > CREATE INDEX tmpPartArgs_participantID_idx ON tmpPartArgs(participantID ASC) > > I have a query that involves multiple INNER JOINs and the problem I am > facing is the Access version of the query takes about a second whereas the > SQLite version of the same query takes 10 seconds (about 10 times slow!) It > is impossible for me to migrate back to Access and SQLite is my only option. > > I am new to writing database queries so these queries might look stupid, so > please advise on anything you see faulty or kid-dish. > > 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; > > > The SQLite query is as follows (this query takes around 10 seconds): > SELECT ij1.Description, ij2.occSimulationID, ij2.occSimRunID, ij2.Period, > Count(ij2.occpParticipantID) AS CountOfParticipantID FROM > ( > SELECT ip.ParticipantGroupID AS ipParticipantGroupID, ip.ParticipantID > AS ipParticipantID, ip.ParticipantTypeID, pg.ParticipantGroupID AS > pgParticipantGroupID, pg.ParticipantGroupTypeID, pg.Description FROM > ParticipantGroup as pg INNER JOIN InitialParticipant AS ip ON > pg.ParticipantGroupID = ip.ParticipantGroupID > ) AS ij1 INNER JOIN > ( > SELECT tpa.participantID AS tpaParticipantID, ij3.* FROM tmpPartArgs AS > tpa INNER JOIN > ( > SELECT ij4.*, occp.SimulationID as occpSimulationID, > occp.SimRunID AS occpSimRunID, occp.OccurrenceID AS occpOccurrenceID, > occp.ParticipantID AS occpParticipantID, occp.RoleTypeID FROM > ( > SELECT tsa.SimulationID AS tsaSimulationID, > tsa.SimRunID AS tsaSimRunID, occ.SimulationID AS occSimulationID, > occ.SimRunID AS occSimRunID, occ.OccurrenceID AS occOccurrenceID, > occ.OccurrenceTypeID, occ.Period, occ.HasSucceeded FROM tmpSimArgs AS tsa > INNER JOIN Occurrence AS occ ON (tsa.SimRunID = occ.SimRunID) AND > (tsa.SimulationID = occ.SimulationID) > ) AS ij4 INNER JOIN OccurrenceParticipant AS occp ON > (occOccurrenceID = occpOccurrenceID) AND (occSimRunID = occpSimRunID) > AND (occSimulationID = occpSimulationID) > ) AS ij3 ON tpa.participantID = ij3.occpParticipantID > ) AS ij2 ON ij1.ipParticipantID = ij2.occpParticipantID WHERE > (((ij2.RoleTypeID)=52 Or (ij2.RoleTypeID)=49)) AND ij2.HasSucceeded = 1 > GROUP BY ij1.Description, ij2.occSimulationID, ij2.occSimRunID, ij2.Period; > > > > I don’t know what I am doing wrong here. I have all the indexes (i.e. > according to me) but I thinking I am missing the declaration of some key > index that will do the trick for me. The interesting thing is before > migration my ‘research’ on SQLite showed that SQLite is faster, smaller and > better in all aspects than MS Access. But I cant seem to get SQLite work > faster than Access in terms of querying. I reiterate that I am new to SQLite > and obviously do not have much idea as well as experience so if any learned > soul could help me out with this, it will be much appreciated. Thanks in > advance! > > Regards, > Saswati > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users