过程花费太多时间来更新数据库

发布于 2024-10-29 08:41:47 字数 1414 浏览 3 评论 0原文

CREATE OR REPLACE PROCEDURE UPDATE_CRDT_JV IS
 BEGIN
   UPDATE GL_DISTRIBUTION
         SET GL_DATE = (SELECT ADJ_DATE FROM ADJUSTMENTS WHERE
         ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'ADJST';
   UPDATE GL_DISTRIBUTION 
         SET GL_DATE = (SELECT PARTY_ADJ_DATE FROM PARTY_ADJUSTMENT
         WHERE PARTY_ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'PRTAJ';
   UPDATE GL_DISTRIBUTION
         SET GL_DATE = (SELECT VEN_PAY_VOU_DATE FROM PAYMENTS_TO_VENDORS WHERE
         VEN_PAY_VOU_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'CRPAY';
   UPDATE GL_DISTRIBUTION
         SET GL_DATE = (SELECT CHEQUE_DATE FROM SYS_PAYMENTS_HEADER WHERE
         REF_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'SYSPY';
   UPDATE GL_DISTRIBUTION
         SET GL_DATE = (SELECT POSTED_DATE FROM PURCHASE_INVOICE_HEADER WHERE
         POSTED_DATE IS NOT NULL AND PIV_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'CRINV';
   UPDATE GL_dISTRIBUTION
     SET GL_dATE = (SELECT DOC_dATE FROM REVERSE_HISTORY
           WHERE TR_NUMBER = TO_NUMBER(GL_DISTRIBUTION.TR_NUMBER)
         AND DOC_dATE IS NOT NULL AND TR_TYPE IN ('SYSPY','CRPAY'))
         WHERE TR_TYPE IN ('RSYSPY','RCRPAY');
   commit;
   UPDATE_INV_DET;
 END;

更新数据库需要超过 15 分钟。现在我正在通过在 SQL PLUS 中使用以下查询来更新此内容：

EXECUTE UPDATE_CRDT_JV;

如果有人知道此问题的解决方案，请帮助我

原文

CREATE OR REPLACE PROCEDURE UPDATE_CRDT_JV IS
 BEGIN
   UPDATE GL_DISTRIBUTION
         SET GL_DATE = (SELECT ADJ_DATE FROM ADJUSTMENTS WHERE
         ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'ADJST';
   UPDATE GL_DISTRIBUTION 
         SET GL_DATE = (SELECT PARTY_ADJ_DATE FROM PARTY_ADJUSTMENT
         WHERE PARTY_ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'PRTAJ';
   UPDATE GL_DISTRIBUTION
         SET GL_DATE = (SELECT VEN_PAY_VOU_DATE FROM PAYMENTS_TO_VENDORS WHERE
         VEN_PAY_VOU_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'CRPAY';
   UPDATE GL_DISTRIBUTION
         SET GL_DATE = (SELECT CHEQUE_DATE FROM SYS_PAYMENTS_HEADER WHERE
         REF_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'SYSPY';
   UPDATE GL_DISTRIBUTION
         SET GL_DATE = (SELECT POSTED_DATE FROM PURCHASE_INVOICE_HEADER WHERE
         POSTED_DATE IS NOT NULL AND PIV_NUMBER = TO_NUMBER(TR_NUMBER))
   WHERE TR_TYPE = 'CRINV';
   UPDATE GL_dISTRIBUTION
     SET GL_dATE = (SELECT DOC_dATE FROM REVERSE_HISTORY
           WHERE TR_NUMBER = TO_NUMBER(GL_DISTRIBUTION.TR_NUMBER)
         AND DOC_dATE IS NOT NULL AND TR_TYPE IN ('SYSPY','CRPAY'))
         WHERE TR_TYPE IN ('RSYSPY','RCRPAY');
   commit;
   UPDATE_INV_DET;
 END;

is taking more than 15 minutes to update the database.
now i am updating this by using the following query in SQL PLUS:

EXECUTE UPDATE_CRDT_JV;

pls help me if any body knows the solution for this problem

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一生独一 2024-11-05 08:41:48

我同意已经给出的建议，开始弄清楚你的代码在哪里花费了时间。但是，您的情况很常见，我认为我认识到这种情况：您以这样的方式编码更新语句，以便为该类型的 GL_DISTRIBUTION 的每一行访问其他表。

解决方案是重写您的更新语句，我发现有两种有效地做到这一点的可能性：

1）更新选择语句（UPDATE（SELECT ...）SET ... WHERE ...）。这需要设置一些唯一的键约束或使用 BYPASS_UJVC 提示。

2）使用MERGE语句。

下面您将看到如何使用单个合并语句重写代码的示例。我期望获得巨大的性能提升，因为现在对每个表使用单个外连接即可完成对其他表的访问，而不是对 GL_DISTRIBUTION 表中的每一行进行访问。

例子。测试数据：

SQL> create table gl_distribution (tr_number, tr_type, gl_date)
  2  as
  3  select '1', 'ADJST', date '2011-01-01' from dual union all
  4  select '2', 'ADJST', null from dual union all
  5  select '3', 'PRTAJ', date '2011-01-01' from dual union all
  6  select '4', 'SYSPY', date '2011-01-01' from dual union all
  7  select '5', 'RCRPAY', date '2011-01-01' from dual
  8  /

Table created.

SQL> create table adjustments (adj_number, adj_date)
  2  as
  3  select 1, sysdate from dual union all
  4  select 2, sysdate from dual
  5  /

Table created.

SQL> create table party_adjustment (party_adj_number, party_adj_date)
  2  as
  3  select 3, sysdate from dual union all
  4  select 33, sysdate from dual
  5  /

Table created.

SQL> create table payments_to_vendors (ven_pay_vou_number, ven_pay_vou_date)
  2  as
  3  select 34, sysdate from dual
  4  /

Table created.

SQL> create table sys_payments_header (ref_number,cheque_date)
  2  as
  3  select 4, sysdate from dual
  4  /

Table created.

SQL> create table purchase_invoice_header (piv_number,posted_date)
  2  as
  3  select 35, sysdate from dual
  4  /

Table created.

SQL> create table reverse_history (tr_number,doc_date,tr_type)
  2  as
  3  select 5, sysdate, 'CRPAY' from dual
  4  /

Table created.
SQL>

您的程序（用于比较）：

SQL> CREATE OR REPLACE PROCEDURE UPDATE_CRDT_JV
  2  IS
  3  BEGIN
  4    UPDATE GL_DISTRIBUTION
  5    SET GL_DATE = (SELECT ADJ_DATE FROM ADJUSTMENTS WHERE  ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
  6    WHERE TR_TYPE = 'ADJST'
  7    ;
  8    UPDATE GL_DISTRIBUTION
  9    SET GL_DATE = (SELECT PARTY_ADJ_DATE FROM PARTY_ADJUSTMENT
 10    WHERE PARTY_ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
 11    WHERE TR_TYPE = 'PRTAJ'
 12    ;
 13    UPDATE GL_DISTRIBUTION
 14    SET GL_DATE = (SELECT VEN_PAY_VOU_DATE FROM PAYMENTS_TO_VENDORS
 15    WHERE VEN_PAY_VOU_NUMBER = TO_NUMBER(TR_NUMBER))
 16    WHERE TR_TYPE = 'CRPAY'
 17    ;
 18    UPDATE GL_DISTRIBUTION
 19    SET GL_DATE = (SELECT CHEQUE_DATE FROM SYS_PAYMENTS_HEADER WHERE
 20    REF_NUMBER = TO_NUMBER(TR_NUMBER))
 21    WHERE TR_TYPE = 'SYSPY'
 22    ;
 23    UPDATE GL_DISTRIBUTION
 24    SET GL_DATE = (SELECT POSTED_DATE FROM PURCHASE_INVOICE_HEADER WHERE
 25    POSTED_DATE IS NOT NULL AND PIV_NUMBER = TO_NUMBER(TR_NUMBER))
 26    WHERE TR_TYPE = 'CRINV'
 27    ;
 28    UPDATE GL_dISTRIBUTION
 29    SET GL_dATE = (SELECT DOC_dATE FROM REVERSE_HISTORY
 30    WHERE TR_NUMBER = TO_NUMBER(GL_DISTRIBUTION.TR_NUMBER)
 31    AND DOC_dATE IS NOT NULL AND TR_TYPE IN ('SYSPY','CRPAY'))
 32    WHERE TR_TYPE IN ('RSYSPY','RCRPAY')
 33    ;
 34    --commit;
 35    --UPDATE_INV_DET;
 36  END;
 37  /

Procedure created.

SQL>

我的建议：

SQL> create procedure new_update_crdt_jv
  2  as
  3  begin
  4    merge into gl_distribution d
  5    using ( select to_number(d.tr_number) tr_number
  6                 , coalesce
  7                   ( a.adj_date
  8                   , pa.party_adj_date
  9                   , pv.ven_pay_vou_date
 10                   , sph.cheque_date
 11                   , pih.posted_date
 12                   , rh.doc_date
 13                   ) new_date
 14              from gl_distribution d
 15                   left outer join adjustments a
 16                     on to_number(d.tr_number) = a.adj_number
 17                     and d.tr_type = 'ADJST'
 18                   left outer join party_adjustment pa
 19                     on to_number(d.tr_number) = pa.party_adj_number
 20                     and d.tr_type = 'PRTAJ'
 21                   left outer join payments_to_vendors pv
 22                     on to_number(d.tr_number) = pv.ven_pay_vou_number
 23                     and d.tr_type = 'CRPAY'
 24                   left outer join sys_payments_header sph
 25                     on to_number(d.tr_number) = sph.ref_number
 26                     and d.tr_type = 'SYSPY'
 27                   left outer join purchase_invoice_header pih
 28                     on to_number(d.tr_number) = pih.piv_number
 29                     and d.tr_type = 'CRINV'
 30                   left outer join reverse_history rh
 31                     on to_number(d.tr_number) = rh.tr_number
 32                     and rh.tr_type in ('SYSPY','CRPAY')
 33                     and d.tr_type in ('RSYSPY','RCRPAY')
 34          ) n
 35       on ( d.tr_number = n.tr_number)
 36     when matched then
 37          update set d.gl_date = n.new_date
 38    ;
 39  end new_update_crdt_jv;
 40  /

Procedure created.

SQL>

让我们运行您的程序：

SQL> select * from gl_distribution
  2  /

T TR_TYP GL_DATE
- ------ -------------------
1 ADJST  01-01-2011 00:00:00
2 ADJST
3 PRTAJ  01-01-2011 00:00:00
4 SYSPY  01-01-2011 00:00:00
5 RCRPAY 01-01-2011 00:00:00

5 rows selected.

SQL> exec update_crdt_jv

PL/SQL procedure successfully completed.

SQL> select * from gl_distribution
  2  /

T TR_TYP GL_DATE
- ------ -------------------
1 ADJST  31-03-2011 14:41:19
2 ADJST  31-03-2011 14:41:19
3 PRTAJ  31-03-2011 14:41:19
4 SYSPY  31-03-2011 14:41:19
5 RCRPAY 31-03-2011 14:41:19

5 rows selected.

SQL> rollback
  2  /

Rollback complete.

SQL>

我的程序返回相同的结果：

SQL> exec new_update_crdt_jv

PL/SQL procedure successfully completed.

SQL> select * from gl_distribution
  2  /

T TR_TYP GL_DATE
- ------ -------------------
1 ADJST  31-03-2011 14:41:19
2 ADJST  31-03-2011 14:41:19
3 PRTAJ  31-03-2011 14:41:19
4 SYSPY  31-03-2011 14:41:19
5 RCRPAY 31-03-2011 14:41:19

5 rows selected.

希望这会有所帮助。

问候，
抢。

I agree with the already given advice to start figuring out where your code is spending time. However, your case is quite common and I think I recognize this situation: you have coded your update statements in such a way that the other tables are accessed for every row of GL_DISTRIBUTION of that type.

The solution is to rewrite your update statements and I see two possibilities to do that efficiently:

1) Update a select statement (UPDATE (SELECT ...) SET ... WHERE ...). This requires some unique key constraints to be in place or using the BYPASS_UJVC hint.

2) Use a MERGE statement.

Below you see an example of how to rewrite your code using a single merge statement. I'm expecting big performance gains because the access of the other tables is now done once using a single outer join for each of the tables, instead of for every row in the GL_DISTRIBUTION table.

The example. Test data:

SQL> create table gl_distribution (tr_number, tr_type, gl_date)
  2  as
  3  select '1', 'ADJST', date '2011-01-01' from dual union all
  4  select '2', 'ADJST', null from dual union all
  5  select '3', 'PRTAJ', date '2011-01-01' from dual union all
  6  select '4', 'SYSPY', date '2011-01-01' from dual union all
  7  select '5', 'RCRPAY', date '2011-01-01' from dual
  8  /

Table created.

SQL> create table adjustments (adj_number, adj_date)
  2  as
  3  select 1, sysdate from dual union all
  4  select 2, sysdate from dual
  5  /

Table created.

SQL> create table party_adjustment (party_adj_number, party_adj_date)
  2  as
  3  select 3, sysdate from dual union all
  4  select 33, sysdate from dual
  5  /

Table created.

SQL> create table payments_to_vendors (ven_pay_vou_number, ven_pay_vou_date)
  2  as
  3  select 34, sysdate from dual
  4  /

Table created.

SQL> create table sys_payments_header (ref_number,cheque_date)
  2  as
  3  select 4, sysdate from dual
  4  /

Table created.

SQL> create table purchase_invoice_header (piv_number,posted_date)
  2  as
  3  select 35, sysdate from dual
  4  /

Table created.

SQL> create table reverse_history (tr_number,doc_date,tr_type)
  2  as
  3  select 5, sysdate, 'CRPAY' from dual
  4  /

Table created.
SQL>

Your procedure (for comparison):

SQL> CREATE OR REPLACE PROCEDURE UPDATE_CRDT_JV
  2  IS
  3  BEGIN
  4    UPDATE GL_DISTRIBUTION
  5    SET GL_DATE = (SELECT ADJ_DATE FROM ADJUSTMENTS WHERE  ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
  6    WHERE TR_TYPE = 'ADJST'
  7    ;
  8    UPDATE GL_DISTRIBUTION
  9    SET GL_DATE = (SELECT PARTY_ADJ_DATE FROM PARTY_ADJUSTMENT
 10    WHERE PARTY_ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
 11    WHERE TR_TYPE = 'PRTAJ'
 12    ;
 13    UPDATE GL_DISTRIBUTION
 14    SET GL_DATE = (SELECT VEN_PAY_VOU_DATE FROM PAYMENTS_TO_VENDORS
 15    WHERE VEN_PAY_VOU_NUMBER = TO_NUMBER(TR_NUMBER))
 16    WHERE TR_TYPE = 'CRPAY'
 17    ;
 18    UPDATE GL_DISTRIBUTION
 19    SET GL_DATE = (SELECT CHEQUE_DATE FROM SYS_PAYMENTS_HEADER WHERE
 20    REF_NUMBER = TO_NUMBER(TR_NUMBER))
 21    WHERE TR_TYPE = 'SYSPY'
 22    ;
 23    UPDATE GL_DISTRIBUTION
 24    SET GL_DATE = (SELECT POSTED_DATE FROM PURCHASE_INVOICE_HEADER WHERE
 25    POSTED_DATE IS NOT NULL AND PIV_NUMBER = TO_NUMBER(TR_NUMBER))
 26    WHERE TR_TYPE = 'CRINV'
 27    ;
 28    UPDATE GL_dISTRIBUTION
 29    SET GL_dATE = (SELECT DOC_dATE FROM REVERSE_HISTORY
 30    WHERE TR_NUMBER = TO_NUMBER(GL_DISTRIBUTION.TR_NUMBER)
 31    AND DOC_dATE IS NOT NULL AND TR_TYPE IN ('SYSPY','CRPAY'))
 32    WHERE TR_TYPE IN ('RSYSPY','RCRPAY')
 33    ;
 34    --commit;
 35    --UPDATE_INV_DET;
 36  END;
 37  /

Procedure created.

SQL>

My suggestion:

SQL> create procedure new_update_crdt_jv
  2  as
  3  begin
  4    merge into gl_distribution d
  5    using ( select to_number(d.tr_number) tr_number
  6                 , coalesce
  7                   ( a.adj_date
  8                   , pa.party_adj_date
  9                   , pv.ven_pay_vou_date
 10                   , sph.cheque_date
 11                   , pih.posted_date
 12                   , rh.doc_date
 13                   ) new_date
 14              from gl_distribution d
 15                   left outer join adjustments a
 16                     on to_number(d.tr_number) = a.adj_number
 17                     and d.tr_type = 'ADJST'
 18                   left outer join party_adjustment pa
 19                     on to_number(d.tr_number) = pa.party_adj_number
 20                     and d.tr_type = 'PRTAJ'
 21                   left outer join payments_to_vendors pv
 22                     on to_number(d.tr_number) = pv.ven_pay_vou_number
 23                     and d.tr_type = 'CRPAY'
 24                   left outer join sys_payments_header sph
 25                     on to_number(d.tr_number) = sph.ref_number
 26                     and d.tr_type = 'SYSPY'
 27                   left outer join purchase_invoice_header pih
 28                     on to_number(d.tr_number) = pih.piv_number
 29                     and d.tr_type = 'CRINV'
 30                   left outer join reverse_history rh
 31                     on to_number(d.tr_number) = rh.tr_number
 32                     and rh.tr_type in ('SYSPY','CRPAY')
 33                     and d.tr_type in ('RSYSPY','RCRPAY')
 34          ) n
 35       on ( d.tr_number = n.tr_number)
 36     when matched then
 37          update set d.gl_date = n.new_date
 38    ;
 39  end new_update_crdt_jv;
 40  /

Procedure created.

SQL>

Let's run your procedure:

SQL> select * from gl_distribution
  2  /

T TR_TYP GL_DATE
- ------ -------------------
1 ADJST  01-01-2011 00:00:00
2 ADJST
3 PRTAJ  01-01-2011 00:00:00
4 SYSPY  01-01-2011 00:00:00
5 RCRPAY 01-01-2011 00:00:00

5 rows selected.

SQL> exec update_crdt_jv

PL/SQL procedure successfully completed.

SQL> select * from gl_distribution
  2  /

T TR_TYP GL_DATE
- ------ -------------------
1 ADJST  31-03-2011 14:41:19
2 ADJST  31-03-2011 14:41:19
3 PRTAJ  31-03-2011 14:41:19
4 SYSPY  31-03-2011 14:41:19
5 RCRPAY 31-03-2011 14:41:19

5 rows selected.

SQL> rollback
  2  /

Rollback complete.

SQL>

My procedure returns the same results:

SQL> exec new_update_crdt_jv

PL/SQL procedure successfully completed.

SQL> select * from gl_distribution
  2  /

T TR_TYP GL_DATE
- ------ -------------------
1 ADJST  31-03-2011 14:41:19
2 ADJST  31-03-2011 14:41:19
3 PRTAJ  31-03-2011 14:41:19
4 SYSPY  31-03-2011 14:41:19
5 RCRPAY 31-03-2011 14:41:19

5 rows selected.

Hope this helps.

Regards,
Rob.

回复收藏 0 原文

安穩 2024-11-05 08:41:48

这个问题的解决方案是：

找出你的代码在哪里
花时间（即分析它）
找出如何加快速度
最慢的部分
重复直到性能可以接受

如果您更喜欢猜测，那么您可能需要尝试以下任一方法：

将多个 UPDATE 合并到单个 UPDATE 语句中，例如使用 @Aklopper 所示的 CASE 条件。

使用 MERGE 而不是 UPDATE 以避免相关子查询。可能会更好，也可能不会。

查看在所示过程末尾调用的 UPDATE_INV_DET 过程。

回复收藏 0 原文

扛刀软妹 2024-11-05 08:41:48

我来自SQL Server环境，这样的CASE UPDATE语句不会对你有帮助吗（SQL中UPDATE CASE语句的使用示例（不知道Oracle是否有等效的方法）：

   UPDATE titles
           SET GL_DATE=
                     CASE
                       WHEN TR_TYPE = 'ADJST' THEN (SELECT ADJ_DATE FROM ADJUSTMENTS WHERE ADJ_NUMBER = TO_NUMBER(TR_NUMBER) END
                       WHEN TR_TYPE = 'PRTAJ'' THEN (SELECT PARTY_ADJ_DATE FROM PARTY_ADJUSTMENT
         WHERE PARTY_ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
 END
                       ELSE price
                     END

I come from a SQL Server environment, won't a CASE UPDATE statement like this also help you(example of usage of the UPDATE CASE statement in SQL(don't know if Oracle has an equivalent methods) :

   UPDATE titles
           SET GL_DATE=
                     CASE
                       WHEN TR_TYPE = 'ADJST' THEN (SELECT ADJ_DATE FROM ADJUSTMENTS WHERE ADJ_NUMBER = TO_NUMBER(TR_NUMBER) END
                       WHEN TR_TYPE = 'PRTAJ'' THEN (SELECT PARTY_ADJ_DATE FROM PARTY_ADJUSTMENT
         WHERE PARTY_ADJ_NUMBER = TO_NUMBER(TR_NUMBER))
 END
                       ELSE price
                     END

回复收藏 0 原文

~没有更多了~