Question

我有一个800MBMS Access数据库,由我迁移到库马特。数据库的结构如下(在移民之后,Kingk数据库约有330MB):

www.un.org/Depts/DGACM/index_french.htm 表格如下:

CREATE TABLE Occurrence 
(
SimulationID  INTEGER,    SimRunID   INTEGER,    OccurrenceID   INTEGER,
OccurrenceTypeID    INTEGER,    Period    INTEGER,    HasSucceeded    BOOL, 
PRIMARY KEY (SimulationID,  SimRunID,   OccurrenceID)
)

该指数如下:

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)

www.un.org/spanish/ga/president 表格如下:

CREATE TABLE OccurrenceParticipant 
(
SimulationID    INTEGER,     SimRunID    INTEGER,    OccurrenceID     INTEGER,
RoleTypeID     INTEGER,     ParticipantID    INTEGER
)

该指数如下:

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)

表格<代码>InitialParticipant有130份记录。表格的结构

CREATE TABLE InitialParticipant 
(
ParticipantID    INTEGER  PRIMARY KEY,     ParticipantTypeID    INTEGER,
ParticipantGroupID     INTEGER
)

该表有以下指数:

CREATE INDEX "initialpart_participantTypeID_idx" ON "InitialParticipant" ("ParticipantGroupID" ASC)

CREATE INDEX "initialpart_ParticipantID_idx" ON "InitialParticipant" ("ParticipantID" ASC)

<代码>ParticipantGroup有22个记录。它希望

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)

表格<代码>tmpSimArgs有18个记录。其结构如下:

CREATE TABLE tmpSimArgs (SimulationID varchar, SimRunID int(10))

以及以下指数:

CREATE INDEX tmpSimArgs_SimRunID_idx ON tmpSimArgs(SimRunID ASC)

CREATE INDEX tmpSimArgs_SimulationID_idx ON tmpSimArgs(SimulationID ASC)

表格“tmpPartArgs”有80个记录。其结构如下:

CREATE TABLE tmpPartArgs(participantID INT)

以及以下指数:

CREATE INDEX tmpPartArgs_participantID_idx ON tmpPartArgs(participantID ASC)

我有一个问询,涉及多个新公司JOIN,我面临的问题是,查询台的准入版本大约是第二次,而同一询问的Kallite版本则需要10秒(大约10倍放慢!) 我不可能再回过来,而我唯一的选择是卡利特。

我是新写数据库问询的,因此这些问询可能会 st,因此,请就你看错或怀疑什么。

准入问题(整个查询需要1次执行):

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;

灰心 que问如下(大约10秒钟):

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;

我不知道我在这里做了什么错误。我有所有指数,但我认为我没有宣布一些关键指数,而这一指数将给我带来trick。我的“再研究”在“King”上的移民表明,在“接触”所有方面,“T”是更快、小和更好的。但是,在审问方面,我似乎比接触更快地开展工作。我重申,我是新的宝库,显然没有什么想法和经验,这样,如果任何学到的灵魂能够帮助我这样做,那将受到高度赞赏。

Answer 1

我对你的法典进行了修改(使用我的家——家——家——家——家——家——————formatter),希望能够使他人更容易阅读。

改革:

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;

按照JohnFx(博览)的说法,我感到困惑。我认为,实际上没有必要这样做,特别是因为它们都是内在的。因此,我下面试图减少复杂性。请审查和测试业绩。由于我只加入“安全”,我不得不与“压力”一道做事——我认为这是理想的行为。

SELECT
    pg.Description,
    occ.SimulationID,
    occ.SimRunID,
    occ.Period,
    COUNT(occp.ParticipantID) AS CountOfParticipantID

FROM ParticipantGroup AS pg

INNER JOIN InitialParticipant AS ip
        ON pg.ParticipantGroupID = ip.ParticipantGroupID

CROSS JOIN tmpSimArgs AS tsa

INNER JOIN Occurrence AS occ
        ON tsa.SimRunID = occ.SimRunID
       AND tsa.SimulationID = occ.SimulationID

INNER JOIN OccurrenceParticipant AS occp
        ON occ.OccurrenceID = occp.OccurrenceID
       AND occ.SimRunID = occp.SimRunID
       AND occ.SimulationID = occp.SimulationID

INNER JOIN tmpPartArgs AS tpa
        ON tpa.participantID = occp.ParticipantID

WHERE occ.HasSucceeded = 1
  AND (occp.RoleTypeID = 52 OR occp.RoleTypeID = 49 )

GROUP BY
    pg.Description,
    occ.SimulationID,
    occ.SimRunID,
    occ.Period;

Answer 2

我的问询减少了。我希望这比我早些时候更清楚、更合理。

SELECT5 * FROM 
(
SELECT4 FROM ParticipantGroup as pg INNER JOIN InitialParticipant AS ip ON pg.ParticipantGroupID = ip.ParticipantGroupID
) AS ij1 INNER JOIN 
(
   SELECT3 * FROM tmpPartArgs AS tpa INNER JOIN 
      (
          SELECT2 * FROM 
              (
                  SELECT1 * 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

我正在处理的申请是一份模拟申请,为了了解上述问题的背景,我认为需要对申请作简要解释。让我们假设有一个拥有一些初步资源和生物制剂的地球。地球允许存在1000年,对特工采取的行动进行监测并储存在数据库中。经过1000年之后,地球被毁灭,并第一次以同样的一套初步资源和生物制剂重新种植。这(制造和销毁)重复了18次,在这1 000年内从事的代理人的所有行动都储存在数据库中。因此,我们的整个试验由18种再造组成,称为“Simulation”。地球每18次复生,每1 000年,每1 000年,就称为一段时期。因此,“Simulation”由18个班子组成,每个班期为1,000个。每次开学时,我们分配了“隔.”首批知识物品和动态媒介,相互交流。知识物品由一代理人储存在知识库内。知识库也被视为参与模拟的实体。但这一概念(关于知识储存)并不重要。我试图详细了解每个选举考试和测验表。

S/9/T1:我认为,这一询问只能用“Occurrence”表取代,因为它没有做任何事情。表Occurrence储存了这些代理人在每次“Simulation”的模拟中采取的不同行动。通常每个“Simulation”由18人组成。每次为期1 000个期间。允许代理人在“Simulation”的每次操作期间采取行动。但是,检察官办公室的表格没有储存有关采取行动的代理人的任何细节。风险表可能储存与多个“Simulations”有关的数据。

SlectT: 这种询问只是回到了“Simulation”每一期行动的细节,以及“Simulation”所有参与者如各自的参与人的详细情况。参与者表格为模拟活动的每个参与实体储存记录,包括代理人、知识商店、知识物品等。

SlectT3: 仅从由于代理人和知识项目而产生的假装表ij3中收回这些记录。第ij3号关于知识项目的所有记录都将被过滤。

SlectT4: 这里的“描述”是指“参与”的每一记录。请注意,“描述”一栏是整个查询的输出一栏。初始参与者包括每个代理人的记录和最初分配给“Simulation”的每个知识项目。

SlectT5:这一最后质询从Pseudo表ij2中收回所有记录,参与实体的作用表(可以是代理人或知识项目)为49或52。

Answer 3

我建议移走吉卜赛人。 RoleTypeID从最外围的电离层过滤到ij3,使用IN而不是OR,并将HasSucceed的电离层移到ij4。

友情链接