Can anyone see if this code can be any quicker

T

tjsingh

Member
Hi

I h ave created some code for a report but just takes ages can anyone see if they can find a faster way. There are lots of indexes for tables. I need to run it for 6 months but one day takes ages.

DEFINE STREAM stOrd.

define temp-table tt-Orders
field ttord-date LIKE ordstatus.kord-date
FIELD ttkspecsn LIKE ordstatus.kspecsn
field ttkcussn LIKE ordstatus.kcussn
FIELD ttkorder LIKE ordstatus.korder
FIELD ttkspeccode LIKE ordstatus.kspeccode
FIELD ttItem LIKE ovar.kvar-code
FIELD type1 LIKE specliq.type1
INDEX idx-cusno is PRIMARY ttkcussn.

OUTPUT STREAM stOrd TO c:\Caroline.csv.

FOR EACH ordstat WHERE ordstat.kord-date >= 02/01/07 AND ordstat.kord-date <= 03/01/07 NO-LOCK:
FOR EACH ordline OF ordstatus NO-LOCK:
FIND ovar WHERE ovar.kvseqnum = ordline.kvseqnum NO-LOCK.

IF AVAILABLE ovar THEN
FIND speclabel WHERE speclabel.label-code = ovar.kvar-code NO-LOCK.
IF AVAILABLE speclabel THEN
FIND specliq WHERE specliq.col-spec = speclabel.col-spec NO-LOCK.

CREATE tt-Orders.
ASSIGN tt-Orders.ttord-date = kord-date
tt-Orders.ttkcussn = ordstat.kcussn
tt-Orders.ttkorder = ordstat.korder
tt-Orders.ttkspecsn = ordstat.kspecsn
tt-Orders.ttkspeccode = ordstat.kspeccode
tt-Orders.ttItem = ovar.kvar-code
tt-Orders.type1[1] = specliq.type1[1]
tt-Orders.type1[2] = specliq.type1[2]
tt-Orders.type1[3] = specliq.type1[3]
tt-Orders.type1[4] = specliq.type1[4]
tt-Orders.type1[5] = specliq.type1[5]
tt-Orders.type1[6] = specliq.type1[6]
tt-Orders.type1[7] = specliq.type1[7]
tt-Orders.type1[8] = specliq.type1[8]
tt-Orders.type1[9] = specliq.type1[9]
tt-Orders.type1[10] = specliq.type1[10].


PUT STREAM stOrd UNFORMATTED
tt-Orders.ttord-date ","
tt-Orders.ttkcussn ","
tt-Orders.ttkorder ","
tt-Orders.ttkspecsn ","
tt-Orders.ttkspeccode ","
tt-Orders.ttItem ","
tt-Orders.type1[1] ","
tt-Orders.type1[2] ","
tt-Orders.type1[3] ","
tt-Orders.type1[4] ","
tt-Orders.type1[5] ","
tt-Orders.type1[6] ","
tt-Orders.type1[7] ","
tt-Orders.type1[8] ","
tt-Orders.type1[9] ","
tt-Orders.type1[10]
SKIP.

END.


END.




OUTPUT STREAM stOrd CLOSE.
 
R

RKR

Member
tjsingh said:
Hi

I h ave created some code for a report but just takes ages can anyone see if they can find a faster way. There are lots of indexes for tables. I need to run it for 6 months but one day takes ages.

DEFINE STREAM stOrd.

define temp-table tt-Orders
field ttord-date LIKE ordstatus.kord-date
FIELD ttkspecsn LIKE ordstatus.kspecsn
field ttkcussn LIKE ordstatus.kcussn
FIELD ttkorder LIKE ordstatus.korder
FIELD ttkspeccode LIKE ordstatus.kspeccode
FIELD ttItem LIKE ovar.kvar-code
FIELD type1 LIKE specliq.type1
INDEX idx-cusno is PRIMARY ttkcussn.

OUTPUT STREAM stOrd TO c:\Caroline.csv.

FOR EACH ordstat WHERE ordstat.kord-date >= 02/01/07 AND ordstat.kord-date <= 03/01/07 NO-LOCK:
FOR EACH ordline OF ordstatus NO-LOCK:
FIND ovar WHERE ovar.kvseqnum = ordline.kvseqnum NO-LOCK.

IF AVAILABLE ovar THEN
FIND speclabel WHERE speclabel.label-code = ovar.kvar-code NO-LOCK.
IF AVAILABLE speclabel THEN
FIND specliq WHERE specliq.col-spec = speclabel.col-spec NO-LOCK.

CREATE tt-Orders.
ASSIGN tt-Orders.ttord-date = kord-date
tt-Orders.ttkcussn = ordstat.kcussn
tt-Orders.ttkorder = ordstat.korder
tt-Orders.ttkspecsn = ordstat.kspecsn
tt-Orders.ttkspeccode = ordstat.kspeccode
tt-Orders.ttItem = ovar.kvar-code
tt-Orders.type1[1] = specliq.type1[1]
tt-Orders.type1[2] = specliq.type1[2]
tt-Orders.type1[3] = specliq.type1[3]
tt-Orders.type1[4] = specliq.type1[4]
tt-Orders.type1[5] = specliq.type1[5]
tt-Orders.type1[6] = specliq.type1[6]
tt-Orders.type1[7] = specliq.type1[7]
tt-Orders.type1[8] = specliq.type1[8]
tt-Orders.type1[9] = specliq.type1[9]
tt-Orders.type1[10] = specliq.type1[10].


PUT STREAM stOrd UNFORMATTED
tt-Orders.ttord-date ","
tt-Orders.ttkcussn ","
tt-Orders.ttkorder ","
tt-Orders.ttkspecsn ","
tt-Orders.ttkspeccode ","
tt-Orders.ttItem ","
tt-Orders.type1[1] ","
tt-Orders.type1[2] ","
tt-Orders.type1[3] ","
tt-Orders.type1[4] ","
tt-Orders.type1[5] ","
tt-Orders.type1[6] ","
tt-Orders.type1[7] ","
tt-Orders.type1[8] ","
tt-Orders.type1[9] ","
tt-Orders.type1[10]
SKIP.

END.


END.




OUTPUT STREAM stOrd CLOSE.
Click to expand...


First of all, why do you create a record in a temp-table and then export this record to file. It would be more efficient if you skipped that part and exported the data directly into the file.

"FOR EACH ordstat WHERE ordstat.kord-date >= 02/01/07 AND ordstat.kord-date <= 03/01/07 NO-LOCK:"

This loop is not 1 day, it is 1 month.. From the 1st of february to the first of march.

I cannot tell if there are more problems without knowledge of the used indexed. You might try to compile this program with XREF and look in the output for WHOLE-INDEX.
 
T

tjsingh

Member
Hi

Sorry what i was meant to say was that i tried for 1 day. I then changed the code for month as i needed to run it.

i will change the temp table and add output data directly.
 
F

FrancoisL

Member
You should be careful , your code will cause errors or export the wrong data if one of your find fails to bring back a record.
 
P

Patrickorigo

New Member
Your code:
"FOR EACH ordstat WHERE ordstat.kord-date >= 02/01/07 AND ordstat.kord-date <= 03/01/07 NO-LOCK:"

You should also be aware that this includes 1st of march, if you only want the month february, (1st to 28th febr) the second equal sign should be taken away

like this
"FOR EACH ordstat WHERE ordstat.kord-date >= 02/01/07 AND ordstat.kord-date < 03/01/07 NO-LOCK:"

It's very important that you have an index on ordstat.kord-date to speed up the search.
/Patrick
 
F

FrancoisL

Member
Here a way where you are sure that it wont crash

Code: 
DEFINE STREAM stOrd.
 

OUTPUT STREAM stOrd TO c:\Caroline.csv.
 
FOR EACH ordstat 
   WHERE ordstat.kord-date >= 02/01/07 
   AND ordstat.kord-date <= 03/01/07 NO-LOCK,
   EACH ordline OF ordstatus NO-LOCK,
       FIRST ovar WHERE ovar.kvseqnum = ordline.kvseqnum NO-LOCK,
          FIRST speclabel WHERE speclabel.label-code = ovar.kvar-code NO-LOCK,
             FIRST specliq WHERE specliq.col-spec = speclabel.col-spec NO-LOCK:
 
 
PUT STREAM stOrd UNFORMATTED
ordstat.kord-date ","
ordstat.kcussn ","
ordstat.korder ","
ordstat.kspecsn ","
ordstat.kspeccode ","
ovar.kvar-code ","
specliq.type1[1] ","
specliq.type1[2] ","
specliq.type1[3] ","
specliq.type1[4] ","
specliq.type1[5] ","
specliq.type1[6] ","
specliq.type1[7] ","
specliq.type1[8] ","
specliq.type1[9] ","
specliq.type1[10]
SKIP.
 
END.
 
 OUTPUT STREAM stOrd CLOSE.
 
T

tjsingh

Member
Cheers

I have a differnet question.I have an excel spreadsheet with headings and data and need to extract the data from the spreadsheet to a temp table not done this before

i have stated to get the column headings and next step is to get the data and populate a temp table

whats the best approach?
 
R

RKR

Member
Look in the sample programs for excelgraph.p (in the samples map of your progress installation directory, or on the documentation CD).

This is a sample program that interacts with excel. It should give you an idea how to retrieve data from an excel sheet.
 
T

tjsingh

Member
Hi

I had a look at that example but it extracts data from datbase and outputs it excel i need it vice versa?

cheers
 
F

FrancoisL

Member
I had a look at that example but it extracts data from datbase and outputs it excel i need it vice versa?
Click to expand...

If you print the code and put the page upside down then it will reverse and import the data. :lol:


Seriously , just look at the code given and instead of assigning the value , just read it and store it in your database. It doesnt get simpler then that.


Code: 
cRange = "A" + cColumn.
chWorkSheet:Range(cRange):Value = salesrep.rep-name.

becomes

Code: 
cRange = "A" + cColumn.
salesrep.rep-name  =  chWorkSheet:Range(cRange):Value .

tada!
 
R

RKR

Member
Hi,

Exactly what FrancoisL writes :lol:

The cells of an excel sheets are read/write attributes.

You can open the an existing excel sheet by changing this line of code:

/* create a new Workbook */
chWorkbook = chExcelApplication:Workbooks:Add().

by

/* Open an existing workbook */
chWorkbook = chExcelApplication:Workbooks:eek:pen("<filename>").

Where filename is the fully qualified filename of the excel file.


Greetings,
 
T

tjsingh

Member
cheers guys

i was wondering this import into the database is going to be automated and scheduled by a batch file. But the file name will change slightly on a weekly basis which is going to be sent via email.

If i want to process the email, extract the file whats the best method to use?

regards

TJ
 
R

RKR

Member
Make sure that the e-mail has a specific format. ie. always put the filename in the subject field, or put it on a single line in the body preceeded by a label.

If you want this automated then you have to make sure that there is some way that your software can recognize the filename.

There are several ways to proceed. The easiest way is to save the e-mail as a flat text file and import this file while parsing the text for the filename.

If you are using Outlook then you can also connect to Outlook in a simular way as you have connected to Excel and look directly into the e-mail for the filename. However for this approach a good knowledge of how Outlook works internally is required. With this last option it is possible to make a fully automated process. It can check if a specific e-mail has arrived and start the processing. A program like this is not written in one day :lol:

On www.msdn.com you can find a lot of information about the Microsoft Office products with lots of VisualBasic and C++ samples.
 
T

tjsingh

Member
Cheers

Just wondering when i processing the excel i am going to go through each line but whats the best way to check when the last line has reached as i then want to leave the repeat loop:

example is below: The code is BOLD is the loop which goes through the excel spreadsheet. cheers

assign
chWorkbook = chExcelApplication:Workbooks:Open(v_filename)
chWorkSheet = chExcelApplication:Sheets:Item(1)
chExcelApplication:VISIBLE = TRUE.

chWorksheet:Range("A1:k29"):Select.

chExcelApplication:Selection:Find("ITEM NO",, -4163, 2, 1, 1, "False"):activate.
w-itemNo-col = chExcelApplication:ActiveCell:COLUMN .

chExcelApplication:Selection:Find("DOCKET",, -4163, 2, 1, 1, "False"):activate.
w-Doc-col = chExcelApplication:ActiveCell:COLUMN .

chExcelApplication:Selection:Find("DELNOTE",, -4163, 2, 1, 1, "False"):activate.
w-delNote-col = chExcelApplication:ActiveCell:COLUMN .

chExcelApplication:Selection:Find("DATE",, -4163, 2, 1, 1, "False"):activate.
w-recDate-col = chExcelApplication:ActiveCell:COLUMN .

chExcelApplication:Selection:Find("QTY DEL",, -4163, 2, 1, 1, "False"):activate.
w-qtyDel-col = chExcelApplication:ActiveCell:COLUMN .

chExcelApplication:Selection:Find("REC",, -4163, 2, 1, 1, "False"):activate.
w-qtyrec-col = chExcelApplication:ActiveCell:COLUMN .

chExcelApplication:Selection:Find("PO NO",, -4163, 2, 1, 1, "False"):activate.
w-po-col = chExcelApplication:ActiveCell:COLUMN .

w-i = chExcelApplication:ActiveCell:row + 1.
w-i = w-i.

message "w-i" w-i view-as alert-box.

message int(chWorkSheet:Range('B6'):value) view-as alert-box.

/*get data*/
alone:
repeat:
w-i = w-i + 1.

message chWorksheet:RANGE("b" + STRING(w-i)):value view-as alert-box.


end.
 
R

RKR

Member
Check if the line that you are reading is blank. ("").

When the sheet contains blank lines then you can set a counter. If the next line is also blank you can increase the counter and check the next line, or leave the loop (depending on how many lines needs to be empty). When the next line contains information reset the counter.

When you have reached lineno 65536 you are always at the end. It is the maximum number of lines in a sheet :D
 
M

mfg_newbie

Member
Hi all,

I have a problem about open a excel file. i want to export data from database to excel file template(i had defined before), when I test direct(F2) on procedure editor Ok but when I compile to file .r and run on QAD software it didnot show excel file. How ever I used .visible = True, this command is not effect. How can I show that excel file?

thank!
 
R

RKR

Member
mfg_newbie said:
Hi all,

I have a problem about open a excel file. i want to export data from database to excel file template(i had defined before), when I test direct(F2) on procedure editor Ok but when I compile to file .r and run on QAD software it didnot show excel file. How ever I used .visible = True, this command is not effect. How can I show that excel file?

thank!
Click to expand...


Is it possible that the procedure is executed on another machine using an AppServer ?
 
You must log in or register to reply here.

Similar threads

L
How to solve looping problem
Replies
2
Views
492
lkeller
L
Mike
Need to find out root cause Please help
Replies
1
Views
527
TomBascom
TomBascom
Ryszard Musielak
Question Locate table errors from "dbtool" report
Replies
2
Views
697
Osborne
O
F
  • Locked
[Stackoverflow] [Progress OpenEdge ABL] Algorithm for de-duping relationship data
Replies
0
Views
326
Felice
F
D
  • Locked
[Stackoverflow] [Progress OpenEdge ABL] Can we use multiple tables in QUERY-PREPARE()?
Replies
0
Views
2K
Diego
D
Top