eseargentina
New Member
Hi people from the forum! I'm working with Progress 9.1d and I'm having problems with the performance of my reports. I've written two procedures: one uses a "normal" query and the other one uses a dynamic query. The "normal" query takes around 15 seconds to run, while the other one takes more than a minute and a half! I'm including both queries so you can see what I'm doing and tell me what's wrong with it.
Normal:
FOR EACH invoice NO-LOCK where ..... USE-INDEX IND-CompAnuFecEnt,
EACH Invoice_Detail NO-LOCK OF invoice,
FIRST Products NO-LOCK WHERE ....,
FIRST Customers NO-LOCK WHERE .... :
.......
......
......
end.
Dynamics:
CREATE BUFFER Bh FOR TABLE "invoice".
CREATE BUFFER Bh1 FOR TABLE "Invoice_Detail".
CREATE BUFFER Bh2 FOR TABLE "products".
CREATE BUFFER Bh3 FOR TABLE "Customers".
CREATE QUERY qh.
qh:SET-BUFFERS(bh,bh1,Bh2,Bh3).
wQuery = "FOR EACH invoice FIELDS (field1 field2 field3 ....) NO-LOCK where " + Filter_Options + " USE-INDEX IND-CompAnuFecEnt, " +
" EACH Invoice_Detail FIELDS(field1 field2 field3 ....) NO-LOCK OF invoice " + Filter_Options +
" FIRST Products FIELDS(Field1) NO-LOCK WHERE .... " +
" FIRST Customers FIELDS(Field1) NO-LOCK WHERE .... " +
qh:QUERY-PREPARE(wQuery).
qh:QUERY-OPEN.
repeat:
qh:get-next(no-lock).
.......
......
......
end.
qh:close.
Thanks in advance
Normal:
FOR EACH invoice NO-LOCK where ..... USE-INDEX IND-CompAnuFecEnt,
EACH Invoice_Detail NO-LOCK OF invoice,
FIRST Products NO-LOCK WHERE ....,
FIRST Customers NO-LOCK WHERE .... :
.......
......
......
end.
Dynamics:
CREATE BUFFER Bh FOR TABLE "invoice".
CREATE BUFFER Bh1 FOR TABLE "Invoice_Detail".
CREATE BUFFER Bh2 FOR TABLE "products".
CREATE BUFFER Bh3 FOR TABLE "Customers".
CREATE QUERY qh.
qh:SET-BUFFERS(bh,bh1,Bh2,Bh3).
wQuery = "FOR EACH invoice FIELDS (field1 field2 field3 ....) NO-LOCK where " + Filter_Options + " USE-INDEX IND-CompAnuFecEnt, " +
" EACH Invoice_Detail FIELDS(field1 field2 field3 ....) NO-LOCK OF invoice " + Filter_Options +
" FIRST Products FIELDS(Field1) NO-LOCK WHERE .... " +
" FIRST Customers FIELDS(Field1) NO-LOCK WHERE .... " +
qh:QUERY-PREPARE(wQuery).
qh:QUERY-OPEN.
repeat:
qh:get-next(no-lock).
.......
......
......
end.
qh:close.
Thanks in advance