Hello

I have a report which uses a table that contains over 25mill rows. Report runs really slow and sometime never process because of large amound of data it has to go through.
I would like to create some kind of temp or hold file and extract only this years data to that hold file before hand.
Then use the hold file to create my report. Is it possible.
Here is my current code:

-DEFAULT &From_Object_Account = 4000
-DEFAULT &To_Object_Account = 4999
-DEFAULT &Document_Type = JE
TABLE FILE F0911
PRINT F0911.F0911.DOCUMENT_NUMBER
F0911.F0911.BUSINESS_UNIT
F0911.F0911.OBJECT_ACCOUNT
F0911.F0911.SUBSIDIARY
F0911.F0911.AMOUNT
F0911.F0911.UNITS
F0911.F0911.UNIT_OF_MEASURE
F0911.F0911.EXPLANATION___NAME_ALPHA
F0911.F0911.EXPLANATION___REMARK
F0911.F0911.TRANSACTION_ORIGINATOR
BY F0911.F0911.DOCUMENT_NUMBER NOPRINT
BY F0911.F0911.BUSINESS_UNIT NOPRINT
BY F0911.F0911.OBJECT_ACCOUNT NOPRINT
BY F0911.F0911.SUBSIDIARY NOPRINT
WHERE (F0911.F0911.OBJECT_ACCOUNT GE '&From_Object_Account')
AND (F0911.F0911.OBJECT_ACCOUNT LE '&To_Object_Account')
AND (F0911.F0911.LEDGER_TYPE EQ 'AA')
AND (F0911.F0911.G_L_DATE GE &From_GL_DATE_MMDDYYYY)
AND (F0911.F0911.G_L_DATE LE &To_GL_DATE_MMDDYYYY)
AND (F0911.F0911.DOCUMENT_TYPE EQ '&Document_Type');
HEADING
"Hanson Aggregates and Subsidiaries"
"General Ledger Report"
"&Document_Type Document Type"
"Object Accounts &From_Object_Account TO &To_Object_Account"
"GL Date Range:  &From_GL_DATE_MMDDYYYY TO &To_GL_DATE_MMDDYYYY"
FOOTING
"Date Report Generated: &DATE"
ON TABLE SET STYLE *
-*-INCLUDE app/hsmalpdf.sty
TYPE=REPORT,
   GRID=OFF,
   ORIENTATION=LANDSCAPE,
 $
TYPE= HEADING,$
TYPE= FOOTING,$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE NOTOTAL
END  

This message has been edited. Last edited by: Kerry,

Looks like the J.D. Edwards General Ledger - F0911.
I'm not sure if this would meet your requirements but make the initial report based on the F0902 - General Ledger Summed to month and then have a drill
down to the F0911.

Other things to check is indexes on the tables, etc. These may increase the speed fo retrieval.

Also for speed of retrieval, USE TABLEF, adn don't use BY Fields, as this will probably be pushed back to the DB.

How many records are you expecting out of this request ?

It looks like you don't need a HOLD file, other then, as Waz put it, "TABLEF, adn don't use BY Fields" which may reduce the retrieval time a bit, then HOLD that and report from that HOLD file. You should gain something. Run it both ways and check the timestamps of execution...BTW: You could use:

AND (F0911.F0911.G_L_DATE FROM &From_GL_DATE_MMDDYYYYTO &To_GL_DATE_MMDDYYYY)

as well. It may gain a millisecond of two depending on the generated SQL which I'm thinnking (now that I wrote this) may be the same either way...

Just to emphasise, check if the WHERE clauses are working on indexed fields, and if the query is returning large amounts of data, then it will take time for the DB to return it.

This should be a tuning exercise. There will be a way to speed things up, it just depends on how much can be achieved.

How can I check for indexes on the table?

Is your database Oracle? If so, try this. Your DBA should also be able to help you with this information.

SET SQLENGINE = SQLORA
SQL
SELECT *
FROM user_ind_columns
WHERE table_name='F0911';
TABLE
END

yes. It is oracle db2 but we use OBDC adapter to connect to it.

I tried using the query provide and got the following error:

(FOC1488) SQL INTERFACE HAS NOT BEEN INSTALLED OR COULD NOT BE INITIALIZED
: Subphysical interface not installed for SUFFIX=SQLORA. Module name: ORAPAS2
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
(FOC1488) SQL INTERFACE HAS NOT BEEN INSTALLED OR COULD NOT BE INITIALIZED
(FOC1479) ERROR CONNECTING TO SQL DATABASE

Try changing SQLORA to SQLODBC.