Making Code Run Faster

[velvettiger]

Hi everyone,

So I am really new to Progress and I was wondering if there is a way to make the below piece of code run faster. I know that you could use an INDEX but when i looked in the help it is soooo confusing. Can anyone shed any light.

Thanks a lot

OUTPUT TO C:\cal\custTT.TXT.
 SESSION:DATE-FORMAT = "ymd".
FOR EACH tranfile FIELDS(card-no donor-card-no on-statement original-card-no post-date subsidiary-id tender-code tran-date tran-value tran-points
                                                           ) 

 where     (
            (CARD-NO = '488')
            OR(card-no = '496')
            OR (CARD-NO = '907')
        )
     NO-LOCK:
    

EXPORT 
    DELIMITER ","

                           card-no 
                          donor-card-no
                          on-statement 
                          original-card-no
                          string(tranfile.post-date, '9999/99/99') 
                          subsidiary-id  
                          tender-code 
                          string(tranfile.tran-date, '9999/99/99') 
                          tran-value 
                          tran-points
                       
                          
     
    .

END.

OUTPUT CLOSE.

 

[Casper]

assuming card-no is the first field of an index this wil be much faster:

DEFINE VARIABLE cNumbers AS CHARACTER   NO-UNDO.
DEFINE VARIABLE iTmp AS INTEGER     NO-UNDO.
assign cNumbers = '488,496,907'.
OUTPUT TO C:\cal\custTT.TXT.
SESSION:DATE-FORMAT = "ymd".
do iTmp = 1 to num-entries(cNumbers):
    FOR EACH tranfile
        FIELDS(card-no donor-card-no on-statement original-card-no post-date subsidiary-id tender-code tran-date tran-value tran-points)
            where tranfile.CARD-NO = entry(iTmp,cNumbers) no-lock:
        EXPORT 
            DELIMITER ","
            card-no 
            donor-card-no
            on-statement 
            original-card-no
            string(tranfile.post-date, '9999/99/99') 
            subsidiary-id  
            tender-code 
            string(tranfile.tran-date, '9999/99/99') 
            tran-value 
            tran-points
        .
    end.
end.
OUTPUT CLOSE.

HTH,

Casper.

 

[tamhas]

Well, we are short a little information. Is there an index on card-no (card-no is the leading field)? If not, you are stuck with a whole table scan, regardless. If there is a suitable index, your current form will still result in a whole table scan since it can't bracket on an OR. So, you could do three separate passes, one for each card-no and either just output them in that order or build a temp-table, sort as desired, and output from the temp-table.

 

[velvettiger]

Hi Thanks for all of your advice,

So i checked through the database and i realised that there is an index on donor-card-no, so I placed that first in the field list. I also made some changes to the code- please see below code-. The code compiles and runs but I am getting the a text file multiple error lines that look like this

utine customer-id, linenumber 31164, error 29764, state 22 (4669) [code]

Any ideas why. Below is the code.

[code]
DEFINE VARIABLE cNumbers AS CHARACTER   NO-UNDO.
DEFINE VARIABLE iTmp AS INTEGER     NO-UNDO.

assign cNumbers = '2525250703889488,2525250703889496,2525250703876907907'.
OUTPUT TO C:\cal\custTT2.TXT.
SESSION:DATE-FORMAT = "ymd".

do iTmp = 1 to num-entries(cNumbers):

FOR EACH customer 
    FIELDS (card-no) /*there is an index on the card-no in the customer table*/
NO-LOCK:
    FOR EACH tranfile
        FIELDS(donor-card-no tran-date subsidiary-id post-date card-no  on-statement  original-card-no  tender-code  tran-value tran-points)
         WHERE tranfile.customer-id = customer.customer-id
         AND tranfile.card-no=customer.card-no
         AND tranfile.CARD-NO = entry(iTmp,cNumbers) no-lock:
        EXPORT 
            DELIMITER ","
            card-no 
            donor-card-no
            on-statement 
            original-card-no
            string(tranfile.post-date, '9999/99/99') 
            subsidiary-id  
            tender-code 
            string(tranfile.tran-date, '9999/99/99') 
            tran-value 
            tran-points
        .
        END.
    end.
end.
OUTPUT CLOSE.

 

[tamhas]

Did you look up error 4669 in the Knowledge Base?

Is it repeatable?

 

[velvettiger]

Did you look up error 4669 in the Knowledge Base?

Is it repeatable?

yes it is repeatable.

 

[tamhas]

By repeatable, do you mean you get the same errors in the same places or that you always get some errors someplace.

The KB makes it sound like a connection issue and that possibly you should be looking in the database log for more hints.

I.e., I think it is a different issue than just this code.

 

[Casper]

It looks like you really need some basic Progress training.
First skip the fields prases cuz it won't gain you so much performance (Well that is my opinion), and it makes the query more readible to yourself. It seems to me that you don't understand the fields options completely.
Next thing: don't nest for eaches if you don't need to. You do now a table-scan of the customer table (3 times to be exactly) which also isn't neccessary.
In the query you have, you have twice the card-no first with the entry function and then with the customer value (which both are the same).

If card-no is the first field of an index in the customer and customer-id,card-no are actually an index in the tranfile table then this should work:

DEFINE VARIABLE cNumbers AS CHARACTER   NO-UNDO.
DEFINE VARIABLE iTmp AS INTEGER     NO-UNDO.
assign cNumbers = '2525250703889488,2525250703889496,2525250703876907907'.
OUTPUT TO C:\cal\custTT2.TXT.
SESSION:DATE-FORMAT = "ymd".
do iTmp = 1 to num-entries(cNumbers):
FOR EACH customer WHERE customer.card-no = ENTRY(iTmp,cNumbers),
     EACH tranfile
         WHERE tranfile.customer-id = customer.customer-id
         AND tranfile.card-no=customer.card-no
          no-lock:
        EXPORT 
            DELIMITER ","
            card-no 
            donor-card-no
            on-statement 
            original-card-no
            string(tranfile.post-date, '9999/99/99') 
            subsidiary-id  
            tender-code 
            string(tranfile.tran-date, '9999/99/99') 
            tran-value 
            tran-points
        .
        END.
    end.
end.
OUTPUT CLOSE.

Just try this example and if it doesn't work don't change the query but tell us what doesn't work and how you want it.

Casper.

 

[Casper]

I just see that the card-no in customers is a different card-no then in transfile.
It looks from you initial example that the card-no in tansfile is the last 3 positions of the card-no in customer table. Is this correct? (If so then it would have been nice if the dba had named this field in the tables differently).

But assuming this is the case then you need to alter the query I just posted:

DEFINE VARIABLE cNumbers AS CHARACTER   NO-UNDO.
DEFINE VARIABLE iTmp AS INTEGER     NO-UNDO.
assign cNumbers = '2525250703889488,2525250703889496,2525250703876907907'.
OUTPUT TO C:\cal\custTT2.TXT.
SESSION:DATE-FORMAT = "ymd".
do iTmp = 1 to num-entries(cNumbers):
FOR EACH customer WHERE customer.card-no = ENTRY(iTmp,cNumbers),
     EACH tranfile
        WHERE tranfile.customer-id = customer.customer-id
         AND tranfile.card-no = substring(customer.card-no,LENGTH(customer.card-no) - 2)
        EXPORT 
            DELIMITER ","
            card-no 
            donor-card-no
            on-statement 
            original-card-no
            string(tranfile.post-date, '9999/99/99') 
            subsidiary-id  
            tender-code 
            string(tranfile.tran-date, '9999/99/99') 
            tran-value 
            tran-points
        .
        END.
    end.
end.
OUTPUT CLOSE.

Casper.