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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
