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

["Ian Mills" <Ian.Mills@orix.com.au>]

Juan,

One possible cause could be the $fecha variable. If this is not defined
with the correct type it can cause a query to run slow.

Another performance problem I have encounted is when I am running
reports against a Sybase database. Somehow the database query analyser
when running queries in SQR doesn't choose the correct index and as a
result, the queries use either no index or the wrong index. To fix this
I include index query hints. If your not using Sybase then this may not
be a problem. Although as a precaution, I use index query hints in all
my queries no matter what database I am using.

In general, I have noticed that some of my queries run much faster in a
query analysis tool than they do in an SQR program. I do not know why
this is

I hope I have been of some assistance.

Regards,
Ian Mills
ORIX Australia

>>> Manuel_Garcia@hdm.honda.com 06/05/2003 3:36:27 am >>>
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























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

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