为什么这个查询需要很长时间才能执行

I have three tables

我有三张桌子

glSalesJournal
HMISAdd
HMISMain

Now what i am trying to do is add the glSalesJournal amt with HMISAdd amt while grouping up with various Fields and inserting the result into glSalesJournal

现在我要做的是添加glSalesJournal amt与HMISAdd amt同时分组各种Fields并将结果插入到glSalesJournal中

The glSalesJournal contains 633173 records

glSalesJournal包含633173条记录

The HMISAdd contains 4193 records

HMISAdd包含4193条记录

HMISAdd and glSalesJournal contains the same columns which are

HMISAdd和glSalesJournal包含相同的列

loc
glAcct
glSubAcct
batchNbr
contractNbr
amt

I added indexes to the table still the results are the same.

我在表中添加了索引,结果仍然相同。

Here is my code:

这是我的代码:

INSERT INTO hmismain
            (loc,
             glacct,
             subacct,
             batchnbr,
             contractnbr,
             amt)
SELECT glsalesjournal.loc,
       glsalesjournal.glacct,
       glsalesjournal.glsubacct,
       ( glsalesjournal.amt + hmisadd.amt ) AS sumAmt,
       glsalesjournal.batchnbr,
       glsalesjournal.salescontnbr
FROM   glsalesjournal
       LEFT OUTER JOIN hmisadd
                    ON ( glsalesjournal.loc = hmisadd.loc
                         AND glsalesjournal.glacct = hmisadd.glacct
                         AND glsalesjournal.glsubacct = hmisadd.subacct
                         AND glsalesjournal.batchnbr = hmisadd.batchnbr
                         AND glsalesjournal.salescontnbr = hmisadd.contractnbr )
GROUP  BY glsalesjournal.loc,
          hmisadd.loc,
          glsalesjournal.glacct,
          hmisadd.glacct,
          glsalesjournal.glsubacct,
          hmisadd.subacct,
          glsalesjournal.batchnbr,
          hmisadd.batchnbr,
          glsalesjournal.salescontnbr,
          hmisadd.contractnbr

The time taken by the script to execute is more than 2 hours. Even when I limit the Records to 100 the time taken is the same.

脚本执行所花费的时间超过2小时。即使我将记录限制为100,所用时间也是一样的。

Can someone please guide me how can I optimize the script.

有人可以指导我如何优化脚本。

Thanks

2 个解决方案

#1

1) It looks like it's a one off query, am I correct here? If not than you are inserting the same data into hmismain table every time.
2) You are grouping on fields from TWO separate tables, so no amount of indexing will ever help you. The ONLY index that will help is an index over a view linking these two tables in the same way.

1)看起来这是一次性查询,我在这里是否正确?如果不是,每次都将相同的数据插入到hmismain表中。 2)您正在对两个独立表中的字段进行分组,因此没有任何索引可以帮助您。唯一有帮助的索引是以相同方式链接这两个表的视图的索引。

Further note:
What is the point of

进一步说明:有什么意义

      GROUP  BY glsalesjournal.loc,
      hmisadd.loc,
      glsalesjournal.glacct,
      hmisadd.glacct,
      glsalesjournal.glsubacct,
      hmisadd.subacct,
      glsalesjournal.batchnbr,
      hmisadd.batchnbr,
      glsalesjournal.salescontnbr,
      hmisadd.contractnbr

You are grouping the data by the same fields twice
glsalesjournal.loc, hmisadd.loc
glsalesjournal.glacct, hmisadd.glacct,
...

您将数据按相同的字段分组两次glsalesjournal.loc,hmisadd.loc glsalesjournal.glacct,hmisadd.glacct,...

Remove the duplicates from GROUP BY and it should run fast

从GROUP BY中删除重复项,它应该快速运行

#2

Did you add an index on this fields:

您是否在此字段上添加了索引:

glSalesJournal.loc
glSalesJournal.glAcct
glSalesJournal.glSubAcct
glSalesJournal.batchNbr
glSalesJournal.salesContNbr
HMISAdd.Loc
HMISAdd.GlAcct
HMISAdd.SubAcct
HMISAdd.batchNbr 
HMISAdd.contractNbr

If this fields are unindexed, it will perform fulltable scan for each individual record thus causing slow performance.

如果此字段未编入索引,它将对每个单独的记录执行完整扫描,从而导致性能降低。

MySQL Create Index Syntax

MySQL创建索引语法

#1