Re: [sqlite] SQlite query performs 10 times slower than MS Access query

[Doyel5]

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, OccurrenceTypeIDINTEGER,PeriodINTEGER,   
>> HasSucceededBOOL, 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
>> (
>> ParticipantIDINTEGER  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
>> (
>> ParticipantGroupIDINTEGER,ParticipantGroupTypeID INTEGER,
>> Descriptionvarchar (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
> 

[sqlite] SQlite query performs 10 times slower than MS Access query

[Doyel5]

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,
OccurrenceTypeIDINTEGER,PeriodINTEGER,HasSucceededBOOL, 
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 
(
SimulationIDINTEGER, SimRunIDINTEGER,OccurrenceID
INTEGER,
RoleTypeID INTEGER, ParticipantIDINTEGER
)

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 
(
ParticipantIDINTEGER  PRIMARY KEY, ParticipantTypeIDINTEGER,
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  
(
ParticipantGroupIDINTEGER,ParticipantGroupTypeID INTEGER,
Descriptionvarchar (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,