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


2

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中删除重复项,它应该快速运行

更多相关文章

  1. MySql索引的优缺点
  2. 外键是否可以引用非唯一索引?
  3. 保持最新的一个字段值,直到它发生变化,然后保持其最新的字段值
  4. mysql索引性能测试
  5. MySQL中建立索引的集中方式
  6. 替换wordpress WP_POSTS表中post_date字段的年份(4位数)
  7. 如何以varchar字段为编号?
  8. MYSQL中的普通索引,主健,唯一,全文索引区别
  9. 如何使用mysql 命令行 查看mysql表大小、数据大小、索引大小

随机推荐

  1. 一网打尽Android-UI系列教程
  2. Android TextView 设置中划线 下划线
  3. android应用安全——通信安全(android htt
  4. 【起航计划 029】2015 起航计划 Android
  5. 创建Android工程时报错:Errors running b
  6. android程序获取WIFI的IP地址和MAC地址
  7. (Android(安卓)Studio)Android(安卓)手机
  8. Eclipse中Android项目XML文件的格式化配
  9. pandaboard ES学习之旅——5 Android Lin
  10. Android(安卓)UI开发第二十九篇——Andro