SQLite query does not finish execution whereas Access takes a minute to run

Question

I am stuck very badly at trying to make an SQLite query finish execution and currently have no bright ideas. Also, I am new to the field of writing SQL queries, and I am still learning the concept of database indexes, etc..

I am migrating an application from Access to SQLite and the query takes around 1 minute to run in Access. The query is given below (also I cannot migrate back to Access from SQLite):

    SELECT pgDescription, oSimulationID, oSimRunID, ipParticipantID, oPeriod, 
Some_Aggregation_Function(dpVal) FROM 

        (
          SELECT pgDescription, oSimulationID, oSimRunID, ipParticipantID, oPeriod, dpVal, 
ptPropertyTypeName FROM tmpPartArgs INNER JOIN 

            (
              SELECT pgDescription, oSimulationID, oSimRunID, ipParticipantID, 
oPeriod, dpVal, dpParticipantID, ptPropertyTypeName FROM 

                (
                  SELECT o.SimulationID AS oSimulationID, o.SimRunID AS oSimRunID, 
o.Period AS oPeriod, dpVal, dpParticipantID, ptPropertyTypeName FROM Occurrence AS o INNER JOIN 

                    (
                      SELECT * FROM 
                        (
                           SELECT dp.Val AS dpVal, dp.SimulationID AS 
dpSimulationID, dp.SimRunID AS dpSimRunID,dp.ParticipantID AS dpParticipantID, 
dp.OccurrenceID AS dpOccurrenceID, pt.PropertyTypeName AS ptPropertyTypeName FROM
 PropertyType AS pt INNER JOIN DynamicProperty AS dp ON pt.PropertyTypeID =  dp.PropertyTypeID
                        ) AS query0 WHERE query0.ptPropertyTypeName = \"" + args.propertyName + "\"
                    ) AS query1 ON (o.SimulationID = query1.dpSimulationID) 
AND (o.SimRunID = query1.dpSimRunID) AND (o.OccurrenceID = query1.dpOccurrenceID)
                ) AS query2 INNER JOIN 

                (
                   SELECT pg.Description AS pgDescription, ip.ParticipantID AS 
ipParticipantID FROM ParticipantGroup AS pg INNER JOIN InitialParticipant AS ip ON pg.ParticipantGroupID = ip.ParticipantGroupID
                ) AS query3 ON query2.dpParticipantID = query3.ipParticipantID
            ) AS query4 ON tmpPartArgs.participantID = query4.dpParticipantID
        ) AS query5 INNER JOIN tmpSimArgs ON (query5.oSimRunID = tmpSimArgs.SimRunID) AND (query5.oSimulationID = tmpSimArgs.SimulationID)

    GROUP BY query5.oSimulationID, query5.oSimRunID, query5.oPeriod,
 query5.ipParticipantID, query5.pgDescription ORDER BY query5.oPeriod ASC;

The above query runs in about a minute in Access. The output of query1, query2, query3, query4 and query5 is around 1,200,000 records. And in SQLite it does not finish execution, I dont know why? I dont understand why the above query takes a minute to execute in Access and does not finish execution in SQLite. The most confounding thing about the above query is the subquery no. 5, i.e.

    SELECT pgDescription, oSimulationID, oSimRunID, ipParticipantID, oPeriod, dpVal, 
ptPropertyTypeName FROM tmpPartArgs INNER JOIN 

            (
              SELECT pgDescription, oSimulationID, oSimRunID, ipParticipantID, 
oPeriod, dpVal, dpParticipantID, ptPropertyTypeName FROM 

                (
                  SELECT o.SimulationID AS oSimulationID, o.SimRunID AS oSimRunID, 
o.Period AS oPeriod, dpVal, dpParticipantID, ptPropertyTypeName FROM Occurrence AS o INNER JOIN 

                    (
                      SELECT * FROM 
                        (
                           SELECT dp.Val AS dpVal, dp.SimulationID AS 
dpSimulationID, dp.SimRunID AS dpSimRunID,dp.ParticipantID AS dpParticipantID, 
dp.OccurrenceID AS dpOccurrenceID, pt.PropertyTypeName AS ptPropertyTypeName FROM 
PropertyType AS pt INNER JOIN DynamicProperty AS dp ON pt.PropertyTypeID = dp.PropertyTypeID
                        ) AS query0 WHERE query0.ptPropertyTypeName = \"" + args.propertyName + "\"
                    ) AS query1 ON (o.SimulationID = query1.dpSimulationID) 
AND (o.SimRunID = query1.dpSimRunID) AND (o.OccurrenceID = query1.dpOccurrenceID)
                ) AS query2 INNER JOIN 

                (
                   SELECT pg.Description AS pgDescription, ip.ParticipantID AS 
ipParticipantID FROM ParticipantGroup AS pg INNER JOIN InitialParticipant AS ip ON pg.ParticipantGroupID = ip.ParticipantGroupID
                ) AS query3 ON query2.dpParticipantID = query3.ipParticipantID
            ) AS query4 ON tmpPartArgs.participantID = query4.dpParticipantID
        ) AS query5

runs in a few seconds, i.e. the statement 'reader = com.ExecuteReader();' takes only a few seconds to run, but if I try to create a table out of the records returned by the above query it does not finish. Can anyone tell me why exactly this happens? I mean, it is able to retrieve around 1,200,000 records in a few seconds but something goes wrong when I try to create a table out of those records,i.e. CREATE TABLE query5 AS SELECT pgDescription, oSimulationID, oSimRunID, ipParticipantID, ... does not finish execution. Please help! Any kind of pointers will be really helpful.

The sole reason why I wanted to create a temporary table called query5 is because I can then create indexes on that temporary table, i.e. query5. I have assumed here that my original query (i.e. the first piece of code) does not finish because of indexing problems. So my another question is how can I efficiently insert around 1,200,000 records in a table within a minute?

The tables have the following structures:

1. Occurrence CREATE TABLE "Occurrence" ("SimulationID" smallint(5) NOT NULL DEFAULT 0, "SimRunID" smallint(5) NOT NULL DEFAULT 0, "OccurrenceID" int(10) NOT NULL DEFAULT 0, "OccurrenceTypeID" smallint(5) NOT NULL DEFAULT 0, "Period" smallint(5) NOT NULL DEFAULT 0, "HasSucceeded" bool NOT NULL DEFAULT 0, PRIMARY KEY ("SimulationID", "SimRunID", "OccurrenceID"))

The Occurrence table has around 740,000 records

1. DynamicProperty CREATE TABLE "DynamicProperty" ("SimulationID" smallint(5) NOT NULL DEFAULT 0 ,"SimRunID" smallint(5) NOT NULL DEFAULT 0 ,"ParticipantID" int(10) NOT NULL DEFAULT 0 ,"PropertyTypeID" smallint(5) NOT NULL DEFAULT 0 ,"OccurrenceID" int(10) NOT NULL DEFAULT 0 ,"Val" DOUBLE DEFAULT 0 , PRIMARY KEY ("SimulationID", "SimRunID", "ParticipantID", "PropertyTypeID", "OccurrenceID") )

The DynamicProperty table has around 6,250,000 records

1. PropertyType CREATE TABLE PropertyType (PropertyTypeID smallint (5),PropertyTypeName varchar (50),IsAgentProperty bool NOT NULL,IsActionProperty bool NOT NULL, IsKnowledgeItemProperty bool NOT NULL,IsStatic bool NOT NULL,IsKnowledgeStoreProperty bool NOT NULL,IsSimulationProperty bool NOT NULL,IsAssignable bool NOT NULL, PRIMARY KEY(PropertyTypeID))

PropertyType has around 50 records.

1. InitialParticipant CREATE TABLE "InitialParticipant" ("ParticipantID" smallint(5) PRIMARY KEY NOT NULL DEFAULT 0, "ParticipantTypeID" smallint(5) NOT NULL DEFAULT 0, "ParticipantGroupID" smallint(5) NOT NULL DEFAULT 0)

InitialParticipant has around 120 records

1. ParticipantGroup CREATE TABLE ParticipantGroup (ParticipantGroupID smallint (5),ParticipantGroupTypeID smallint (5),Description varchar (50), PRIMARY KEY (ParticipantGroupID))

ParticipantGroup has around 25 records.

1. tmpSimArgs CREATE TABLE tmpSimArgs (SimulationID smallint(5), SimRunID int(10))

tmpSimArgs has around 20 records.

1. tmpPartArgs CREATE TABLE tmpPartArgs(participantID INT)

tmpPartArgs has around 80 records

The database has the following indexes:

1. All primary keys of the respective tables as shown above.

2. CREATE INDEX tmpSimArgs_SimulationID_idx ON tmpSimArgs(SimulationID ASC); CREATE INDEX tmpSimArgs_SimRunID_idx ON tmpSimArgs(SimRunID ASC);

3. CREATE INDEX tmpPartArgs_participantID_idx ON tmpPartArgs(participantID ASC);

Please help!!

Warm Regards, Saswati De