Re: [sqr-users] Urgent help needed !!

["Edwin Hommes" <hommes@bluewin.ch>]

Manuel,

I think you should check the amount of data in the target table as well.
When there is a lot of data in there it could be the initial space planned
for the table is full and the database needs to write it elsewhere. As long
as you're nagging you're DBA's anyway, you may also want them to check the
fragmentation of your database as well as the status of your database
statistics. Also check the amount of indices on the table. The more there
are, the more need to be updated when inserting a row. You may want to
consider dropping your indices just before the inserts and rebuild them
after you are done. For the SQL, you may want to get rid of the OR
statement, though that should not make a huge difference in performance.

Regards,
Edwin


----- Original Message ----- 
From: <Manuel_Garcia@hdm.honda.com>
To: <sqr-users@sqrug.org>
Sent: Monday, May 05, 2003 7:36 PM
Subject: [sqr-users] Urgent help needed !!


> Hi all,
>
>
> I'm writing a sqr program and I'm having a performance problem with this
> statement:
>
>
> BEGIN-SQL
> INSERT into ps_hdm_saldos_tmp using
> SELECT
> D.VOUCHER_ID,
> E.PYMNT_CNT,
> D.INVOICE_ID,
> D.VENDOR_ID,
> D.INVOICE_DT,
> D.ACCOUNTING_DT,
> D.FOREIGN_AMOUNT,
> D.FOREIGN_CURRENCY,
> E.RATE_MULT,
> D.MONETARY_AMOUNT,
> E.PYMNT_GROSS_AMT,
> E.SCHEDULED_PAY_DT,
> E.PYMNT_SELCT_STATUS,
> E.DSCNT_DUE_DT
> FROM PS_HDM_VOUCHER_TMP D, PS_PYMNT_VCHR_XREF E
> WHERE D.VOUCHER_ID = E.VOUCHER_ID
> AND E.PYMNT_SELCT_STATUS <> 'P'
> AND E.PYMNT_SELCT_STATUS <> 'X'
> AND D.ACCOUNTING_DT <= $fecha
> AND ( D.CLOSE_STATUS <> 'C'
> OR ( D.CLOSE_STATUS = 'C'
> AND D.MANUAL_CLOSE_DT > $fecha))
> ;
> COMMIT;
> END-SQL
>
> My key fields are:
>
> PS_HDM_VOUCHER_TMP:
> voucher_id
>
> PS_PYMNT_VCHR_XREF:
> business_unit,
> voucher_id,
> pymnt_count.
>
>
> If I run a query oustide sqr (PS Query for instance), it runs almost
> immediately,
> SELECT
> D.VOUCHER_ID,
> E.PYMNT_CNT,
> D.INVOICE_ID,
> D.VENDOR_ID,
> D.INVOICE_DT,
> D.ACCOUNTING_DT,
> D.FOREIGN_AMOUNT,
> D.FOREIGN_CURRENCY,
> E.RATE_MULT,
> D.MONETARY_AMOUNT,
> E.PYMNT_GROSS_AMT,
> E.SCHEDULED_PAY_DT,
> E.PYMNT_SELCT_STATUS,
> E.DSCNT_DUE_DT
> FROM PS_HDM_VOUCHER_TMP D, PS_PYMNT_VCHR_XREF E
> WHERE D.VOUCHER_ID = E.VOUCHER_ID
> AND E.PYMNT_SELCT_STATUS <> 'P'
> AND E.PYMNT_SELCT_STATUS <> 'X'
> AND D.ACCOUNTING_DT <= $fecha
> AND ( D.CLOSE_STATUS <> 'C'
> OR ( D.CLOSE_STATUS = 'C'
> AND D.MANUAL_CLOSE_DT > $fecha))
> ;
>
>
> I have similar statements along my program and there's no problem whit
> them, the next, for instance, it runs quick:
>
> BEGIN-SQL
>
> INSERT into ps_hdm_saldos_tmp using
> SELECT
> A.VOUCHER_ID,
> B.PYMNT_CNT,
> A.INVOICE_ID,
> A.VENDOR_ID,
> A.INVOICE_DT,
> A.ACCOUNTING_DT,
> A.FOREIGN_AMOUNT,
> A.FOREIGN_CURRENCY,
> B.RATE_MULT,
> A.MONETARY_AMOUNT,
> B.PYMNT_GROSS_AMT,
> B.SCHEDULED_PAY_DT,
> B.PYMNT_SELCT_STATUS,
> C.PYMNT_DT
> FROM PS_HDM_VOUCHER_TMP A, PS_PYMNT_VCHR_XREF B, PS_PAYMENT_TBL C
> WHERE A.VOUCHER_ID = B.VOUCHER_ID
> AND C.BANK_SETID = B.BANK_SETID
> AND C.BANK_CD = B.BANK_CD
> AND C.BANK_ACCT_KEY = B.BANK_ACCT_KEY
> AND C.PYMNT_ID = B.PYMNT_ID
> AND A.ACCOUNTING_DT <= $fecha
> AND B.PYMNT_SELCT_STATUS = 'P'
> AND C.ACCOUNTING_DT > $fecha
> ;
> COMMIT;
> END-SQL
>
>
>
>
> Thanks.
>
>
>
>
>
>
>
> Juan Manuel García
> Tecnología de Información
> Honda de Mexico S.A. de C.V.
> Tel: (01 33) 328 40135
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.476 / Virus Database: 273 - Release Date: 24/04/2003

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users