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
--
View this message in context:
http://old.nabble.com/SQlite-query-performs-10-times-slower-than-MS-Access-query-tp27026977p27026977.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
